If you’re getting an error that reads something like “Cannot drop a default constraint by DROP DEFAULT statement. Use ALTER TABLE to drop a constraint default“, it’s because you’re trying to use DROP DEFAULT to drop a DEFAULT constraint.

The DROP DEFAULT statement has been flagged for deletion from SQL Server, and Microsoft recommends that we use the ALTER TABLE statement to drop DEFAULT constraints.

Therefore, to fix this issue, use the ALTER TABLE statement to drop the DEFAULT constraint.

Example of Error

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

DROP DEFAULT IF EXISTS DF__Dogs__DogId__6FE99F9F;

Result:

Msg 3717, Level 16, State 1, Line 1
Cannot drop a default constraint by DROP DEFAULT statement. Use ALTER TABLE to drop a constraint default.

I tried to use DROP DEFAULT to drop a DEFAULT constraint called DF__Dogs__DogId__6FE99F9F, but I got an error instead.

Solution

To fix this issue, we need to use ALTER TABLE to drop the constraint:

ALTER TABLE Dogs 
DROP CONSTRAINT DF__Dogs__DogId__6FE99F9F;

Result:

Commands completed successfully.

The constraint has now been dropped as expected.

We can also use IF EXISTS so that we don’t get an error if the constraint doesn’t exist:

ALTER TABLE Dogs 
DROP CONSTRAINT IF EXISTS DF__Dogs__DogId__6FE99F9F;