Why does SQL Server keep creating a DF constraint?
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
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
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