SQL SERVER – How to Drop Primary Key Contraint

One area that always, unfailingly pulls my interest is SQL Server Errors and their solution. I enjoy the challenging task of passing through the maze of error to find a way out with a perfect solution. However, when I received the following error from one of my regular readers, I was a little stumped at first! After some online probing, I figured out that it was actually syntax from MySql and not SQL Server. The reader encountered error when he ran the following query.

ALTER TABLE Table1
DROP PRIMARY KEY
GO

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘PRIMARY’.

As mentioned earlier, this syntax is for MySql, not SQL Server. If you want to drop primary key constraint in SQL Server, run the following query.

ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

Let us now pursue the complete example. First, we will create a table that has primary key. Next, we will drop the primary key successfully using the correct syntax of SQL Server.

CREATE TABLE Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARY KEY CLUSTERED (
Col1 ASC)
)
GO

/* For SQL Server/Oracle/MS ACCESS */
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

/* For MySql */
ALTER TABLE Table1
DROP PRIMARY KEY
GO

I hope this example lucidly explains how to drop primary key. This, no doubt, is a very simple and basic explanation, but when I chanced upon the error message it aroused curiosity in me.  As you all know by now I love sharing new issues and ideas with my readers. So I have included this interesting error in my blog.

Let me have your feedback on this post and also, do feel free to share with me your ideas as well!

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Questions and Answers with Database Administrators
Next Post
SQLAuthority News – Release of SQL Server 2008 R2 Announced

Related Posts

60 Comments. Leave new

  • Hi sir, consider I have 3 primary keys in a table (sql server). If i drop the primary key constraint, all the primary keys are droped, how i drop only certain primary key.

    Reply
  • Your blog is an invaluable resource. Thanks for sharing your knowledge.

    Reply
  • Love all your posts! Thank you!

    Reply
  • when we create primary key on a table in sql server without giving name how we drop that constarinat

    Reply
    • Even if you don’t give the name, SQL would give its own name having PK_ in the start. You need to find that using catalog views and drop it.

      Reply
  • Merlin Griffin
    October 4, 2018 7:45 pm

    Tried it in SQL Server 2014 and got this error.
    Msg 3728, Level 16, State 1, Line 4
    ‘PK_ENVSAMPlEPOINTS_ENVSAMPOINTID’ is not a constraint.
    Msg 3727, Level 16, State 0, Line 4
    Could not drop constraint. See previous errors.

    Reply
  • Hi Sir,

    I need to Drop Primary Constraint using IF EXIST , When I am doing in SQL SERVER 2008 below command
    ALTER TABLE[TABLE_NAME] DROP CONSTRAINT IF EXIST [PK_ITEMS]
    it gives compilation error saying syntax error near if

    Reply
  • ALTER TABLE Table1
    DROP CONSTRAINT PK_Table1_Col1
    GO
    This is actually wrong syntax. It should be

    ALTER TABLE Table1
    DROP PK_Table1_Col1
    GO

    Reply
  • Stephani Rouse
    August 10, 2020 7:26 am

    What happens if you migrate a table from Access and you don’t know what the PK is?

    Reply

Leave a Reply

Menu