If you’re getting an error that reads something like “Msg 3728, Level 16, State 1, Line 1
‘DF__Dogs__DogId__6FE99F9F’ is not a constraint“, it’s probably because you’re trying to drop a constraint that isn’t in the database.

To fix this issue, check to make sure the constraint exists before dropping it. Alternatively, use the IF EXISTS clause to drop the constraint only if it exists.

Example of Error

Here’s an example of code that produces the error:

ALTER TABLE Dogs 
DROP CONSTRAINT DF__Dogs__DogId__6FE99F9F;

Result:

Msg 3728, Level 16, State 1, Line 1
'DF__Dogs__DogId__6FE99F9F' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

I got two errors here; error Msg 3728 and error Msg 3727.

Error 3728 tells me that DF__Dogs__DogId__6FE99F9F is not a constraint, and error 3727 tells me that it couldn’t drop the constraint due to same reason that cause the previous error (which is that the constraint didn’t exist).

Solution 1

If you’re sure that there’s a constraint that needs to be dropped, check that you’ve got the right database and constraint name. Perhaps you’ve typed the wrong constraint name, and you just need to find the constraint to see what its name is.

In my case, I’m trying to drop a DEFAULT constraint. Therefore, I could query the sys.default_constraints view to return the names of all DEFAULT constraints:

USE PetHotel;
SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
    name 
FROM sys.default_constraints;

Result:

SchemaName  TableName  ColumnName  name                     
----------  ---------  ----------  -------------------------
dbo         Dogs       DogId       DF__Dogs__DogId__02FC7413

If too many constraints are listed, we can always filter the query to just the table we’re interested in:

USE PetHotel;
SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
    name 
FROM sys.default_constraints
WHERE OBJECT_NAME(parent_object_id) = 'Dogs';

Result:

SchemaName  TableName  ColumnName  name                     
----------  ---------  ----------  -------------------------
dbo         Dogs       DogId       DF__Dogs__DogId__02FC7413

Another way to do it is to check the table’s definition with something like sp_help:

EXEC sp_help 'dbo.Dogs';

That returns a lot of information about the table, including any constraints.

In my case, I could see from that there’s a constraint called DF__Dogs__DogId__02FC7413, which has a slightly different name to the one I was trying to drop. I’m satisfied that this is the one that needs to be dropped, and so I can change my ALTER TABLE statement to drop this constraint:

ALTER TABLE Dogs 
DROP CONSTRAINT DF__Dogs__DogId__02FC7413;

Result:

Commands completed successfully.

We can see that the constraint has now been dropped.

Solution 2

Another approach is to use the IF EXISTS clause when attempting to drop the constraint. This ensures that no error is returned if the constraint doesn’t exist. Obviously, we wouldn’t want to use this option if we know there’s a constraint that needs to be dropped and we just don’t know its name. In that case we’d use the solution above. But if that’s not the case, we can use IF EXISTS to prevent any error that might occur in the event that the constraint doesn’t exist.

To demonstrate this, here’s what happens when I don’t use IF EXISTS:

ALTER TABLE Dogs 
DROP CONSTRAINT DF__Dogs__DogId__02FC7413;

Result:

Msg 3728, Level 16, State 1, Line 1
'DF__Dogs__DogId__02FC7413' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

The constraint doesn’t exist because I dropped it in the previous example.

Now let’s change the statement to use IF EXISTS:

ALTER TABLE Dogs 
DROP CONSTRAINT IF EXISTS DF__Dogs__DogId__02FC7413;

Result:

Commands completed successfully.

The command completed successfully and without error, even though the constraint didn’t exist.