CODEWITHSUNDEEP
sqlsql-server-2008
Nix
Nix

Reputation: 321

SQL Server 2008 cannot drop constraint

I'm trying to drop a primary key constraint from a table using the following

ALTER TABLE SchemaName.LabourGrade DROP CONSTRAINT Labour_Grade_pk

and getting the error Labour_Grade_pk is not a constraint.

when I do

SELECT * FROM sysobjects WHERE name = 'LabourGrade_pk'

I get one row back. It does have FKs to it so I tried dropping those first but same problem. I only want to drop the PK so as to change the column's datatype, is there better way to do this?

Upvotes: 4

Views: 4879

Answers (2)

Martin Smith
Martin Smith

Reputation: 452957

I only want to drop the PK so as to change the column's datatype, is there better way to do this?

Yes, you don't need to drop and recreate the PK (and associated index(es)) for this at all. You can do it as a simple metadata change via ALTER TABLE ... ALTER COLUMN.

CREATE TABLE #T
(
P VARCHAR(2) PRIMARY KEY
)      

INSERT INTO #T VALUES ('AA')  

ALTER TABLE #T ALTER COLUMN P VARCHAR(3) NOT NULL

DROP TABLE #T

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

If SELECT * FROM sysobjects WHERE name = 'LabourGrade_pk' is returning a row, then you want to use:

ALTER TABLE SchemaName.LabourGrade DROP CONSTRAINT LabourGrade_pk

not

ALTER TABLE SchemaName.LabourGrade DROP CONSTRAINT Labour_Grade_pk
                                                       --^-- We don't want this

But, this doesn't address why you need to drop this constraint, as per other comments and @Martin's answer.

Upvotes: 4

Related Questions