How to drop a column in a SQL 2005 database table which has a default constraint on it
I have added a new column which has a default value on it by running the following ALTER TABLE ... ADD COLUMN ... DEFAULT .... syntax
ALTER TABLE RISStatus ADD StatusForHavingRecord BIT DEFAULT 1
After then I realized that I had missed something and tried to drop the recently added column as using the following command
ALTER TABLE RISStatus DROP COLUMN StatusForHavingRecord
I got the following error message, since there is a Default Constraint declared on the newly added column.
Msg 5074, Level 16, State 1, Line 1
The object 'DF__RISStatus__Statu__3ABC4CB2' is dependent on column 'StatusForHavingRecord'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN StatusForHavingRecord failed because one or more objects access this column.
When I query the sys.objects catalog view for the object in the error message, 'DF__RISStatus__Statu__3ABC4CB2' I see that the object has a type D which is DEFAULT constraint.
I had to drop the default constraint by using the following sql statement.
ALTER TABLE RISStatus DROP Constraint DF__RISStatus__Statu__3ABC4CB2
And then I could successfully drop the column I had added to the table previously
ALTER TABLE RISStatus DROP COLUMN StatusForHavingRecord
All I wanted to do was using the WITH VALUES key in order to update the newly added column with default value for the existing rows.
ALTER TABLE RISStatus ADD StatusForHavingRecord BIT DEFAULT 1 WITH VALUES