It might happen that we defined a primary key constraint on the wrong column, and then we need to delete (drop) that. In this article, we will see how to drop a primary key in SQL Server using Management Studio and T-SQL.
DROP primary key constraint
Let’s create two tables: Movie and Genre. Then, we will define a primary key for those tables and delete them.
The following script is used to create the Genre table with a primary key named PK_Genre on the GenreName column.
-- Creating the Genre table with a primary key named PK_Genre CREATE TABLE Genre ( GenreId INT NOT NULL, GenreName NVARCHAR(20) NOT NULL, CONSTRAINT PK_Genre PRIMARY KEY (GenreName) )
For the Movie table, we will specify its primary key on the MovieName column. However, we won’t specify the name of the constraint. In this case, SQL Server will generate the constraint name automatically.
-- Creating the Movie table with a primary key without specifying the constraint name CREATE TABLE Movie ( MovieId INT NOT NULL, MovieName NVARCHAR(20) NOT NULL PRIMARY KEY )
Next, we will remove the primary key in the Movie and Genre tables using Management Studio and T-SQL.
Using Management Studio
The first option is to delete the PK_Genre using the Object Explorer. The following steps explain how to find the PK_Genre in the Object Explorer and then remove it from the table.
Step 1. In the Object Explorer, locate the Genre table. Then, expand the Keys folder under it. We will see that the PK_Genre is listed under this folder.
Figure 1. The Keys folder
Step 2. Right-click on the PK_Genre, then select Delete in the context menu.
Figure 2. Deleting the PK_Genre
Step 3. Verify that the PK_Genre is listed in the Objects to be deleted grid. Then, click the OK button to confirm.
Figure 3. The Delete Object window
Another option to delete the primary key of a table is by opening the table in Table Designer. We can follow the following steps to open the Genre table in the design view and then delete its PK_Genre primary key.
Step 1. Locate the Genre table in the Object Explorer. Right-click on it then select Design.
Figure 4. Opening a table in the design view
Step 2. Right-click on the row selector of the GenreName column then choose Remove Primary Key.
Figure 5. Removing a primary key using Table Designer.
Step 3. Press Ctrl+S to save the changes.
Using DDL command (T-SQL)
Following is the DDL syntax to drop a primary key from a table.
ALTER TABLE <tablename> DROP CONSTRAINT <pk_constraintname>
We can simply replace the tablename with the name of our table and the pk_constraintname with its primary key constraint name. However, sometimes, we create our table without specifying the primary key constraint name as we did for our Movie table. In this case, we need to identify the name of the constraint before deleting it.
The following script finds the name of the primary key constraint of the Movie table.
SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'Movie' GO
The result would be as follows:
After we know that the constraint name is PK__Movie__F5C0AEA4FD8AC6AA, we can delete it using this command:
ALTER TABLE Movie DROP CONSTRAINT PK__Movie__F5C0AEA4FD8AC6AA GO
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.