Referential Integrity (ON DELETE, ON UPDATE)

A referential integrity enforces insert and update rules for the tables with the foreign key and the corresponding primary key constraint. Examples 5.7 and 5.10 (see section “CREATE TABLE and Declarative Integrity Constraints“) specify two such constraints: prim_empl and foreign_works. The REFERENCES clause in Example 5.10 determines the employee table as the parent table.

If the referential integrity for two tables is specified, the modification of values in the primary and the corresponding foreign key are not always possible. The following subsection discusses when it is possible and when not.

Possible Problems with Referential Integrity

There are four cases in which the modification of the values in the foreign key or in the primary key can cause problems. All of these cases will be shown using the sample database. The first two cases affect modifications of the referencing table, while the last two concern modifications of the parent table.

Case 1 – Insert a new row into the works_on table with the employee number 11111.

The insertion of the new row in the referencing table works_on introduces a new employee number for which there is no matching employee in the parent table (employee). If the referential integrity for both tables is specified as is done in Examples 5.7 and 5.10, the Database Engine rejects the insertion of a new row. For readers who are familiar with the SQL language, the corresponding Transact-SQL statement is

USE sample;
INSERT INTO works_on (emp_no, ...)
	VALUES (11111, ...);Code language: PHP (php)

Case 2 – Modify the employee number 10102 in all rows of the works_on table. The new number is 11111.

In Case 2, the existing value of the foreign key in the works_on table should be replaced using the new value, for which there is no matching value in the parent table employee. If the referential integrity for both tables is specified as is done in Examples 5.7 and 5.10, the database system rejects the modification of the rows in the works_on table. The corresponding Transact-SQL statement is

USE sample;
UPDATE works_on
	SET emp_no = 11111 WHERE emp_no = 10102;Code language: PHP (php)

Case 3 – Modify the employee number 10102 in the corresponding row of the employee table. The new number is 22222.

In Case 3, the existing value of the primary key in the parent table and the foreign key of the referencing table is modified only in the parent table. The values in the referencing table are unchanged. Therefore, the system rejects the modification of the row with the employee number 10102 in the employee table. Referential integrity requires that no rows in the referencing table (the one with the FOREIGN KEY clause) can exist unless a corresponding row in the parent table (the one with the PRIMARY KEY clause) also exists. Otherwise, the rows in the parent table would be “orphaned.” If the modification described above were permitted, then rows in the works_on table having the employee number 10102 would be orphaned, and the system would reject it. The corresponding Transact-SQL statement is

USE sample;
UPDATE employee
	SET emp_no = 22222 WHERE emp_no = 10102;Code language: PHP (php)

Case 4 – Delete the row of the employee table with the employee number 10102.

Case 4 is similar to Case 3. The deletion would remove the employee for which matching rows exist in the referencing table. Example 5.11 shows the definition of tables of the sample database with all existing primary key and foreign key constraints. (If the employee, department, project, and works_on tables already exist, drop them first using the DROP TABLE table_name statement.)

USE sample;

CREATE TABLE department(dept_no CHAR(4) NOT NULL,
		dept_name CHAR(25) NOT NULL,
		location CHAR(30) NULL,
		CONSTRAINT prim_dept PRIMARY KEY (dept_no));

CREATE TABLE employee (emp_no INTEGER NOT NULL,
		emp_fname CHAR(20) NOT NULL,
		emp_lname CHAR(20) NOT NULL,
		dept_no CHAR(4) NULL,
		CONSTRAINT prim_emp PRIMARY KEY (emp_no),
		CONSTRAINT foreign_emp FOREIGN KEY(dept_no) REFERENCES department(dept_no));

CREATE TABLE project (project_no CHAR(4) NOT NULL,
		project_name CHAR(15) NOT NULL,
		budget FLOAT NULL,
		CONSTRAINT prim_proj PRIMARY KEY (project_no));

CREATE TABLE works_on (emp_no INTEGER NOT NULL,
		project_no CHAR(4) NOT NULL,
		job CHAR (15) NULL,
		enter_date DATE NULL,
		CONSTRAINT prim_works PRIMARY KEY(emp_no, project_no),
		CONSTRAINT foreign1_works FOREIGN KEY(emp_no) REFERENCES employee(emp_no),
		CONSTRAINT foreign2_works FOREIGN KEY(project_no) REFERENCES project(project_no));Code language: PHP (php)

The ON DELETE and ON UPDATE Options

The Database Engine can react differently if the values of the primary key of a table should be modified or deleted. If you try to update values of a foreign key, and those modifications result in inconsistencies in the corresponding primary key (see Case 1 and Case 2 in the previous section), the database system will always reject the modification and will display a message similar to the following:

But if you try to modify the values of a primary key, and these modifications result in inconsistencies in the corresponding foreign key (see Case 3 and Case 4 in the previous section), a database system could react very flexibly. Generally, there are four options for how a database system can react:

• NO ACTION – Allows you to modify (update or delete) only those values of the parent table that do not have any corresponding values in the foreign key of the referencing table.
• CASCADE – Allows you to modify (update or delete) all values of the parent table. If this option is specified, a row is updated (i.e., deleted) from the referencing table (i.e., the one with the foreign key) if the corresponding value in the primary key has been updated, or the whole row with that value has been deleted from the parent table (i.e., the one with the primary key).
• SET NULL – Allows you to modify (update or delete) all values of the parent table. If you want to update a value of the parent table and this modification would lead to data inconsistencies in the referencing table, the database system sets all corresponding values in the foreign key of the referencing table to NULL. Similarly, if you want to delete the row in the parent table and the deletion of the value in the primary key would lead to data inconsistencies, the database system sets all corresponding values in the foreign key to NULL. That way, all data inconsistencies are omitted.
• SET DEFAULT – Analogous to the SET NULL option, with one exception: all corresponding values in the foreign key are set to a default value. (Obviously, the default value must still exist in the primary key of the parent table after modification.)

Example 5.12 shows the use of the ON DELETE and ON UPDATE options.

USE sample;
CREATE TABLE works_on1
(emp_no INTEGER NOT NULL,
 project_no CHAR(4) NOT NULL,
 job CHAR (15) NULL,
 enter_date DATE NULL,
 CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no),
 CONSTRAINT foreign1_works1 FOREIGN KEY(emp_no)
	REFERENCES employee(emp_no) ON DELETE CASCADE,
CONSTRAINT foreign2_works1 FOREIGN KEY(project_no)
	REFERENCES project(project_no) ON UPDATE CASCADE);Code language: PHP (php)

Example 5.12 creates the works_on1 table that uses the ON DELETE CASCADE and ON UPDATE CASCADE options. If you load the works_on1 table with the content shown in Table 1-4 in section “Relational Database Systems“, each deletion of a row in the employee table will cause the additional deletion of all rows in the works_on1 table that have the corresponding value in the emp_no column. Similarly, each update of a value in the project_no column of the project table will cause the same modification on all corresponding values in the project_no column of the works_on1 table.