How Well Do You Know the Rules?

If you’ve worked with relational databases for any length of time, you’re probably aware that referential integrity (RI) constraints are used to define relationships between two or more base tables. But are you aware that a set of rules governs the behavior of RI constraints? More importantly, do you know how these rules work to safeguard data integrity when DML operations are performed against tables that are related? In this column, I’ll identify the rules available and I’ll illustrate how these rules control behavior when insert, update, and delete operations are performed against tables that are linked by one or more RI constraints.

Why the RI constraint rules exist

The primary reason for using an RI constraint is to ensure that data integrity is maintained whenever one table references another. When one table (the child) is linked to another (the parent) via a foreign key (one or more columns in the child table that reference columns in the parent table), the resulting RI constraint guarantees that for every row in the child table that has a value in a column that’s part of a foreign key, a corresponding row will exist in the parent table. So what happens when a SQL operation tries to manipulate data in a way that would void this guarantee? Before answering this question, let’s look at how data integrity could be compromised if the checks and balances provided by an RI constraint were not in place:

• An insert operation could add a record to a child table for which there is no matching record in the associated parent table.

• An update operation could change a record in a child table such that it no longer has a matching record in the associated parent table.

• An update operation could change a record in a parent table, leaving records in an associated child table that no longer have a matching record in the parent table.

• A delete operation could remove a record from a parent table, leaving records in an associated child table that no longer have a matching record in the parent table.

DB2 can either prevent such operations from being performed, or it can attempt to perform these actions in a way that will safeguard data integrity. That’s where the rules that govern RI constraints come into play. Each RI constraint has three rules—an Insert rule, an Update rule, and a Delete rule—and the way in which DB2 responds to operations that threaten data integrity is controlled by the way two of these rules are defined.

The Insert rule

The Insert rule guarantees that a value will never be inserted into the foreign key of a child table unless a matching value already exists in the corresponding key column(s) of the associated parent table. Any attempt to insert records into a child table that violate this rule will result in an error. In contrast, no checking is performed when records are added to the key columns of a parent table. Figure 1 illustrates how the Insert rule is enforced.

Figure 1: How the Insert rule is enforced

The Update rule

The Update rule controls how update operations performed against tables linked by an RI constraint are processed. Two types of behaviors are possible, depending upon how this rule is defined. Update rules can be defined as follows:

ON UPDATE RESTRICT—When an update operation is performed on the parent table, each record in the child table will have the same value for its foreign key that it had before the update operation was performed. This rule is enforced before all other constraints, including other RI constraints.

ON UPDATE NO ACTION—When an update operation is performed on either table (parent or child), each record in the child table will have a value for its foreign key that has a matching value in the key column(s) of the associated parent table. However, the value may not be the same as it was before the update operation occurred. This rule is enforced after all other constraints, including other RI constraints, are applied.

Figure 2 illustrates how the ON UPDATE NO ACTION definition will allow an update operation to be performed that the ON UPDATE RESTRICT definition would prevent. (Note that the COLORS and FRUITS tables referenced in this illustration were created as shown in Figure 1.) Like the Insert rule, the Update rule is implicitly created as part of an RI constraint. If an Update rule is not explicitly defined, the ON UPDATE NO ACTION definition is used by default.

Figure 2: Example where the ON UPDATE NO ACTION definition allows data in a parent table to be modified

The Delete rule

The Delete rule controls how delete operations performed against a parent table in an RI relationship are processed. Four types of behaviors are possible, depending upon how this rule is defined. Delete rules can be defined as follows:

ON DELETE CASCADE—When a record is deleted from the parent table, all records in the child table with matching foreign key values are also deleted.

ON DELETE SET NULL—When a record is deleted from the parent table, all records in the child table with matching foreign key values are set to NULL (provided the columns that make up the foreign key are nullable). Other values for the dependent row are not affected.

ON DELETE RESTRICT—When a delete operation is performed on the parent table, each row in the child table will have the same value for its foreign key that it had before the delete operation was performed. This rule is enforced before all other constraints, including other RI constraints that modify data such as ON DELETE CASCADE and ON DELETE SET NULL.

ON DELETE NO ACTION—When a delete operation is performed on the parent table, each row in the child table will have the same value for its foreign key that it had before the delete operation was performed. This rule is enforced after all other constraints, including RI constraints that modify data such as ON DELETE CASCADE and ON DELETE SET NULL.

Figure 3 illustrates how the Delete rule is enforced when the ON DELETE CASCADE definition is used; Figure 4 illustrates how the CREATE REFERENTIAL INTEGRITY CONSTRAINT Delete rule is enforced when the ON DELETE SET NULL definition is used. (Note that the COLORS and FRUITS tables referenced in these illustrations were created as shown in Figure 1.)

Figure 3: Example of the ON DELETE CASCADE definition at work

Figure 4: Example of the ON DELETE SET NULL definition at work

If a table has only one RI constraint, the behavior of ON DELETE RESTRICT and ON DELETE NO ACTION is essentially the same. Where the two definitions differ can be seen when an attempt is made to delete rows from multiple parent tables that are referenced by a single child. Figure 5 shows an example where the ON DELETE NO ACTION definition will allow a delete operation to be performed that the ON DELETE RESTRICT definition would prevent. In this example, the RI constraint with the ON DELETE CASCADE definition is processed before the RI constraint with the ON DELETE NO ACTION definition, allowing records to be deleted from all of the related tables.

As with the previous rules, the Delete rule is implicitly created as part of an RI constraint. If a Delete rule is not explicitly defined, the ON DELETE NO ACTION definition is used by default.

Figure 5: Example where the ON DELETE NO ACTION definition allows data in parent tables to be deleted

Make the rules work for you

Within most businesses, data often must adhere to a certain set of rules and restrictions. (For example, “Every employee must be assigned to a department.”) By using RI constraints, you can easily place some of the logic needed to enforce such business rules directly in a database rather than in applications that interact with the database. But when you define RI constraints, keep in mind that there are rules in place to safeguard data integrity when DML operations are performed against related tables.