CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Serge P
Serge P

Reputation: 1611

Error 3728: is not a constraint

I'm trying to alter (not best practice?) a SQL server database's table that was installed by a piece of software.

Goal:
Set a constraint on a column to be a primary key then remove it.

Notes:
APPT - Table name
ApptId - Column that functions as a primary key (but was not set as the primary key by the software)

Question:
How to resolve ___ is not a constraint. error message?


The command I'm trying to run (test) is as follows and should enable and disable the ApptId row as the primary key:

-- Set an existing field as the primary key
ALTER TABLE APPT
ADD PRIMARY KEY (ApptID)

-- Remove primary key constraint
ALTER TABLE APPT
DROP CONSTRAINT ApptID

The above command sets the ApptID as the primary key, however when it tries to drop it an error message is produced:

-- Error 3728: 'ApptID' is not a constraint.
-- Could not drop constraint. See previous errors.

Why is this the case?
when I try to view the constraints for that table using:

-- List all constraints for a specific table
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' AND parent_object_id = OBJECT_ID('APPT')

I get:

NameofConstraint           | TableName | ConstraintType
PK__APPT__EDACF695230515B9 | APPT      | PRIMARY_KEY_CONSTRAINT

When I set the ApptId column as the primary key via Sql Management Studio the result looks cleaner (i.e. without random string)

NameofConstraint           | TableName | ConstraintType
PK_APPT                    | APPT      | PRIMARY_KEY_CONSTRAINT

But this does not resolve my issue when trying to programmatically drop the primary key constraint on the ApptId field.

Upvotes: 2

Views: 18519

Answers (3)

Naresh Muthyala
Naresh Muthyala

Reputation: 163

You'll get this error if constraint is already dropped and you are running the drop script again

Upvotes: 0

radar
radar

Reputation: 13425

When you add primary key with out a name, SQL server generates a name for the constraint

So you are seeing the name as PK__APPT__EDACF695230515B9

In this case you need to use the generated name to drop the constraint

ALTER TABLE APPT DROP CONSTRAINT PK__APPT__EDACF695230515B9

When you added the Primary key from SSMS, it gave the name as PK_APPT

you can do the same 

ALTER TABLE APPT
ADD CONSTRAINT PK_APPT PRIMARY KEY (ApptID)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Have you tried using the name of the constraint?

ALTER TABLE APPT DROP CONSTRAINT PK_APPT;

Upvotes: 1

Related Questions