Mysql 19 19 20 constraints over multiple tables

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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: not CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), (sid), CHECK ((SELECT COUNT (S.sid) (S.sid) FROM Sailors S) + (SELECT COUNT(B.bid) FROM BOATS B) COUNT(B.bid) < 100)); Assertion CREATE ASSERTION SmallClub CHECK ((SELECT COUNT (S.sid) FROM Sailors S) (S.sid) + (SELECT COUNT (B.bid) FROM Boats B) (B.bid) < 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 table? • 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

This note was uploaded on 10/15/2012 for the course CSE 4503 taught by Professor Edallen during the Fall '12 term at Mississippi State.

Ask a homework question - tutors are online