Why does SQL Server keep creating a DF constraint?

Question

I'm trying to create upgrade and backout scripts in SQL. The upgrade script adds a column like so:

IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'ColumnName' 
    AND object_id = OBJECT_ID(N'[dbo].[TableName]'))

ALTER TABLE TableName
    ADD ColumnName bit NOT NULL DEFAULT(0)

The backout script removes the column like so:

IF EXISTS (SELECT * FROM sys.columns WHERE Name = N'ColumnName' 
    AND object_id = OBJECT_ID(N'[dbo].[TableName]'))

ALTER TABLE TableName
    DROP COLUMN ColumnName

However, the backout script throws this error:

Msg 5074, Level 16, State 1, Line 5
    The object 'DF__TableName__ColumnName__1BF3D5BD' is dependent on column 'ColumnName'.
Msg 4922, Level 16, State 9, Line 5
    ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.

I know how to drop the constraint, but the constraint's name changes everytime (the suffix changes). I either need SQL Server to stop creating this randomly-named constraint OR I need to be able to remove the constraint in my script using wild-card characters, since the name changes.

Martin Smith · Accepted Answer · 2011-10-05 15:11:28Z

115

This is the default constraint that is added because of the DEFAULT(0) in your newly added column.

You can name this yourself so it has a known fixed name rather than relying on the auto name generation.

ALTER TABLE TableName
    ADD ColumnName bit NOT NULL CONSTRAINT DF_Some_Fixed_Name DEFAULT(0)

Then to remove the column and constraint together

ALTER TABLE dbo.TableName
DROP CONSTRAINT DF_Some_Fixed_Name, COLUMN ColumnName

edited Oct 5, 2011 at 15:11

answered Oct 5, 2011 at 15:06

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WEFX Over a year ago

Thanks Martin. Also, to properly check if the constraint exists (before dropping it), I used the method described here

A-K Over a year ago

@ÁlvaroG.Vicario - yes. Martin's answer gives you an example.

Mateusz Kraska · Accepted Answer · 2019-08-13 10:25:52Z

6

Run this:

declare @name as nvarchar(255);
SELECT @name = name FROM dbo.sysobjects 
WHERE name LIKE 'DF__XXX__YYY__%' and type = 'D'

IF @name IS NOT NULL BEGIN
    EXEC('ALTER TABLE XXX DROP CONSTRAINT ' + @name);
END

edited Aug 13, 2019 at 10:25

Mateusz Kraska

1051 silver badge6 bronze badges

answered Nov 9, 2017 at 22:30

Kenneth

2614 silver badges6 bronze badges

2 Comments

Steve Smith Over a year ago

Do we need to change any of the XXX/YYY to something specific?

Kenneth Over a year ago

XXX is the tablename and YYY is the column name. The names will probably be shortened to 6 characters though.

Mateusz Kraska · Accepted Answer · 2019-08-13 10:00:44Z

2

Run this if you want remove constraint:

DECLARE @tableName NVARCHAR(255) = '[INSERT]';
DECLARE @first5CharsFromColumnName NVARCHAR(255) = '[INSERT]';
DECLARE @name NVARCHAR(255);
SELECT @name = d.name FROM dbo.sysobjects d
INNER JOIN dbo.sysobjects t ON t.id = d.parent_obj
WHERE d.name LIKE '%'+@first5CharsFromColumnName+'%' AND d.type = 'D' AND t.name = @tableName

IF @name IS NOT NULL BEGIN
    EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @name);
END

answered Aug 13, 2019 at 10:00

Mateusz Kraska

1051 silver badge6 bronze badges

1 Comment

James Craig Burley Apr 29 at 19:43

This was very helpful! I prefer it to my approach of capturing the parent_obj id number, as it uses Join instead.

James Craig Burley · Accepted Answer · 2025-04-29 19:50:06Z

Other answers are great; in my case, the table name was also too long, so I ended up with something like this:

DECLARE @tableName NVARCHAR(255) = '[INSERT TABLE NAME WITHOUT dbo. PREFIX]';
DECLARE @columnName NVARCHAR(255) = '[INSERT COLUMN NAME]';

DECLARE @first9CharsFromTableName NVARCHAR(9) = @tableName;
DECLARE @first5CharsFromColumnName NVARCHAR(5) = @columnName;

DECLARE @name NVARCHAR(255);

SELECT @name = d.name FROM dbo.sysobjects d
  INNER JOIN dbo.sysobjects t ON t.id = d.parent_obj
  WHERE d.name LIKE 'DF__'+@first9CharsFromTableName+'__'+@first5CharsFromColumnName+'__%' AND d.type = 'D' AND t.name = @tableName;

IF @name IS NOT NULL BEGIN
  EXEC('ALTER TABLE [dbo].' + @tableName + ' DROP CONSTRAINT ' + @name);
END

EXEC('ALTER TABLE [dbo].' + @tableName + ' DROP COLUMN ' + @columnName);

Collectives™ on Stack Overflow

Why does SQL Server keep creating a DF constraint?

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related