Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Remove Primary Keys in SQL Server – Querychat

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:

Name
---------------------------
PK__Movie__F5C0AEA4FD8AC6AA

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc