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

ttist25
ttist25 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Developer
Commented:
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.

Author

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial