Jan
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
sp_helptext ‘DBObjectName’
[Or]
select object_definition(object_id(’DBObjectName’))
Tags: drop, drop primary key, Primary key, SQL Server
