Default Constraint Names

By Bill Graziano on 24 January 2001 | 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
as
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
where
	c_obj.uid	= user_id()
	and c_obj.xtype	= 'D'

GO

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.