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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nix
  • 321
  • 8
  • 20
  • What are you changing the datatype from and to? You don't always have to drop the PK. – Martin Smith Oct 01 '12 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 – Nix Oct 01 '12 at 10:17
  • 3
    One of those names has two underscores, the other has one. Typo in your question, or the actual issue? – Damien_The_Unbeliever Oct 01 '12 at 10:17
  • Have you tried: ALTER TABLE LabourGrade ALTER COLUMN id_column_name varchar(3); ? – Martin Wilson Oct 01 '12 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 – Martin Smith Oct 01 '12 at 10:19
  • Thanks Damian, that was it. Can't believe I didn't see it... – Nix Oct 01 '12 at 10:20

2 Answers2

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I tried doing that but am getting an error about the pk depending on the column – Nix Oct 01 '12 at 10:30
  • @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.` – Martin Smith Oct 01 '12 at 10:31
  • 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 Oct 01 '12 at 11:09
  • @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. – Martin Smith Oct 01 '12 at 11:11