If you’re getting SQL Server error 1989 that reads something like “Cannot enable foreign key constraint ‘FK_Jobs_Employees’ as index ‘PK__Employee__7AD04FF1A39ECAB1’ on referenced key is disabled“, it’s probably because you’re trying to enable a foreign key, when the index that it references is disabled.

For example, if you’ve disabled a primary key on the parent table, and then try to enable a foreign key that references that primary key, then you’ll get this error.

To fix this issue, enable the index for the primary key/referenced key before enabling the foreign key.

Example of Error

Let’s start by disabling the primary key on a table:

ALTER INDEX PK__Employee__7AD04FF1A39ECAB1 ON Employees DISABLE;

Output:

Warning: Foreign key 'FK_Jobs_Employees' on table 'Jobs' referencing table 'Employees' was disabled as a result of disabling the index 'PK__Employee__7AD04FF1A39ECAB1'.
Warning: Index 'UQ_Employees_Email' on table 'Employees' was disabled as a result of disabling the clustered index on the table.

First of all, we get a couple of warnings. The first mentions that a foreign key was disabled on another table. That’s the foreign key that we’ll try to enable here to get the error.

The second warning told us that another index was disabled at the same time. So we should not forget that whenever we disable a primary key/clustered index. When we want to re-enable the primary key, we’ll need to re-enable any other indexes that had been disabled at the same time.

So now to the error.

Let’s try to enable the foreign key:

ALTER TABLE Jobs
CHECK CONSTRAINT FK_Jobs_Employees;

Output:

Msg 1989, Level 16, State 1, Line 1
Cannot enable foreign key constraint 'FK_Jobs_Employees' as index 'PK__Employee__7AD04FF1A39ECAB1' on referenced key is disabled.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

As the error message suggests, we can’t enable the foreign key while the primary key that it references is disabled.

Solution

To fix this issue we must enable/rebuild the primary key first.

To do this, we use the ALTER INDEX statement with the REBUILD option. We can enable all indexes at once, or we can enable the clustered index, then follow that up with the unclustered indexes if required.

To rebuild all indexes on the table, we can use the ALL keyword:

ALTER INDEX ALL ON Employees REBUILD;

That should enable the primary key index along with all unclustered indexes.

We can alternatively rebuild each index individually, starting with the clustered/primary key index:

ALTER INDEX PK__Employee__7AD04FF1A39ECAB1 ON Employees REBUILD;
ALTER INDEX UQ_Employees_Email ON Employees REBUILD;

Here, I rebuilt the clustered index first, followed by the unclustered one. If I’d done it the other way around, I’d have received a different error. That’s because the clustered index must be enabled before any unclustered ones are rebuilt.

Anyway, so after enabling the primary key’s index (and any other indexes), we can go ahead and enable the foreign key:

ALTER TABLE Jobs
CHECK CONSTRAINT FK_Jobs_Employees;

That enables the foreign key called FK_Jobs_Employees on the Jobs table.

We can check whether the foreign key is enabled or disabled with the following query (just replace Jobs with the name of your table):

SELECT 
  OBJECT_NAME(parent_object_id) AS [FK Table],
  name AS [Foreign Key],
  OBJECT_NAME(referenced_object_id) AS [PK Table],
  is_disabled
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('Jobs');

Output:

FK Table  Foreign Key        PK Table   is_disabled
--------  -----------------  ---------  -----------
Jobs      FK_Jobs_Employees  Employees  false      

We can see that it’s not disabled (which means that it’s enabled).