Murray Brown
asked on
SQL Drop Primary Key
Hi
I am trying to drop a primary key in my SQL table [Machines]. The following isn't working
ALTER TABLE Machines DROP CONSTRAINT pk_Machine_Number
This is strange because with SELECT column_name FROM INFORMATION_SCHEMA.KEY_COL UMN_USAGE
I can see "Machine Number" so I am not sure what I am doing wrong
I am trying to drop a primary key in my SQL table [Machines]. The following isn't working
ALTER TABLE Machines DROP CONSTRAINT pk_Machine_Number
This is strange because with SELECT column_name FROM INFORMATION_SCHEMA.KEY_COL
I can see "Machine Number" so I am not sure what I am doing wrong
Use this query to verify if that constraint really exists:
SELECT name
FROM sys.key_constraints
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'Machines';
What does "not work" means? What error message do you get?
btw, you cannot drop a PRIMARY KEY or UNIQUE constraint, when it's part of a FOREIGN KEY relation or when the table has a XML or spatial index.
btw, you cannot drop a PRIMARY KEY or UNIQUE constraint, when it's part of a FOREIGN KEY relation or when the table has a XML or spatial index.
ASKER
Hi Vitor. When I run your SQL I get PK__Machines__0687CEB62221 77F3
Would that last bit have been added on automatically
Would that last bit have been added on automatically
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so this worked ALTER TABLE Machines DROP CONSTRAINT PK__Machines__0687CEB62221 77F3. I used VB.net code to create the primary key so I made a mistake then
ASKER
Great. Thanks once again! Excellent answer
ASKER
try this, try to right click the table, and then select Script Table as > CREATE To > New Query Editor Window, see what's the CONSTRAINT name there?