Referential Integrity Options (Cascade, Set Null and Set Default)
Referential Integrity Options (Cascade, Set Null and Set Default)
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.
CREATE DATABASE SchoolEnrollment
CREATE TABLE dbo.Course
CourseID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL,
Credits tinyint NULL
CREATE TABLE dbo.Section
SectionID int IDENTITY(1,1) NOT NULL,
Days varchar(3) NULL,
Location varchar(5) NULL,
Time time(0) NULL,
CourseID int NULL CONSTRAINT FK_Section_Course FOREIGN KEY REFERENCES dbo.Course(CourseID)
VALUES('SQL Level 1', 3)
,('SQL Level 2', 3)
,('Database Design', 4)
,('Data Warehouses', 4)
VALUES('MW', 'B24', '6:00 PM', 2)
,('TH', 'B24', '6:00 PM', 1)
,('MWF','A18', '3:00 PM', 3)
,('MWF','C12', '3:00 PM', 3)
,('TH', 'A18', '4:00 PM', 2)
,('MW', 'A18', '4:00 PM', 1)
,('MWF','B15', '2:00 PM', 4)
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.
You May Also Like
In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management
How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015