Modify, Delete Primary Key in SQL Server

Here you will learn how to modify or delete an existing primary key in a table.

Delete Primary Key using T-SQL

Use the ALTER TABLE DROP CONSTRAINT command to delete a primary key from a table.

The following T-SQL script deletes a primary key PK_Employee_EmployeeID from the Employee table.

ALTER TABLE Employee DROP CONSTRAINT PK_Employee_EmployeeID;   

Delete a Primary Key using SSMS

In the Object Explorer, expand the table that contains the primary key to be deleted.

Expand Keys node and Right-click on the key, and select Delete.

In the Delete Object dialog box, verify the correct key is specified and select OK.

Delete Primary Key using Table Designer

In Object Explorer, right-click the table whose primary key has to be deleted. Select Design.

In the table designer, right-click on the row with the primary key and choose Remove Primary Key.

Save the table to reflect the changes.

Modify Primary Key Using SSMS

You can modify the primary key of a table by changing the key name, columns order, clustered option, or fill factor.

To modify a primary key using T-SQL, you must first delete the existing primary key constraint and then re-create it with the new definition.

To modify or delete a primary key using SSMS right-click on the table for which you want to modify the primary key and click on Design option to open a table in the design mode.

Now, right-click in the table designer and choose Indexes/Keys from the menu, as shown below.

In the Indexes/Keys dialog box, select the primary key index, as shown below.

In the Indexes/Keys dialog box, you can rename a primary key, set the clustered option, set the fill factor, change the primary key column or change the column order in case of the composite primary key.

Click on the Add button to add a new index or primary key.

Click on the Delete button to delete the selected index or primary key.

Save the changes by pressing Ctrl + s. If it prevents saving and display a message to re-create a table, then go to Tools menu, click Options, expand Designers, and then click Table and Database Designers. Clear the Prevent saving changes that require the table to be re-created check box, as shown below.