How to DROP a Primary Key in SQL Server

Dropping a primary key in any RDBMS is a critical operation that requires careful consideration, especially when there are related foreign keys in other tables.

The decision to drop a primary key is not to be taken lightly.

That said, this article walks through the process of how to drop a primary key in SQL Server, while highlighting important considerations.

Understanding the Impact

Before dropping a primary key, it’s crucial to understand its implications.

Here are some of the main things to consider before we decide to drop a primary key:

Data Integrity: The primary key ensures the uniqueness of records in a table. Dropping it will remove your ability to enforce, and vouch for, the uniqueness of those records.
Relationships: Other tables may have foreign keys that reference this primary key. You’ll need to drop such foreign keys before you drop the primary key. This therefore has wider implications with regards to the referential integrity of the database as a whole.
Performance: Indexes associated with the primary key will also be removed, potentially affecting query performance.

Now that we’re clear on the impact, let’s walk through the steps involved in dropping a primary key.

Steps to Dropping a Primary Key

In consideration of the above impacts of dropping a primary key, we shouldn’t (and often can’t) just drop the primary key without taking some other steps first.

For example, we first need to know the name of the primary key constraint. We also need to know if there are any foreign keys that reference the primary key, because SQL Server won’t let us drop a primary key if it has any foreign keys pointing to it.

Step 1: Identify the Primary Key

First, we need to identify the name of the primary key constraint. We can do this with the following query:

SELECT
    name
FROM 
    sys.key_constraints
WHERE 
    type = 'PK' 
    AND SCHEMA_NAME(schema_id) = 'dbo'
    AND OBJECT_NAME(parent_object_id) = 'Employees';

Replace Employees with the name of the table that contains the primary key you want to drop.

Step 2: Check for Related Foreign Keys

Before dropping the primary key, check if there are any foreign keys referencing it:

SELECT
    OBJECT_NAME(parent_object_id) AS ForeignKeyTable,
    name AS ForeignKeyName,
    OBJECT_NAME(referenced_object_id) AS PrimaryKeyTable,
    is_disabled
FROM 
    sys.foreign_keys
WHERE 
    SCHEMA_NAME(schema_id) = 'dbo'
    AND OBJECT_NAME (referenced_object_id) = 'Employees';

Another way to get the foreign keys that reference a particular table is to use the sp_fkeys system stored procedure:

EXEC sp_fkeys 
    @pktable_name = 'Employees', 
    @pktable_owner = 'dbo';

Step 3: Drop Related Foreign Keys (if necessary)

If foreign keys exist, you’ll need to drop them first:

ALTER TABLE Jobs
DROP CONSTRAINT FK_Jobs_Employees;

Repeat this for all foreign keys referencing your primary key (replacing Jobs with the table that contains the foreign key, and FK_Jobs_Employees with the foreign key itself).

Step 4: Drop the Primary Key

Now you can drop the primary key:

ALTER TABLE Employees
DROP CONSTRAINT PK__Employee__7AD04FF1A39ECAB1;

Replace PK__Employee__7AD04FF1A39ECAB1 with the name of your primary key and Employees with the table that it belongs to.

Considerations and Best Practices

Backup: Always backup your database before making structural changes.
Testing: Perform this operation in a test environment first.
Assessing the Impact: Ensure your application can handle the absence of the primary key.
Recreating Constraints: Plan for recreating necessary constraints and indexes after the operation.
Permissions: Ensure you have the necessary permissions to alter the table structure. Dropping the primary key requires ALTER permission on the table.

Database.Guide