troubleshooting Question

Is there a simple way to drop a column with a constraint?

Avatar of ttist25
ttist25 asked on
Microsoft SQL ServerSQL
2 Comments1 Solution90 ViewsLast Modified:
Hello,

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.  


Thanks!
ASKER CERTIFIED SOLUTION
Russ SuterSenior Software Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros