If you’ve got a primary key in SQL Server that you need to enable, you’ll need to enable its index. The index is how we enable and disable primary key constraints in SQL Server.

More specifically, we enable a primary key index (and other indexes) by rebuilding them.

Below is an example of enabling a primary key in SQL Server.

Example

As mentioned, we enable a primary key by rebuilding its index. When we rebuild a primary key’s index, we have the option of enabling just that primary key’s index or all indexes on the table.

We can enable just the primary key’s index like this:

ALTER INDEX PK__Employee__7AD04FF1A39ECAB1 ON Employees REBUILD;

That rebuilds a primary key called PK__Employee__7AD04FF1A39ECAB1 on a table called Employees.

Or we can rebuild all indexes on the table:

ALTER INDEX ALL ON Employees REBUILD;

That rebuilds all indexes on the table, including the primary key’s index.

However, it doesn’t enable any foreign keys that reference the primary key. In case you don’t know, disabling a primary key also disables any foreign keys that reference it. So when we re-enable the primary key, we need to re-enable any foreign keys that reference the primary key (as well as any nonclustered indexes on the table that were disabled at the same time the primary key was disabled).

Check the Result

After enabling the primary key, we can verify the result with the following query:

SELECT
    name,
    type_desc,
    is_primary_key,
    is_disabled
FROM 
    sys.indexes
WHERE 
    object_id = OBJECT_ID('dbo.Employees');

That returns a different row for each index on the table. That way we can verify that all indexes were enabled (if that’s what we want to do).

The query includes the is_primary_key column, which tells us which index is the primary key’s index, and the is_disabled column, which tells us whether or not the primary key is currently enabled or disabled.

We can also use this query to find the name of the primary key before enabling it, as well as checking its enabled/disabled status.

Enable Foreign Keys

As mentioned, enabling a primary key won’t automatically enable any foreign keys that reference it. So we’ll need to enable those separately.

Here’s an example:

ALTER TABLE Jobs
CHECK CONSTRAINT FK_Jobs_Employees;

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

See How to Enable a Foreign Key Constraint in SQL Server for more information.