If you’re getting SQL Server error 1987 that reads something like “Cannot alter nonclustered index ‘UQ_Employees_Email’ on table ‘Employees’ because its clustered index is disabled“, it’s probably because you’re trying to rebuild a nonclustered index when the clustered index for the table is disabled.

To fix this issue, either enable/rebuild the table’s clustered index first, then try again, or enable all indexes at once.

The clustered index will typically be the primary key index (unless you’ve specified another clustered index for the table).

Example of Error

Let’s start by disabling all indexes on a table:

ALTER INDEX ALL 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. One of them mentions that a foreign key was disabled on another table. That’s a different issue to the error that this article is about, but we’ll cover this warning when we fix the error.

Anyway, when we ran the code, it disabled the clustered and any unclustered indexes on the table.

In my case, there’s a nonclustered index called UQ_Employees_Email that was disabled in addition to the clustered index (which is the primary key index).

So now to the error.

Let’s try to enable/rebuild that unclustered index:

ALTER INDEX UQ_Employees_Email ON Employees REBUILD;

Output:

Msg 1987, Level 16, State 1, Line 1
Cannot alter nonclustered index 'UQ_Employees_Email' on table 'Employees' because its clustered index is disabled.

As the error message suggests, we can’t rebuild the nonclustered index while the table’s clustered index is disabled.

Solution

To fix this issue we must enable the table’s clustered index.

To enable an index, 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 clustered one along with all unclustered indexes.

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

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

Here, I name the clustered index first, followed by the unclustered one.

I can run both of these options without receiving any errors.

But there’s one more thing I need to take care of – the foreign key.

Back when we disabled the primary key index, this also disabled a foreign key that referenced that primary key. So now that we’ve successfully rebuilt the indexes on our Employees table, we should not forget the disabled foreign key on the Jobs table. That’s still disabled.

So let’s 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 the foreign key using the following query:

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');

Just replace Jobs with the name of your table.