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!
LVL 1
ttist25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
You will have to drop the constraint before you drop the column. You have a couple of options to make your life a little easier.

1. Use one of the stored procedures you've found. They're really not that bad. This is one of my favorites.
CREATE PROCEDURE [dbo].[sp_DropColumn]
(
    @TableName NVARCHAR(200),
    @ColumnName NVARCHAR(200)
)
AS
BEGIN

    DECLARE @ConstraintName NVARCHAR(200);
    SELECT @ConstraintName = [name]
    FROM [sys].[default_constraints]
    WHERE [parent_object_id] = OBJECT_ID(@TableName)
          AND [parent_column_id] =
          (
              SELECT [column_id]
              FROM [sys].[columns]
              WHERE [name] = @ColumnName
                    AND [object_id] = OBJECT_ID(@TableName)
          );
    IF @ConstraintName IS NOT NULL
        EXEC ('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName);
    EXEC ('ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName);

END

Open in new window

2. Name your constraint when you create the column so it's easy to identify later when you need to drop it. You'd do it like this:
ALTER TABLE MyTable ADD MyBITColumn1 BIT CONSTRAINT [DF_MyBITColumn1] DEFAULT 0 NOT NULL;

Open in new window

Notice I used a simple convention where I named the constraint "DF_<columnname>" so I don't really have to think about it when it comes time to delete the column.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ttist25Author Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.