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
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.