If you have a foreign key constraint in SQL Server that is currently disabled, you can use the code below to re-enable it.

When you enable a foreign key constraint, you have the option to specify whether or not to check any existing data in the table. This also applies when you enable a CHECK constraint.

Below are code examples of enabling a foreign key constraint, while specifying each of these different options.

Example 1 – Enable a Constraint using WITH CHECK

This is the recommended method (unless you have a specific reason not to use it).

Here’s an example of enabling a foreign key constraint called FK_Albums_Artists:

ALTER TABLE Albums 
WITH CHECK CHECK CONSTRAINT FK_Albums_Artists; 

Here I explicitly state WITH CHECK, which tells SQL Server to check the existing data before enabling the constraint. If any data violates the constraint, the constraint won’t be enabled and you’ll get an error.

This is good, because it enforces referential integrity.

When you create a new foreign key constraint, this is the default setting. However, when you enable an existing constraint (as we’re doing here), it’s not the default setting.

Example 2 – Enable a Constraint using WITH NOCHECK

In this example the constraint is enabled without checking the existing data:

ALTER TABLE Albums 
WITH NOCHECK CHECK CONSTRAINT FK_Albums_Artists;

Here I explicitly state WITH NOCHECK, which tells SQL Server not to check the existing data. This means that the constraint will be enabled even if the table already contains data that violates the constraint.

This is the default setting when enabling a constraint (but not when creating one).

One of the few reasons (probably the only reason) you would use this is if you want to keep invalid data in the database. Perhaps you have a one-off exception where you must enter a row or more of invalid data, but you require all future data to conform to the constraint.

However, there are still risks associated with doing this. Here’s what Microsoft has to say about this:

We don’t recommend doing this, except in rare cases. The new constraint is evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that doesn’t follow the constraint.

So using WITH NOCHECK could potentially cause issues later on.

Example 3 – Enable a Constraint using the Default Option

Here’s an example using the default option:

ALTER TABLE Albums 
CHECK CONSTRAINT FK_Albums_Artists;

This example is the equivalent of the previous example. Because I didn’t specify whether or not to check, SQL Server assumes I want WITH NOCHECK.

So be sure to explicitly specify WITH CHECK if you want to avoid referential integrity issues.

Using WITH NOCHECK Removes Trust

When you enable a constraint using (the default) WITH NOCHECK, one consequence you should be aware of is that SQL Server will no longer trust that constraint. It flags it as not trusted. Actually, it’s already flagged as not trusted when you disable the constraint.

SQL Server has a is_not_trusted flag that it sets to 1 when you disable a foreign key constraint (which means it’s not trusted), and the only way to set it to 0 (trusted) is to specify WITH CHECK when re-enabling the constraint. On the other hand, using WITH NOCHECK just enables it without checking existing data.

By using WITH CHECK, you ensure that the constraint checks all existing data before it is enabled. The only way it can be enabled is if all existing data conforms to the constraint. Once it has checked all existing data, the constraint can then be trusted.

Example 4 – Check the Trusted/Disabled Status

You can check the trusted and disabled status by querying the sys.foreign_keys system view.

Like this:

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

Result:

+-------------------+---------------+------------------+
| Constraint        | is_disabled   | is_not_trusted   |
|-------------------+---------------+------------------|
| FK_Albums_Artists | 0             | 1                |
| FK_Albums_Genres  | 0             | 0                |
+-------------------+---------------+------------------+

This tells me that the constraint that I enabled in the previous example (FK_Albums_Artists) is not trusted.

This is because I enabled it using the default setting, which is WITH NOCHECK.

If I re-enable it using WITH CHECK, here’s what happens:

ALTER TABLE Albums 
WITH CHECK CHECK CONSTRAINT FK_Albums_Artists;

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

Result:

+-------------------+---------------+------------------+
| Constraint        | is_disabled   | is_not_trusted   |
|-------------------+---------------+------------------|
| FK_Albums_Artists | 0             | 0                |
| FK_Albums_Genres  | 0             | 0                |
+-------------------+---------------+------------------+

Fortunately in this case I didn’t have any data that violated the constraint, so the constraint was successfully enabled and its trust was restored.

If there was data that violated the constraint, an error would’ve been displayed, and I would be forced to fix the data before I could restore trust in the constraint.