SQL Database Design » 9. Default Values

9. Default Values

Table of Contents

Default values are specified along with column declarations in a CREATE TABLE statement. Because of this, defaults are often falsely denoted as constraints. Default values do not constrain any column values. Even though defaults determine the values a column can take if some data is missing, columns can still only take the values as specified by their constraints. The domain is independent of any defaulting constructs.

No one knows who created the term "default constraint", but it is wrong. In standard SQL, a CONSTRAINT constraint_name can only be followed by [ NOT ] NULL, UNIQUE, PRIMARY KEY, CHECK, or FOREIGN KEY. Check this source to confirm it.

Some DBMSs, like PostgreSQL, allow using DEFAULT after CONSTRAINT constraint_name, consequently allowing non-standard syntax plus the ability to name defaults. The term "default constraint" has probably been an convenience decision to handle defaults along with constraints when discussing the CREATE TABLE statement. I only know of PostegreSQL and SQL Server people using this term, their documentations do, too. Just be aware that you might stumble across "default constraints" in certain documents.

Now that you know that defaults are not constraints, how do you specify them? Aside from simple defaults, there is no uniform way of generating more complex defaults across DBMS implementations. Each DBMS has its own methods, which has mostly historic reasons. The following default value constructs are available:

  • Simple Defaults
  • Derived Values
  • Auto-Increment Numbers
  • Sequences

Note, that unique numbers and sequences exclusively produce integral numbers. Simple defaults and derived values depend on the types of columns involved.

Last updated: 2010-08-04