2.11 Referential Integrity

Referential integrity can be violated in four ways

-A primary key is updated. -A foreign key is updated. -A row containing a primary key is deleted. -A row containing a foreign key is inserted.

Referential Integrity Actions

RESTRICT, SET NULL, SET DEFAULT, CASCADE

If a primary key is updated

matching foreign keys are updated to the same value.

CASCADE

propagates primary key changes to foreign keys

RESTRICT

rejects an insert, update, or delete that violates referential integrity

If a primary key is deleted

rows containing matching foreign keys are deleted

SET NULL

sets invalid foreign keys to NULL.

SET DEFAULT

sets invalid foreign keys to the foreign key default value

fully NULL foreign key

a simple or composite foreign key in which all columns are NULL.

Referential Integrity

a relational rule that requires foreign key values are either fully NULL or match some primary key value.