I'm adding a few BIT columns to an existing table using the following code:
ALTER TABLE MyAwesomeTable ADD MyAwesomeField BIT NOT NULL DEFAULT 0;
The reason for doing this was an assumption that using "DEFAULT 0" when creating the column would be more efficient than something like adding INT columns and setting them all to 0 in an update statement.
The problem I'm having is that I've learned a new SQL curse word - "CONSTRAINT".
Anyway - I've looked around the web for a bit and see all kinds of crazy stored procedures and what I'm hoping are unnecessarily complicated methods to drop a default constraint.
What I'd like to do is at the top of my script (which will be run manually) is drop and then re-add some BIT columns to an existing table:
-- Drop the columns
ALTER TABLE MyTable DROP COLUMN MyBITColumn1
ALTER TABLE MyTable DROP COLUMN MyBITColumn2
ALTER TABLE MyTable DROP COLUMN MyBITColumn3
-- Readd the columns
ALTER TABLE MyTable ADD MyBITColumn1 BIT NOT NULL DEFAULT 0;
ALTER TABLE MyTable ADD MyBITColumn2 BIT NOT NULL DEFAULT 0;
ALTER TABLE MyTable ADD MyBITColumn3 BIT NOT NULL DEFAULT 0;
Then lather, rinse, repeat as needed.
Is there a simple way to do this?
Seems like I should be able to do something like:
ALTER TABLE MyTable DROP CONSTRAINT DEFAULT MyBITColumn1
Or some heavy handed hack like:
ALTER TABLE MyTable DROP MyBITColumn1 /force
Any help will be greatly appreciated.