Referential Integrity Options (Cascade, Set Null and Set Default)

Home > Blogs > SQL Server > Referential Integrity Options (Cascade, Set Null and Set Default)

Referential Integrity Options (Cascade, Set Null and Set Default)

4 2 Peter Avila
Added by 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?

SQL Server Referential Integrity Options

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).

ne-to-many relationship SQL Server Referential Integrity Options

(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.

Impacts on referential integrity SQL Server Referential Integrity Options

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).

Object Explorer SQL Server Referential Integrity Options

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.

Object Explorer SQL Server Referential Integrity Options

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.

INSERT and UPDATE Specification SQL Server Referential Integrity Options

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

Videos You May Like

A Simple Introduction to Cisco CML2

0 3955 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Creating Dynamic DNS in Network Environments

0 649 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader teaches how to create Dynamic DNS zones in Network Environments. Video Transcription: Now that we’ve installed DNS, we’ve created our DNS zones, the next step is now, how do we produce those … Continue reading Creating Dynamic DNS in Network Environments

Data Models in Business Analysis

0 200 0

This video is from our PMI-PBA Business Analysis for IT Analysts and Project Managers (PMI-PBA)® Certification now available at Interface Technical Training. Also see Steve’s PMP Project Management Certification Course: Project Management Professional (PMP®) Certification Video Training PMBOK® 6th Edition  Video Transcription: Data Models are part of the elicitation analysis in PMI-PBA. This is the way … Continue reading Data Models in Business Analysis

Write a Comment

See what people are saying...

    Share your thoughts...

    Please fill out the comment form below to post a reply.