Dropping a Primary Key Constraint in SQL Server – is it easy? May be. May be not!

Hello Folks,

We also know some basics about the Primary Key.

The Primary Key constraint uniquely identifies each record in a database table. Primary Keys must contain unique values. By default, Primary key creates a clustered index on the column on which they are defined.  A Primary key column cannot contain NULL values.

There are multiple ways of creating, altering and deleting Primary Key Constraint on a table:

How to create a PK with a constrain name while creating a table?

CREATE TABLE Students
(
S_Id int NOT NULL,
LName varchar(255) NOT NULL,
FName varchar(255),
Email_Id varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LName)
)

Note that in the above code, we are explicitly specifying the name of the PK constraint.

How to add a PK constraint later (after the table was created)?

ALTER TABLE Students
ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LName)

So, how do you remove it? Well, its simple:

ALTER TABLE Students
DROP CONSTRAINT pk_StudentID

But what if you created a table with PK like this:

CREATE TABLE Students
(
S_Id int NOT NULL PRIMARY KEY,
LName varchar(255) NOT NULL,
FName varchar(255),
Email_Id varchar(255),
Address varchar(255),
City varchar(255)
)

Note that PK constraint is created in the above code but you have not specified any constraint name. Now how do you drop it without the name?

Will this work?

ALTER TABLE Students
DROP Primary Key

Well, not 🙁 – this works MySQL but not in SQL Server. And the error message is very interesting too 🙂

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Primary'.

Well don’t worry; we have a solution for this:

As you have seen so far that if you know the name of Primary Key Constraint, you can easily be drop it.

But in the above case, SQL Server has automatically/implicitly assigned a name to this PK constraint. So the question is how to find that out? Well there are multiple ways again. Quickest could be:

See these few steps carefully:

  • In your SQL Query Window, write the name of the Table in which you have declared Primary Key and Select it.

1_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

  • Press ALT + F1 on your Keyboard. This is mainly being done to get all the details of the Students table. They are being displayed under the Results tab.

2_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

   

3_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

  • So scrolling down, you will find the Constraint Name:

4_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

  • Now after knowing the Constraint Name just use the simple query, which we saw earlier:
ALTER TABLE Students
DROP CONSTRAINT PK__Students__A3DFF08D0AD2A005

5_Dropping_a_Primary_Key_Constraint_in_SQL_Server_is_it_easy_May_be_May_be_not!

Hope you enjoyed reading this. Do comment so that I can imporve in my next post.

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

14 Comments on “Dropping a Primary Key Constraint in SQL Server – is it easy? May be. May be not!”

  1. You can also use the script as follows :

    01 DECLARE @Constraint_Name SYSNAME, @Table_Name SYSNAME, @Constraint_Type NVARCHAR(200), @Database_Name SYSNAME

    02 DECLARE @SQLStmt NVARCHAR(4000), @Params NVARCHAR(4000)

    03 SELECT @Table_Name = ‘Test_a’ –‘Table_Name’

    04 , @Constraint_Type = ‘PRIMARY KEY’

    05 SELECT @Constraint_Name = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

    06 WHERE TABLE_NAME = @Table_Name

    07 AND CONSTRAINT_TYPE = @Constraint_Type

    08

    09 SELECT @SQLStmt = N’

    10 RAISERROR(”PRIMARY KEY CONSTRAINT [%s] is being dropped from Table [%s] on Database [%s]”,0,1,@Constraint_Name,@Table_Name,@Database_Name) WITH NOWAIT

    11 ALTER TABLE ‘+@Table_Name+’

    12 DROP CONSTRAINT ‘+@Constraint_Name+’

    13 ‘

    14 , @Params = N’@Table_Name SYSNAME, @Constraint_Name SYSNAME, @Database_Name SYSNAME’

    15 , @Database_Name = DB_NAME()

    16 EXEC sp_executeSQL @stmt = @SQLStmt, @params = @Params

    17 , @Table_Name = @Table_Name

    18 , @Constraint_Name = @Constraint_Name

    19 , @Database_Name = @Database_Name

    The above can be even wrapped in APIs (SQL Stored Procedures, JOB SQL steps etc)

  2. Hi Piyush

    With Help of this below script we can drop primary key constraint without Constraint name Please try…………………………….

    DECLARE @SQL VARCHAR(2000)

    SET @SQL=’ALTER TABLE students DROP CONSTRAINT’ + ‘ ‘ +

    (select name from sysobjects where xtype = ‘PK’ and

    parent_obj = object_id(‘students’))

    exec (@SQL)

    Thanks

    Manoj (mkumar_dba)

  3. Hi SriramSu and Manoj, i am glad to see your response. You both have given a very nice queries, and thanx for the great learning.

    Hi Prashant, its really not necessary to give the constraint name specially for Primary Key . But if want to give explicitly as a constraint you might have to add a constraint name.

  4. that’s fine article about droping primary key in the table,but arises qusetion when the table linked to another table(s) with primary key?i meannig parent and child tables,

    or

    …also when i created trigger for preventing to drop data inthe child table, and tested it, first fired the error statment:: The DELETE statement conflicted with the REFERENCE constraint “FK_makavshirebeli_avtori”. The conflict occurred in database “my db”, table “dbo.makavshirebeli”, column ‘avtoriID’.,but not trigger’s statment

  5. how to set more than one primary key in a table using SQL SERVER 2008 r2?..

    In pl/sql…Composite key is referred..

    and thanks for your document

  6. @mahalakshmi thanks 🙂

    For adding a NULL constraint:

    1 ALTER TABLE
    2 ALTER COLUMN NOT NULL

    For removing a NULL constraint:

    1 ALTER TABLE
    2 ALTER COLUMN NULL

  7. @kavi… welcome 🙂

    You can only have one primary key, but you can have multiple columns in your primary key, which is called as Composite Primary Key.

  8. Thank you very much sir . Before reading this article I have spent many hours but no use. your post is greatly appreciated …….thank you for your time………

Leave a Reply

Your email address will not be published.