How to Drop and Recreate a Primary Key Constraint in SQL Server

by

In relational database management systems (RDBMSs) like SQL Server, the primary key constraint is a fundamental element that uniquely identifies each record in a table and enforces data integrity. However, there are situations where you might need to drop and then recreate this constraint. Such situations could include modifying the primary key columns, changing data types, resolving design issues, etc.

Understanding how to safely remove and reapply primary key constraints is extremely important for maintaining database consistency and minimizing downtime during schema changes.

In this article, we’ll walk through how to safely drop and then re-add a primary key constraint without breaking your database.

Why Drop and Recreate a Primary Key?

First, you may be wondering why we would want to drop and recreate a primary key? Why not just modify whatever you need to modify? Well it’s a little bit more complicated than that. Here are some of the main reasons you might need to drop a primary key, only to recreate it later:

  • Changing the PK column(s): You cannot directly modify a column that is part of a primary key. You need to drop the PK constraint first.
  • Changing the data type or length of PK columns: Same as above, you drop the constraint, alter the column, then recreate the PK.
  • Fixing PK issues or redefining the PK: Sometimes you need to redefine the PK to a different column or set of columns.
  • Index rebuilds or performance tuning: Since a PK automatically creates a unique clustered index (by default), you might drop and recreate for advanced tuning.
  • Data import or bulk operations: You might temporarily drop constraints to bulk load data faster, then recreate the PK.

How to Drop a Primary Key Constraint

In SQL Server, you must know the name of the primary key constraint to drop it.

You can find it via:

SELECT name 
FROM sys.key_constraints 
WHERE type = 'PK' 
AND OBJECT_NAME(parent_object_id) = 'YourTableName';

Then, to drop the PK constraint:

ALTER TABLE YourTableName
DROP CONSTRAINT PK_ConstraintName;

From here, you can make whatever modifications you need to make before recreating the primary key constraint.

How to Recreate a Primary Key Constraint

Once dropped, you can recreate it like this:

ALTER TABLE YourTableName
ADD CONSTRAINT PK_ConstraintName PRIMARY KEY (ColumnName);

And that’s it. The primary key has been dropped and recreated.

Example

Let’s say you have a table Employees with a primary key on EmployeeID. Now, you want to change the data type of the EmployeeID column. In this case you’ll need to drop the PK and recreate it.

Here’s a complete step-by-step example to demonstrate the full process.

Let’s start by creating the table and selecting the primary key:

-- ================================================
-- Step 1: Create the Employees table (fresh setup)
-- ================================================
DROP TABLE IF EXISTS Employees;
GO

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE,
    CONSTRAINT PK_Employees_EmployeeID PRIMARY KEY (EmployeeID)
);
GO

-- ================================================
-- Step 2: Find the PK constraint name (optional)
-- ================================================
SELECT name 
FROM sys.key_constraints 
WHERE type = 'PK' 
AND OBJECT_NAME(parent_object_id) = 'Employees';
GO

Output:

PK_Employees_EmployeeID

In this case, we named the primary key when we created it, so we already knew the name. But we checked the database anyway, and we can see that the primary key name is indeed PK_Employees_EmployeeID as specified.

Now let’s drop the primary key and make some schema changes, before recreating the primary key.

But first, let’s try changing the data type without dropping the primary key:

-- =================================================================
-- Optional Step: Try changing the data type WITHOUT dropping the PK
-- =================================================================
ALTER TABLE Employees
DROP CONSTRAINT PK_Employees_EmployeeID;
GO

Result:

Msg 5074, Level 16, State 1, Line 1
The object 'PK_Employees_EmployeeID' is dependent on column 'EmployeeID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN EmployeeID failed because one or more objects access this column.

As expected, it returned an error. Actually, it returned two errors.

Fortunately, we already know that it was because we tried to modify the column while it had a primary key constraint applied to it. So we must drop the PK constraint before making the change.

Let’s do that now:

-- ================================================
-- Step 3: Drop the PK constraint
-- ================================================
ALTER TABLE Employees
DROP CONSTRAINT PK_Employees_EmployeeID;
GO

-- ================================================
-- Step 4: Make schema changes
-- (Example: change EmployeeID from INT to BIGINT)
-- ================================================
ALTER TABLE Employees
ALTER COLUMN EmployeeID BIGINT NOT NULL;
GO

-- ================================================
-- Step 5: Recreate the PK constraint
-- ================================================
ALTER TABLE Employees
ADD CONSTRAINT PK_Employees_EmployeeID PRIMARY KEY (EmployeeID);
GO

-- ================================================
-- Step 6: Find the PK constraint name (optional)
-- ================================================
SELECT name 
FROM sys.key_constraints 
WHERE type = 'PK' 
AND OBJECT_NAME(parent_object_id) = 'Employees';
GO

Output:

PK_Employees_EmployeeID

The primary key was successfully dropped and recreated. And the data type of the underlying table was changed from INT to BIGINT.

Points to Remember

  • Knowing the Constraint Name:
    You’ll need to identify the PK constraint name first. If you didn’t specify it explicitly when creating the PK, SQL Server generates one with a system-generated name. That’s why we queried sys.key_constraints. If you already know the PK name, then you won’t need to query this view (although you may still want to, just to check).
  • Data Integrity:
    Dropping the primary key removes the uniqueness and NOT NULL enforcement on the column(s). Be cautious about potential duplicate or NULL values during this window.
  • Foreign Key Dependencies:
    If other tables reference this PK with foreign keys, you may not be able to drop the PK until you drop or disable those foreign keys. You’ll need to handle those dependencies carefully.
  • Downtime / Locking:
    Dropping and recreating constraints can lock the table and affect availability. Plan maintenance windows accordingly.
  • Clustered vs Non-Clustered PK:
    By default, creating a primary key creates a clustered index. Most of the time this is what you’ll want. However, when recreating, you can specify if you want a CLUSTERED or NONCLUSTERED PK: ADD CONSTRAINT PK_Employees_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID);
  • Multiple Columns in PK:
    If the PK spans multiple columns, you’ll need to list them all when creating/recreating it: ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID, DepartmentID);

Summary

Here’s the process in a nutshell:

  • Find the primary key constraint name from system tables.
  • Drop the PK constraint with ALTER TABLE DROP CONSTRAINT.
  • Make necessary schema changes if any.
  • Recreate the PK with ALTER TABLE ADD CONSTRAINT PRIMARY KEY.
  • Mind dependencies, locking, and data integrity risks during this process.