SQL Server 2005
Trying to add a default value of A to a field with no existing constraint, that allows nulls (the current default).
(this first command does not work because there is no constraint by this name)
ALTER TABLE dbo.TMGSAR
DROP CONSTRAINT DF_TMGSAR_B_DATA_QUALITY;
dbo.TMGSAR Add constraint DF_TMGSAR_B_DATA_QUALITY DEFAULT 'A' for B_Data_Quality
This fails becuse the default of Null is considered a default - although it is not listed as a constraint:
Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
If I manually add the 'A' as the default using Enterprise manager - the DF_TMGSAR_B_DATA_QUALITY constraint was added.
After that point.. there is no problem using this code.
The problem is that I am restoring a DB that that needs this Default of 'A' added.