How to Disable a Primary Key in SQL Server

Posted on by Ian

Under most circumstances, disabling a primary key is a bad idea. A very bad idea. After all, we implement primary keys in the name of data integrity, and by disabling a primary key, we could compromise that effort.

But there may be cases where you need to disable a primary key, for one reason or another. For example, to facilitate data migration efforts, or bulk inserts, to perform certain maintenance tasks, or simply to insert dummy data in your development environment.

Whatever the reason, here’s how to disable a primary key in SQL Server.

Step 1: Identify the Primary Key’s Index Name

We first need to find the name of the index associated with our primary key:

SELECT 
    name, 
    is_primary_key,
    is_disabled
FROM 
    sys.indexes
WHERE 
    object_id = OBJECT_ID('YourTableName')
AND 
    is_primary_key = 1;

Replace YourTableName with the name of your table.

That returns the name of the primary key’s index, and it also includes a column that indicates whether the index is currently disabled or not. It also includes the is_primary_key column, which is probably a bit superfluous, given we’re filtering by that column, but it’s there in case you want to see it.

The reason we’re getting the index and not the constraint itself, is because we disable a primary key by disabling its index.

Step 2: Disable the Primary Key

Once you have the index name, you can disable it using the ALTER INDEX statement:

ALTER INDEX PK_YourIndexName ON YourTableName DISABLE;

Replace PK_YourIndexName with the actual name of your primary key’s index, and YourTableName with the name of your table.

As you can see, we disable a primary key by disabling its index.

Example

Let’s say we have a table named Employees with a primary key on the EmployeeID column. Here’s how we would disable this primary key.

Find the primary key’s index:

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

Output:

name                            is_primary_key  is_disabled
------------------------------  --------------  -----------
PK__Employee__7AD04FF1A39ECAB1  true            false

Now that we’ve got the name of the primary key’s index, we can disable that index:

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.

Disabling the primary key resulted in a couple of warnings. The first warning mentions that a foreign key was disabled on another table. The second warning told us that another index was disabled at the same time.

So this is something we should always remember 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.

By the way, you may or may not see such warnings, depending on the tool you use to access SQL Server.

Verifying Disabled Primary Keys

To check if a primary key is disabled, we can query the sys.indexes system view again:

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

Output:

name                            is_primary_key  is_disabled
------------------------------  --------------  -----------
PK__Employee__7AD04FF1A39ECAB1  true            true

Re-enabling a Disabled Primary Key

To re-enable a disabled primary key, we can use the REBUILD option:

ALTER INDEX PK__Employee__7AD04FF1A39ECAB1 ON Employees REBUILD;

Don’t forget 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.

As for the nonclustered indexes on the same table, we could replace the above code with this:

ALTER INDEX ALL ON Employees REBUILD;

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

Best Practices and Considerations

  • Be cautious: Disabling a primary key removes a crucial data integrity constraint. Make sure you understand the implications before proceeding.
  • Use in non-production first: Always test this operation in a non-production environment before applying it to a production database.
  • Plan for re-enabling: Remember to re-enable the primary key after you’ve completed your maintenance or bulk insert operations.
  • Check for violations: Before re-enabling the primary key, check for any potential violations that may have occurred while it was disabled.
  • Consider alternatives: For some scenarios, we might want to consider dropping and recreating the primary key instead of disabling it.

Primary Key Basics

See my article What is a Primary Key? for a general understanding of primary keys, and to understand why we should think very carefully before disabling them.