If you find yourself in the situation where you need to disable a foreign key constraint in SQL Server, here’s how to do that using Transact-SQL.

This will allow you to enter data without being restricted by the foreign key. Obviously, you wouldn’t do this unless you had a very good reason to do so. Foreign keys enforce referential integrity, so disabling them has the potential to create all sorts of issues.

Example 1 – Disable the Foreign Key Constraint

To disable a foreign key constraint, use the NOCHECK argument within an ALTER TABLE statement.

Like this:

ALTER TABLE BandMember 
NOCHECK CONSTRAINT FK_BandMember_Musician; 

This code disables a foreign key constraint called FK_BandMember_Musician.

Example 2 – Review the Constraint

We can query the sys.foreign_keys system view to verify that our constraint has been disabled:

SELECT 
  name AS 'Constraint',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys;

Result:

+--------------------------------+---------------+------------------+
| Constraint                     | is_disabled   | is_not_trusted   |
|--------------------------------+---------------+------------------|
| FK_BandMember_Band             | 0             | 0                |
| FK_BandMember_Musician         | 1             | 1                |
| FK_MembershipPeriod_BandMember | 0             | 0                |
+--------------------------------+---------------+------------------+

In this case I selected all foreign key constraints from the current database.

We can see that this is the only one that’s disabled (because its is_disabled column is set to 1).

You might notice that the is_not_trusted column is also set to 1. This indicates that the constraint has not been verified by the system.

This makes sense, because we can no longer assume that the constraint has checked all the data. The fact that the constraint is disabled means that data is now able to get into the database without being checked by the constraint. Therefore, the potential exists for invalid data to be present in the database.

If you ever need to re-enable the constraint, you will have the opportunity to restore the constraint’s trust (by using the WITH CHECK option). This will check all existing rows before enabling the constraint.

You will also have the option of not checking the existing data, but this should only be done in rare cases.

Here’s the same query again, but with some extra columns to show the tables and referenced tables:

SELECT 
  name AS 'Constraint',
  OBJECT_NAME(parent_object_id) AS 'Table',
  OBJECT_NAME(referenced_object_id) AS 'Referenced Table',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys;

Result:

+--------------------------------+------------------+---------------------+---------------+------------------+
| Constraint                     | Table            | Referenced Table    | is_disabled   | is_not_trusted   |
|--------------------------------+------------------+---------------------+---------------+------------------|
| FK_BandMember_Band             | BandMember       | Band                | 0             | 0                |
| FK_BandMember_Musician         | BandMember       | Musician            | 1             | 1                |
| FK_MembershipPeriod_BandMember | MembershipPeriod | BandMember          | 0             | 0                |
+--------------------------------+------------------+---------------------+---------------+------------------+