Chapter 13: Defining Defaults and Rules for Data

Creating defaults

You can create or drop defaults at any time, before or after data has been entered in a table. In general, to create defaults you:

Define the default, using create default.
Bind the default to the appropriate table column or user-defined datatype using sp_bindefault.
Test the bound default by inserting data.

You can drop defaults using drop default and remove their association using sp_unbinddefault.

When you create and bind defaults:

Make sure the column is large enough for the default. For example, a char (2) column will not hold a 17-byte string like “Nobody knows yet.”
Be careful when you put a default on a user-defined datatype and a different default on an individual column of that type. If you bind the datatype default first and then the column default, the column default replaces the user-defined datatype default for the named column only. The user-defined datatype default is bound to all the other columns having that datatype.

However, once you bind another default to a column that has a default because of its type, that column ceases to be influenced by defaults bound to its datatype. This issue is discussed in more detail under “Binding defaults”.
Watch for conflicts between defaults and rules. Be sure the default value is allowed by the rule; otherwise, the default may be eliminated by the rule.

For example, if a rule allows entries between 1 and 100, and the default is set to 0, the rule rejects the default entry. Either change the default or change the rule.