Dropping Primary Key

[jfokas]

I need to drop on the primary key on a table within a script but keep getting an error. The syntax should be straight-forward:

ALTER TABLE accession_2 DROP PRIMARY KEY

Yet, I get this error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PRIMARY'

This is a SQL Server 2000 database. Can anyone tell me what I'm missing. I'm sure the answer is rediculously simply but I'm not seeing it.

 

[Catadmin]

You have to drop it as a constraint.

ALTER TABLE Accession_2 DROP CONSTRAINT <Primarykeyname>

Go to the design of the table and find out what your primary key name is and insert it appropriately.

You might also want to add this code to your script to prevent an attempt to drop it if it doesn't exist (or already has been dropped):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<primarykeyname>]') 
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
Alter table ...

Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!