Drop a SQL column default constraint that has been implicitly named

CodeMinder: to remind myself of stuff that I usually have to look up each time I do it.

Notice the unnamed default in the following line:

alter table test add col1 int not null default 1

This creates a default constraint in the database that is named something like “DF__test__col1__182C9B23,” which is pretty ugly.  The last several characters are some sort of random-ish sequence, and will be different on everybody’s database.  The difficulty comes later if some needs to modify this table:

alter table test drop column col1

You’ll get the error:

The object ‘DF__test__col1__182C9B23’ is dependent on column ‘col1’.

ALTER TABLE DROP COLUMN col1 failed because one or more objects access this column.

So to drop the column you have to first drop the constraint:

alter table test drop constraint DF__test__col1__182C9B23

Checking this script into source control is probably a big mistake.  That script will only work on my database – everyone else will get this error, because their constraint is named something else.  They’ll get the following error:

‘DF__test__col1__182C9B23’ is not a constraint.

Could not drop constraint. See previous errors.

So rewind to the beginning.  Instead of this:

alter table test add col1 int not null default 1

Do this:

alter table test add col1 int not null constraint DF_test_col1 default 1

Now everyone’s default constraint is named the same, and it’s trivial to write a script that will drop the constraint on anyone’s database.  I think it’s a fairly standard naming convention for default constraints to be DF_[table name]_[column name].

By the way, here is the definition of a stored procedure that can be used to drop unnamed default constraints.

<span style="color: #0000FF;">create</span><span style="color: #000000;"> </span><span style="color: #0000FF;">proc</span><span style="color: #000000;"> DropColumnDefault (
      </span><span style="color: #008000;">@table_name</span><span style="color: #000000;"> </span><span style="color: #0000FF;">nvarchar</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">256</span><span style="color: #000000;">),
      </span><span style="color: #008000;">@col_name</span><span style="color: #000000;"> </span><span style="color: #0000FF;">nvarchar</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">256</span><span style="color: #000000;">)
) </span><span style="color: #0000FF;">as</span><span style="color: #000000;">
</span><span style="color: #0000FF;">declare</span><span style="color: #000000;"> </span><span style="color: #008000;">@Command</span><span style="color: #000000;">  </span><span style="color: #0000FF;">nvarchar</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">)
</span><span style="color: #0000FF;">select</span><span style="color: #000000;"> </span><span style="color: #008000;">@Command</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">ALTER TABLE </span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #808080;">+</span><span style="color: #000000;"> </span><span style="color: #008000;">@table_name</span><span style="color: #000000;"> </span><span style="color: #808080;">+</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;"> drop constraint </span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #808080;">+</span><span style="color: #000000;"> d.name
</span><span style="color: #0000FF;">from</span><span style="color: #000000;"> sys.tables t   
</span><span style="color: #808080;">join</span><span style="color: #000000;"> sys.default_constraints d </span><span style="color: #0000FF;">on</span><span style="color: #000000;"> d.parent_object_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> t.</span><span style="color: #FF00FF;">object_id</span><span style="color: #000000;">
</span><span style="color: #808080;">join</span><span style="color: #000000;"> sys.columns c </span><span style="color: #0000FF;">on</span><span style="color: #000000;"> c.</span><span style="color: #FF00FF;">object_id</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> t.</span><span style="color: #FF00FF;">object_id</span><span style="color: #000000;"> </span><span style="color: #808080;">and</span><span style="color: #000000;"> c.column_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> d.parent_column_id
</span><span style="color: #0000FF;">where</span><span style="color: #000000;"> t.name </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #008000;">@table_name</span><span style="color: #000000;"> </span><span style="color: #808080;">and</span><span style="color: #000000;"> c.name </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #008000;">@col_name</span><span style="color: #000000;">
</span><span style="color: #0000FF;">if</span><span style="color: #000000;"> </span><span style="color: #008000;">@Command</span><span style="color: #000000;"> </span><span style="color: #0000FF;">is</span><span style="color: #000000;"> </span><span style="color: #808080;">not</span><span style="color: #000000;"> </span><span style="color: #0000FF;">null</span><span style="color: #000000;">
</span><span style="color: #0000FF;">execute</span><span style="color: #000000;"> (</span><span style="color: #008000;">@Command</span><span style="color: #000000;">)
</span>

This entry was posted in CodeMinder, Programming, SQL. Bookmark the permalink.