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

SQL + Vark

In honor of Google's acquisition of Aardvark, thought I'd share a SQL-related Vark from a while back.

Here's how I spent part of my morning in an empty house.

http://vark.com/t/83c38c

Question

Dec 05, 2009

Sent to Aardvark via IM

Friend 1

22 / M / Omaha, NE

In database design, are relational schemas created with ER diagrams usually in 3NF? Is there a case where ERD schemas wouldn't be in 3NF?

Answer 1

+ 11 minutes

Aardvark found someone who knows about database

William A. (<--- that's me)

I am a database architect. For a relational system, yes, diagrams are created with at least third normal form. It is a part of the design process to consider business rules, usage and business entities... strictly designing a system to a normalization spec is not realistic. When designing a data warehouse however, converting higher-normalization relational data into a less normalized form may lead to better performance in a system that will be primarily for reads, not writes.

Friend 1

Is there a something about the ERD approach to designing relational schemas that makes it generally create schemas that satisfy third normal form, or is it just something you keep in mind during the design process?

William A.

So, I wasn't aware ERD was an "approach". Typically a CERD is followed by a PERD. That is, a Conceptual ERD, where relationships are specified as M-M, and objects are in business terms that a client would be able to easily recognize and comprehend. A Physical ERD is not easily consumable by a client or business user because it translates the CERD into database tables and foreign key relationships. The PERD contains join tables that break many to many relationships into a pair of 1-M relationships, for example. So building an ERD is to me separate from proper Normalization methodology.

Friend 1

Yeah, ERD schema design and 3NF normalization are usually not thought of simultaneously, which is why I'm having a hard time finding any resources that relate the two. Much harder is figuring out how well ERD creates schemas which satisfy 3NF, whether that was the intention of the design or not.

William A.

To me, ERD is not a design process or architecture methodology. To me, it is a document. Yes, ERD can display a schema that has been created in 3NF well, though typically only a PERD would be capable to displaying proper normalization. I'd suggest papers or books by CJ Date or EF Codd

Friend 1

I'll look into it, thanks for your help.

William A.

good luck

He'll need it.

Aardvark is a really neat social tool that is sort of like the oft-advertised KGB, except the answers come from other users who have signed up for knowledge in general categories. I signed up for Aardvark after hearing a story about it on NPR, and had a delightful conversation about Xbox360's and the weather with a fellow named Mert in Turkey. As you can see, the above conversation was brought to me because I signed up as knowledgeable in the area of database.

I've also Vark'd (is that a word?) about Conan O'Brien's TV ratings and martial arts schools for my kid. But since this is a SQL blog, I figured I'd share this one.

UPDATE: Sadly, Google killed Aardvark in September 2011.  Oh well.