A message on default constraints

One of my assignments as a consultant DBA for a local Fortune 500 company is to support development of a large project. I was in a meeting on naming conventions and best practices from a DBA standpoint with the development leads this past week when the practice of naming default contraints came up.

The benefit of providing user-defined names on your default constraints is that SQL won't create a standardized name with a random string at the end. That random string can be a real pain when it comes to database object comparison, plus, it looks ugly and isn't nearly as informative as a user-defined name could be.

Here's the email sent to the developers, who were unsure of the syntax and the deprecated behavior of default objects. Names have been changed to protect the innocent tables and columns.

You are correct, creating a CONSTRAINT object is deprecated behavior and will not be supported. Creating the constraints in the CREATE TABLE script is the preferred method. Constraints can still be named, however.

CREATE TABLE [dbo].[TABLECD](

[CD] [char](1) NOT NULL DEFAULT ' ',

[id] [varchar](15) NOT NULL DEFAULT ' ',

[ADDUSER] [int] NOT NULL DEFAULT ((0)),

[ADDDATE] [datetime] NOT NULL DEFAULT GETDATE(),

[EDITUSER] [int] NOT NULL DEFAULT ((0)),

[EDITDATE] [datetime] NOT NULL DEFAULT CONVERT(datetime, '1899/12/31'),

[DELETEUSER] [int] NOT NULL DEFAULT ((0)),

[DELETEDATE] [datetime] NOT NULL DEFAULT CONVERT(datetime, '1899/12/31'),

[DELETEFLAG] [char](1) NOT NULL DEFAULT ' ',

When the above script is run, below is what actually gets created. Notice the names generated.

DEFAULT on column ADDDATE DF__TABLECD__ADDDA__59FA5E80 (getdate())

DEFAULT on column ADDUSER DF__TABLECD__ADDUS__59063A47 ((0))

DEFAULT on column CD DF__TABLECD__CD__571DF1D5 (' ')

DEFAULT on column DELETEUSER DF__TABLECD__DELET__5CD6CB2B ((0))

DEFAULT on column DELETEDATE DF__TABLECD__DELET__5DCAEF64 (CONVERT([datetime],'1899/12/31',0))

DEFAULT on column DELETEFLAG DF__TABLECD__DELET__5EBF139D (' ')

DEFAULT on column EDITDATE DF__TABLECD__EDITD__5BE2A6F2 (CONVERT([datetime],'1899/12/31',0))

DEFAULT on column EDITUSER DF__TABLECD__EDITU__5AEE82B9 ((0))

DEFAULT on column id DF__TABLECD__id__5812160E (' ')

Here’s a version of the script that sets user-provides constraint names:

CREATE TABLE [dbo].[TABLECD](

[CD] [char](1) NOT NULL CONSTRAINT [DF_TABLECD_CD] DEFAULT ' ',

[id] [varchar](15) NOT NULL CONSTRAINT [DF_TABLECD_id] DEFAULT ' ',

[ADDUSER] [int] NOT NULL CONSTRAINT [DF_TABLECD_ADDUSER] DEFAULT ((0)),

[ADDDATE] [datetime] NOT NULL CONSTRAINT [DF_TABLECD_ADDDATE] DEFAULT GETDATE(),

[EDITUSER] [int] NOT NULL CONSTRAINT [DF_TABLECD_EDITUSER] DEFAULT ((0)),

[EDITDATE] [datetime] NOT NULL CONSTRAINT [DF_TABLECD_EDITDATE] DEFAULT CONVERT(datetime, '1899/12/31'),

[DELETEUSER] [int] NOT NULL CONSTRAINT [DF_TABLECD_DELETEUSER] DEFAULT ((0)),

[DELETEDATE] [datetime] NOT NULL CONSTRAINT [DF_TABLECD_DELETEDATE] DEFAULT CONVERT(datetime, '1899/12/31'),

[DELETEFLAG] [char](1) NOT NULL CONSTRAINT [DF_TABLECD_DELETEFLAG] DEFAULT ' '

)

And here’s the output. Notice the constraint names are not truncated or appended with random strings.

DEFAULT on column ADDDATE DF_TABLECD_ADDDATE (getdate())

DEFAULT on column ADDUSER DF_TABLECD_ADDUSER ((0))

DEFAULT on column CD DF_TABLECD_CD (' ')

DEFAULT on column DELETEDATE DF_TABLECD_DELETEDATE (CONVERT([datetime],'1899/12/31',0))

DEFAULT on column DELETEFLAG DF_TABLECD_DELETEFLAG (' ')

DEFAULT on column DELETEUSER DF_TABLECD_DELETEUSER ((0))

DEFAULT on column EDITDATE DF_TABLECD_EDITDATE (CONVERT([datetime],'1899/12/31',0))

DEFAULT on column EDITUSER DF_TABLECD_EDITUSER ((0))

DEFAULT on column id DF_TABLECD_id (' ')

“There are no constraints on the human mind, no walls around the human spirit, no barriers to our progress except those we ourselves erect.” -Ronald Reagan