Unformatted text preview: straints Over Multiple Tables
Suppose the number of sailors plus the number of boats should be less
than 100. This will not work:
CREATE TABLE Sailors ( sid
PRIMARY KEY (sid),
CHECK ((SELECT COUNT (S.sid)
FROM Sailors S)
(SELECT COUNT(B.bid) FROM BOATS B)
< 100)); Assertion
CREATE ASSERTION SmallClub
CHECK ((SELECT COUNT (S.sid) FROM Sailors S)
(SELECT COUNT (B.bid) FROM Boats B)
< 100); Not supported by MySQL.
MySQL. 21 Exercise 5.10.2 (p. 180) 22 Enforcing Integrity Constraints
What happens if we: Consider the following relational schema.
Emp (eid, ename, age, salary)
Works (eid, did, pct_time)
Dept (did, budget, managerid) • Try to insert tuple with same PK as another tuple in
• Try to insert tuple with null PK?
• Try to insert tuple with value for field that’s not in
domain (type) for that field?
• Try to modify tuple’s PK so that new PK value is same
as another in table? Write...
View Full Document
- Fall '12
- MYSQL , Foreign key, sailors, Sid