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:

  DROP CONSTRAINT DF__Test1__Val1__32E0915F

  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:

select	db_name()				as CONSTRAINT_CATALOG
	, 				as TABLE_NAME
	,user_name(c_obj.uid)			as CONSTRAINT_SCHEMA
	,				as COLUMN_NAME
	,col.colid				as ORDINAL_POSITION
	,com.text				as DEFAULT_CLAUSE

from	sysobjects	c_obj
join 	syscomments	com on =
join 	sysobjects	t_obj on c_obj.parent_obj =  
join    sysconstraints con on	= con.constid
join 	syscolumns	col on =
			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:

  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.

- Advertisement -