4 2

Added by Peter Avila April 11, 2014

Ever wonder why there are only two options under the INSERT and UPDATE Specification of a foreign key? Why is there no Insert Rule? And to which table in the relationship do these rules apply?

Let’s start at the beginning. Relationships in a database are implemented with foreign keys and primary keys. (For a primer on relationships, download this article.) Referential Integrity is a constraint in the database that enforces the relationship between two tables. The Referential Integrity constraint requires that values in a foreign key column must either be present in the primary key that is referenced by the foreign key or they must be null.

Let’s take the example shown in the database design article referenced in the paragraph above. The Course and Section tables are related to each other in a one-to-many relationship (for each course in the Course table, there can be many sections in the Section table; and for each section in the Section table, there can be only one course in the Course table). This relationship is implemented by creating a foreign key (CourseID) in the table on the many-side of the relationship (the Section table) that references the primary key (CourseID) in the table on the one-side of the relationship (the Course table).

(Notice that, though both tables have primary keys, the only primary key that plays a role in the relationship is the primary key on the one-side of the relationship. We often refer to the tables in a relationship as the primary-key table and the foreign-key table. We will do so here so that this discussion can also be applied to a one-to-one relationship.)

There is potential for violating referential integrity between these two tables if we modify (insert, update or delete) data; in some cases, data modifications can result in referential integrity violations, and in others no violations will occur. For example, deleting rows from the primary-key table can cause referential integrity violations. Let’s say we delete course 2 (SQL Level 2). That will cause sections 1 and 5 to reference non-existing courses, which violates referential integrity. On the other hand, deleting rows from the foreign-key table will cause no referential integrity violations. Let’s say we delete section 7 that references course 4. The result will simply be that course 4 will no longer have that section; the worst that can happen is that it will end up with no sections. No problem.

When a data modification would cause a referential integrity violation, what can the database do to prevent the violation? Disallowing the data modification is always an option. But in some cases, it has other options. In the case we just saw of the deletion of a course, the database can also prevent a referential integrity violation if it also deletes the sections that reference the deleted course (sections 1 and 5 in the Section table). This is called a cascade, because the deletion in the primary-key table is cascaded to the foreign-key table. Other options the database has are to set the foreign key to null or to its default value (as long as the default value references an existing value in the primary-key table).

The table below summarizes all the data modifications that can take place, their impacts on referential integrity, and the choices the database has in preventing the violations in each case.

Notice that the database has choices in the event of a referential integrity violation (disallow, cascade, set the foreign key to null or its default value) but only when an update or a delete is performed in the primary-key table; in all other cases where there would be a referential integrity violation, the only action the database can take is to disallow the operation.

That explains why only the delete and update operations are represented in the dialog you saw above. It also shows that the table in which the deletions and updates in question occur is the primary-key table.

Let’s set up a simple database that will allow you to see how to tell the database what to do when an update or delete is performed in the primary-key table. Run the script below in SQL Server 2008 or higher to create a database called SchoolEnrollment with the Course and Section tables related as shown above.

After you run the script above, refresh the database folder so you can see the new database (right-click on the database folder in Object Explorer and select Refresh).

Next, expand the Databases folder, then the SchoolEnrollment folder, the Tables folder, the Section table folder, and finally the Keys folder. Double-click on the referential integrity constraint, FK_Section_Course, in the Keys folder of the Section table.

This will put the table in design mode and will display the Foreign Key Relationships window.

Next, expand the INSERT and UPDATE Specification section (by now, you are probably aware that this is a misnomer; it should be called DELETE and UPDATE Specification), and select the dropdown for either the Delete Rule or the Update Rule.

By default, no action is specified for either operation. If no action is specified, the database will not allow the deletion or update in the primary-key table if they would result in referential integrity violations. You can also select Cascade, in which case the database will allow the deletion or update in the primary-key table, but it will also cascade them to the foreign-key table as discussed above. Other choices include Set Null, which, if the delete or update will result in a referential integrity violation, the database will put Null into the CourseID foreign key on the corresponding section row(s), and Set Default, which will instead use the default value of the foreign key column if one exists.

Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ