sqlsql-serversql-server-2008

Why does SQL Server keep creating a DF constraint?


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.


Solution

  • 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