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 an error when they ran the following query:

ALTER TABLE Table1 DROP PRIMARY KEY
GO

As mentioned earlier, this syntax is for MySql, not SQL Server. If you want to drop a primary key constraint in SQL Server, you need to use a different syntax. Here’s the correct query:

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1_Col1
GO

Let’s now pursue the complete example. First, we will create a table that has a 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 a 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, feel free to share with me your ideas as well!