Foreign key constraints are an excellent way to preserve the referential integrity of the data in your database.
If you’re new to SQL Server and don’t fully understand what a foreign key is or how they work, make sure you check out the full beginner-friendly tutorial first to learn everything you need to know:
SQL Server Foreign Key: Everything you need to know
In this very brief tutorial, we’ll discuss how to disable a foreign key constraint:
We disable a foreign key constraint by running the NOCHECK statement within an ALTER TABLE statement
The syntax is very easy. It follows this pattern:
ALTER TABLE <table-name> NOCHECK CONSTRAINT <foreign-key-constraint-name>
To demonstrate, we’ll create a couple of tables. First we’ll create a table called CollegeMajors:
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')
Then a table called Students:
CREATE TABLE Students ( StudentID INT IDENTITY(100,5) PRIMARY KEY, FirstName VARCHAR(15), LastName VARCHAR(15), DeclaredMajor INT NOT NULL )
Notice the DeclaredMajor column. We want this column to be a foreign link back to the CollegeMajors table, MajorID column. We can create our foreign key constraint very easily:
ALTER TABLE Students ADD CONSTRAINT fk_CollegeMajors_MajorID FOREIGN KEY (DeclaredMajor) REFERENCES CollegeMajors(MajorID)
Simple enough. Let’s check the content of the CollegeMajors table:
So if we want to insert a row into our Students table, the DeclaredMajor value must exist in the MajorID column of this CollegeMajors table. For example, this INSERT works fine:
But if we were to outline a major ID that simply doesn’t exist in the CollegeMajors table, we get an error message:
That entire error message says “The INSERT statement conflicted with the FOREIGN KEY constraint “fk_CollegeMajors_MajorID”. The conflict occurred in database “SimpleSQLTutorials”, table “dbo.CollegeMajors”, column ‘MajorID’.”
But let’s say for whatever reason, we needed to insert this row, and it needs to have that major ID of 99. We understand that major ID doesn’t exist in the parent table, but we’re ok with that.
Example of disabling a foreign key constraint:
Here’s how we would disable the foreign key constraint:
ALTER TABLE Students NOCHECK CONSTRAINT fk_CollegeMajors_MajorID
That’s it! The NOCHECK keyword is how we tell SQL Server to stop enforcing the foreign key constraint. If we run that code, we can then insert our row:
To re-enable the foreign key constraint, you would change the word ‘NOCHECK‘ to ‘CHECK‘, like this:
ALTER TABLE Students CHECK CONSTRAINT fk_CollegeMajors_MajorID
This is how we tell SQL Server to resume enforcing the foreign key constraint.
Next Steps:
Leave a comment if you found this tutorial helpful!
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!
Want to learn more about Foreign Key Constraints? Click the link to learn everything you need to know!
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!
