Referential integrity = integrity of references = no “dangling pointers”
Referential integrity from R.A to S.B
Each value in column A of table R must appear in column B of table S
A is called the “foreign key” – foreign key constraints
B is usually required to be the primary key for table S or at least unique
Multi-attribute foreign keys are allowed
Enforcement
Potentially violating modifications:
Insert into R if violation -> error
Delete from S can auto-update R to avoid violation
Update R.A if violation -> error
Update S.B can auto-update R to avoid violation
Special actions
Delete from S
Restrict(default) – error
Set Null – take referencing tuples and replace sIDs with NULLs
Cascade – will delete referencing value, will cascade through all referencing tables
Update S.B
Restrict(default) – error
Set Null – take referencing tuples and replace sIDs with NULLs
Cascade – will update referencing value, will cascade through all referencing tables
create table College(cName text primary key, state text, enrollment int);
create table Student(sID int primary key, sName text, GPA real, sizeHS int);
create table Apply(sID in references Student(sID), cName text references College(cName), major text, decision text);
can’t drop tables!
create table Apply(sID int references Student(sID) on delete set null, cName text references College(cName) on update cascade, major text, decision text);
create table T (A int, B int, C int, primary key (A,B), foreign key (B,C) references T(A,B) on delete cascade.