SQL Server 2008 cannot refuse a restriction

Question

I am trying to remove a primary key constraint from a table using the following

ALTER TABLE SchemaName.LabourGrade DROP CONSTRAINT Labour_Grade_pk 

and get the error Labour_Grade_pk is not a constraint.

when i do

SELECT * FROM sysobjects WHERE name = 'LabourGrade_pk'

I get one row back. He really has FK, so I tried to give up this first, but the same problem. I only want to abandon PK to change the column data type, is there a better way to do this?

Answer 1

I only want to abandon PK to change the column data type, is there a better way to do this?

Yes, you do not need to pop up and recreate PK (and its associated indexes) at all for this. You can do this as a simple metadata change with 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 

Answer 2

If SELECT * FROM sysobjects WHERE name = 'LabourGrade_pk' returns a string, 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 does not concern why you need to abandon this restriction, like the other comments and @Martin's answer.