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.