Removing an auto named Default Constraint DF_TableName_ColumnName MS SQL

ometimes when we create new Columns in tables in MS SQL like for example:

alter table Stock add IsAvailable bit not null default(0)

The last part with default forces MS SQL to create a DF constraint something like this ‘DF_Stock_IsAvailable_03589’

Problem is, at some point when you need to remove this column, to be able to remove it you first need to remove its DF Constraint else it won’t allow you to drop the column, because its being used by that automated constraint.

To remove this constraint what needs to be done is:

SQL

DECLARE @DFConstraintName NVARCHAR(250)
-what this does is it get the constraint for that Table, and Column
SELECT @DFConstraintName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
-in my case the table name is Stock and the Column is IsAvailable
WHERE [object_id] = OBJECT_ID(‘Stock’) AND [name] = ‘IsAvailable’;
-here we drop the constraint by name
EXEC(‘ALTER TABLE Stock DROP CONSTRAINT ‘ + @DFConstraintName)


Leave a Reply

Your email address will not be published. Required fields are marked *