Default Constraint Names
By Bill Graziano
on 24 January 2001
| 2 Comments
| Tags: Table Design
A DBA I know named Lance sent me a view and a write-up on it and asked if I'd like to publish it. He posts in the forum under JohnDeere. Lance was fighting with server named default constraints and was using the view to help manage and rename the constraints.
When you create a table in SQL Server you can add various constraints to the table. One of the constraints you can add is a default contstraint. Your CREATE TABLE might look like this:
CREATE TABLE Test1 (Val1 int DEFAULT -1,
Val2 int DEFAULT -2,
Val3 int DEFAULT -3 )
SQL Server will assign each default constraint default a default name. In my case it named the first default constraint
DF__Test1__Val1__32E0915F. You can see the default values for each column in the Enterprise Manager in the Design Table dialog box. You can't see the constraint names however.
This doesn't cause you any problems until you try to change one of the defaults. In order to alter a constraint, you need to know the name of the constraint. The code to change the default value from -1 to -9 looks like this:
ALTER TABLE Test1
DROP CONSTRAINT DF__Test1__Val1__32E0915F
ALTER TABLE Test1
ADD CONSTRAINT DF__Test1__Val1__32E0915F DEFAULT (-9) FOR Val1
This becomes important in rolling out new databases or keeping multiple copies synchronized. The problems that Lance faced were compounded by the fact that he had four databases on two remote servers. Lance created a view to help him identify the default constraints and rename them. Here's his view:
create view v_DEFAULT_CONSTRAINT
select db_name() as CONSTRAINT_CATALOG
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name as CONSTRAINT_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,com.text as DEFAULT_CLAUSE
from sysobjects c_obj
join syscomments com on c_obj.id = com.id
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id
and con.colid = col.colid
c_obj.uid = user_id()
and c_obj.xtype = 'D'
After that, all Lance needed to do was drop the constraints and recreate them using a naming scheme he defined. All that took was a little dynamic SQL and SQL Server do the work.
Lance's advice was to always name your constraints. Especially if you are using scripts to rollout or modify databases. This works much better if the initial CREATE TABLE looked like this:
CREATE TABLE Test1 (
Val1 int CONSTRAINT Test1_Val1_DF DEFAULT -1,
Val2 int CONSTRAINT Test1_Val2_DF DEFAULT -2,
Val3 int CONSTRAINT Test1_Val3_DF DEFAULT -3 )
It's a little more work up front but worth it if you're going to be working in a multiple server environment.