A set of attributes should be unique in a table
Class (dept, cnum, sec, unit, instructor, title)
Class (dept, cnum, sec, unit, instructor, title)
CREATE TABLE Class(
dept CHAR(2) NOT NULL,
cnum INT NOT NULL,
sec INT NOT NULL,
unit INT, instructor VARCHAR(100), title VARCHAR(100),
PRIMARY KEY(dept, cnum, sec)
UNIQUE (dept, sec, title)
)
# or since sid is just one attribute, we can do
sid INT REFERENCES Student(sid)
One PRIMARY KEY per table. Others should use UNIQUE
CREATE TABLE Enroll(
sid INT,
dept CHAR(2),
cnum INT,
sec INT
FOREIGN KEY(sid) REFERENCES Student(sid),
FOREIGN KEY(dept, cnum, sec) REFERENCES Class(dept,cnum,sec)
)
# or since sid is just one attribute, we can do
sid INT REFERENCES Student(sid)
Insert into E, Update E, Delete from S, Update S
RI violation from referencing table E is never allowed
RI violation from referenced table S is not allowed by default, but we can instruct DBMS to "fix" the violation automatically
Syntax:
CREATE TABLE E(
A INT, B INT,
FOREIGN KEY(B) REFERENCES S(B)
ON UPDATE{CASCADE | SET NULL | SET DEFAULT}
ON DELETE{CASCADE | SET NULL | SET DEFAULT}
);