Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 349
WEFX Avatar asked Oct 05 '11 15:10

WEFX


People also ask

How do I drop a default constraint?

Syntax: ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP DEFAULT; Example: Lets say we want to drop the constraint from STUDENTS table, which we have created in the above sections.

What is default constraint in SQL?

The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.


Video Answer


3 Answers

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
like image 70
Martin Smith Avatar answered Oct 09 '22 12:10

Martin Smith


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
like image 31
Kenneth Avatar answered Oct 09 '22 11:10

Kenneth


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
like image 1
Mateusz Kraska Avatar answered Oct 09 '22 10:10

Mateusz Kraska