4

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?

Improve this question
6
  • What are you changing the datatype from and to? You don't always have to drop the PK. Commented Oct 1, 2012 at 10:15
  • Its currently varchar(2) and it needs to be varchar(3). I got an error about the object LabourGrade_pk being dependent on column code when I tried to alter the column Commented Oct 1, 2012 at 10:17
  • 3
    One of those names has two underscores, the other has one. Typo in your question, or the actual issue? Commented Oct 1, 2012 at 10:17
  • Have you tried: ALTER TABLE LabourGrade ALTER COLUMN id_column_name varchar(3); ? Commented Oct 1, 2012 at 10:19
  • 1
    @Nix - You should be able to do that without dropping the PK. Make sure you specify NOT NULL in the ALTER COLUMN as otherwise it will fail as the default is to set it to NULL Commented Oct 1, 2012 at 10:19

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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
Improve this answer

4 Comments

I tried doing that but am getting an error about the pk depending on the column
@Nix - make sure you specify NOT NULL. Otherwise you get the error The object 'PK__#T________3BD019BD4CA06362' is dependent on column 'P' ... ALTER TABLE ALTER COLUMN P failed because one or more objects access this column.
There are several FK relationships that reference that table's PK so I'm trying to alter them first including the not null and still getting the object is dependent on column error.
@Nix - Drop the foreign key constraints then alter the column datatypes (in all affected tables) then add back the FK constraints then. Still no need to drop the PK itself.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.