Avatar of ttist25
ttist25 asked on

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

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;

Open in new window


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;

Open in new window


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!
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
ttist25

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Russ Suter

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ttist25

Thanks Russ!

I first read your answer and thought - ugh - I can't use an SP for this specific project and then I looked more closely and saw the blurb about naming the constraint.  

Perfect!

Thanks so much!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes