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)
61 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.
Your blog is an invaluable resource. Thanks for sharing your knowledge.
Love all your posts! Thank you!
Thanks Enid.
when we create primary key on a table in sql server without giving name how we drop that constarinat
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.
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.
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
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
What happens if you migrate a table from Access and you don’t know what the PK is?
Hi Pinal-
Is there any concern if I drop and re-create Primary key(clustered index) on very large table during downtime ? This will free up around 280 gb unallocated space for my production database. This is one of the largest table of our database. I am trying to recover some space and reduce backup size of our current 3 TB production database.
Technically It should not create any performance issue or slowness for the application right ? Can you please share your feedback ?
Thanks in advance !