Foreign Key Constraints in Microsoft SQL Server are excellent for preserving the integrity of our data.
We use a foreign key constraint to basically establish a parent-child relationship between two tables. A foreign key constraint will enforce that a value must first exist in the parent table before it can be referenced in the child table.
Take a look at the full tutorial on foreign key constraints to learn more:
SQL Server Foreign Key: Everything you need to know
In this very brief tutorial, we’ll learn about one important rule you should know when it comes to working with foreign key constraints:
If your foreign key constraint uses the ON DELETE/UPDATE SET DEFAULT setting, the default value in the default constraint must still exist in the parent table.
This rule applies for when you have a foreign key constraint and a Default Constraint applied to the same child column.
We’ll create some data to demonstrate. We’ll create a Students table and a CollegeMajors table:
/*
Create a CollegeMajors table and populate it with data
*/
CREATE TABLE CollegeMajors
(
MajorID INT IDENTITY PRIMARY KEY,
MajorName VARCHAR(30) NOT NULL
)
INSERT INTO CollegeMajors (MajorName)
VALUES
('Computer Science'),
('Information Technology'),
('Graphic Design'),
('Computer Criminology'),
('Biology'),
('Chemistry')
/*
Create a Students table and populate it with data
*/
CREATE TABLE Students
(
StudentID INT IDENTITY(100,5) PRIMARY KEY,
FirstName VARCHAR(15),
LastName VARCHAR(15),
DeclaredMajor INT NOT NULL DEFAULT 0
)
INSERT INTO Students (FirstName, LastName, DeclaredMajor)
VALUES
('Jeff','Alanzo', 2),
('Amy','Bell', 1),
('Marla','Bridge', 2),
('Andrew','Bucks', 6),
('Sara','Carlton', 5),
('Kimberly','Cutter', 6),
('Jack','Faun', 4),
('Sam','Green', 1),
('Max','Mills', 3)
Notice the DeclaredMajor column in the Students table. This represents the Major that a student has decided to pursue. We established a Default Constraint on this column to set the value to 0 if no value is provided on inserts.
In order to maintain the referential integrity of our data, it would be good to establish a Foreign Key Constraint on this column to make sure values in this column actually exist in the CollegeMajors table first.
Creating the constraint is easy:
ALTER TABLE Students ADD CONSTRAINT fk_CollegeMajors FOREIGN KEY (DeclaredMajor) REFERENCES CollegeMajors(MajorID) ON DELETE SET DEFAULT
Notice this constraint uses the ON DELETE SET DEFAULT setting. The idea is that if a parent row is deleted, any child rows that reference it in the Students.DeclaredMajor column will have that value changed to the default value in the default constraint (which is just 0).
Let’s take a look at the data we have in the CollegeMajors table:
Let’s say we want to delete the Biology major, which has a MajorID value of 5. The question is: Do we have any child rows (a.k.a referencing rows) in the Students table for this major?
Let’s take a look:
Looks like there is, indeed, a referencing row.
What happens if we delete the referenced row?
If we delete the parent row (a.k.a the referenced row) for the major Biology, what we expect to happen is the referencing row to have it’s DeclaredMajor value change to the default value of 0. This is because of the ON DELETE SET DEFAULT setting we established on our foreign key constraint.
Ok, so let’s try it:
Here is that full error message:
The DELETE statement conflicted with the FOREIGN KEY constraint “fk_CollegeMajors”. The conflict occurred in database “SimpleSQLTutorials”, table “dbo.CollegeMajors”, column ‘MajorID’.
The statement has been terminated.
So, uh, what happened? Folks, when you use the ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT setting with your foreign key constraints, the default value in the default constraint must still follow the rules of a foreign key constraint. In other words, the default value must exist in the parent table before it can be referenced in the child table.
In order for this to work, the default value of 0 must exist in the parent table. Presently, it does not. Here’s a reminder of what’s in the CollegeMajors table:
So again, in order for the ON DELETE SET DEFAULT setting to work properly, we need to add a row to this table where the MajorID value is the value of the default constraint, which is 0. This row will basically be considered a dummy row. We’ll use this row to basically represent that the student doesn’t have a declared a major:
--Turn on IDENTITY_INSERT so we can add a dummy row manually SET IDENTITY_INSERT CollegeMajors ON INSERT INTO CollegeMajors (MajorID, MajorName) VALUES (0, 'No Major') SET IDENTITY_INSERT CollegeMajors OFF
Then we’ll check the content of the table:
Now that there is officially a row in CollegeMajors with a MajorID value of 0, our DELETE statement works fine:
If we check the child table now, the referencing row has had it’s value changed to the default value:
Superb!
Next Steps:
If you found this tutorial helpful, make sure you download your FREE Ebook:
FREE Ebook on SQL Server Constraints!
This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:
- Primary Key constraints
- Foreign Key constraints
- Default constraints
- Check constraints
- Unique constraints
Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!
Make sure you check out the full beginner-friendly tutorial on Foreign Key Constraints to learn everything you need to know about them:
SQL Server Foreign Key: Everything you need to know
Also make sure you check out the full beginner-friendly tutorial on Default Constraints:
SQL Server Default Constraint: A Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
