Professional SQL Server™ 2005 Programming

5.5. DEFAULT Constraints

This will be the first of two different types of data integrity tools that will be called something to do with "default." This is, unfortunately, very confusing, but I'll do my best to make it clear (and I think it will become so).

You'll see the other type of default when you look at rules and defaults later in the chapter.

A DEFAULT constraint, like all constraints, becomes an integral part of the table definition. It defines what to do when a new row is inserted that doesn't include data for the column on which you have defined the default constraint. You can define it as a literal value (for example, by setting a default salary to zero or UNKNOWN for a string column) or as one of several system values such as GETDATE().

The following list points out the main things to understand about a DEFAULT constraint:

• Defaults are used only in INSERT statements; they are ignored for UPDATE and DELETE statements.

• If any value is supplied in the INSERT, the default isn't used.

• If no value is supplied, the default will always be used.

NOTE

Under the heading of "One more thing," it's worth noting that there is an exception to the rule about an UPDATE command not using a default. The exception happens if you explicitly say that you want a default to be used. You do this by using the keyword DEFAULT as the value you want the column updated to.

5.5.1. Defining a DEFAULT Constraint in Your CREATE TABLE Statement

At the risk of sounding repetitious, this works pretty much ...