Delete primary keys
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
You can delete (drop) a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL.
When the primary key is deleted, the corresponding index is deleted. This may be the clustered index of the table, causing the table to become a heap. For more information, see Heaps (Tables without Clustered Indexes). Most tables should have a clustered index. To re-create the primary key, see Create Primary Keys.
Primary keys can be referenced by foreign keys in another table. If referenced by a foreign key, you'll need to drop referencing foreign keys first, then drop the primary key. For more information, see Primary and Foreign Key Constraints.
Permissions
Requires ALTER permission on the table.
Use SQL Server Management Studio
To delete a primary key constraint using Object Explorer
In Object Explorer, expand the table that contains the primary key and then expand Keys.
Right-click the key and select Delete.
In the Delete Object dialog box, verify the correct key is specified and select OK.
To delete a primary key constraint using Table Designer
In Object Explorer, right-click the table with the primary key, and select Design.
In the table grid, right-click the row with the primary key and choose Remove Primary Key to toggle the setting from on to off.
Note
To undo this action, close the table without saving the changes. Deleting a primary key cannot be undone without losing all other changes made to the table.
On the File menu, select Save table name.
Use Transact-SQL
To delete a primary key constraint
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. The example first identifies the name of the primary key constraint and then deletes the constraint.
USE AdventureWorks2022; GO -- Return the name of primary key. SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive'; GO -- Delete the primary key constraint. ALTER TABLE Production.TransactionHistoryArchive DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID; GO