Referential Integrity Options (SQL)

A referential integrity constraint may be violated when tuples in the referenced tuple are updated / deleted. By default REJECT is used for operations that violate constraints.

However, we can specify what should happen on different events such as:

• ON DELETE
• ON UPDATE

And trigger actions such as:

• RESTRICT
• CASCADE
• SET NULL
• SET DEFAULT

Example:

CREATE TABLE EMPLOYEE (
	...,
	FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER)
	ON DELETE SET DEFAULT ON UPDATE CASCADE,
	FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN)
	ON DELETE SET NULL ON UPDATE CASCADE
);