Defaults

Defaults allow you to specify a value to be entered into a column, if one is not otherwise specified. Defaults can be anything that evaluates to a constant, such as a constant, a built-in function, or a mathematical expression. Defaults are of two types: declarative and bound. The two are functionally the same; the difference is in how they are implemented.

Declarative Defaults

A declarative default is just another flavor of constraint, and as such is implemented using the CREATE TABLE and ALTER TABLE commands. Following is an example of adding a DEFAULT constraint to the employee table that enters a value of UNLISTED if no phone number is specified:

 ALTER TABLE employee ADD    CONSTRAINT df_emp_ph DEFAULT 'UNLISTED' FOR Phone 

To remove a declarative default constraint, use ALTER TABLE :

 ALTER TABLE employee DROP CONSTRAINT df_emp_ph 

Bound Defaults

Bound defaults are implemented in the same way rules are. The default is first created as an object in the database, and then it is bound to a column or User -Defined Datatype (UDT). The following implements the preceding example as a bound default:

 --First create the default.  CREATE DEFAULT phone_df AS 'UNLISTED' --Bind the default to a column sp_bindefault phone_df, 'employee.phone' 

As you can see, the bound default appears to require an extra step, but after it is created, it has the advantage of being able to be bound to other columns . This way, you have a consistent value of UNLISTED when no phone number is entered, rather than a mixed bag of perhaps NONE , UNKNOWN , and NULL .

Creating Bound Defaults in Enterprise Manager

If you want to use Enterprise Manager to create a bound default, right-click Defaults in the appropriate database, and select New Default from the pop-up menu. This opens the Default Properties dialog box, as shown in Figure 14.6. Enter a name for the default and the default value (don't forget the single quotes around constants), and click OK. After a default is created, this same dialog box can be used to bind the default to UDTs and columns.

Figure 14.6. The Default Properties dialog box.

Default Usage

A default can act as a placeholder of sorts, perhaps with a value of NONE being entered as opposed to just allowing nulls. A default can also be used to provide the most common entry; if most of your employees live in Texas, providing 'Texas' as a default in the state column of the employee table would make sense.

Another usage of the default is to generate a value based on a system function. For instance, when inserting or updating records, it is often useful to record a timestamp, or the user ID of the person making the change. The following example adds the updtime and updby columns and provides default values for the updating user and the time using system functions SUSER_SNAME() and GETDATE() .

 CREATE TABLE employee  (   Emp_no int IDENTITY (100, 10)CONSTRAINT emp_pk_emp_no PRIMARY KEY NOT NULL,   Lname char(20) NOT NULL,   Fname char(20) NOT NULL,   Phone char(13) NULL,   Dept smallint CONSTRAINT emp_dept_fk REFERENCES dept(dept_no)NOT NULL,   updtime datetime DEFAULT GETDATE() NOT NULL,   updby varchar(30) DEFAULT SUSER_SNAME() NOT NULL, ) 

When a Default Is Applied

SQL Server can automatically generate a value for a column when a row is inserted in four ways: using a constraint or bound default, specifying the IDENTITY property for the column, using the timestamp datatype, or simply letting the column allow NULL values.

Because columns defined with the timestamp datatype or associated with the IDENTITY property automatically insert a value, they cannot allow NULL , nor can you specify the DEFAULT keyword on inserts for an IDENTITY column. The column must be omitted from the INSERT list. Just because a default is defined for a column doesn't mean that NULL values will never be in that column. If a column allows NULL , and an explicit NULL is passed in the Insert statement, a NULL will be inserted. If you implemented a default because you don't want NULL in a column, define the column as NOT NULL .

Now look at the different ways you can generate defaults in an Insert statement based on the following table:

 CREATE TABLE test_default  (id int IDENTITY NOT NULL,  tmstmp timestamp NOT NULL,  phone char(13) NOT NULL DEFAULT 'UNLISTED',  notes VARCHAR(100) NULL) 

Because all the columns have some sort of default associated with them, the keywords DEFAULT VALUES can be used to generate values for the entire row:

 INSERT test_default DEFAULT VALUES  SELECT * FROM test_default id          tmstmp                phone         notes ----------- --------------------- ------------- ----- 1           0x0000000000000191    UNLISTED      NULL 

If you want a default generated for a particular column, exclude the column from the column listing. Remember that the identity column is omitted as well. The following generates an identity for id, and a default for tmstmp:

 INSERT test_default (phone, notes)   VALUES('[phone]', 'Phone added') SELECT * FROM test_default id        tmstmp              phone          notes --------- ------------------- -------------- ------------ 1         0x0000000000000192  [phone]  Phone added 

Another method is to specify the keyword DEFAULT in the value listing for each column you want populated with the default value:

 INSERT test_default (tmstmp, phone, notes)   VALUES(DEFAULT, DEFAULT, DEFAULT) SELECT * FROM test_default id          tmstmp                phone         notes ----------- ------------------ ------------- ------- 1           0x0000000000000193 UNLISTED       NULL 

Binding Precedence with Rules and Defaults

A default value, rule, or check can be assigned to a column using a CHECK or DEFAULT constraint, by binding to the column a RULE or DEFAULT object, or by creating the column with a User-Defined Datatype that has a RULE or DEFAULT object bound to the UDT. To avoid conflicts between the different methods , some restrictions must apply.

The following restrictions apply for check constraints and rules:

• Only one rule can be bound to a column. Binding a new rule to a column unbinds the old rule automatically.

• Only one rule can be bound to a datatype. Binding a new rule to a datatype unbinds the old rule automatically.

• A rule bound to a column has precedence over a rule bound to a datatype. To reapply a rule bound to a datatype, unbind the rule from the column and then rebind the rule to the datatype.

• If a CHECK constraint and a rule are on the same column, the rule is validated first. If the verification for the rule fails, an error message is generated and the statement fails.

These restrictions apply for defaults:

• A column can have only one type of default apply at any one time.

• You cannot create a table with a default constraint if the column is based on a datatype to which a default object is bound.

• You cannot alter a table to add a default constraint if the column is based on a datatype to which a default object is bound or if a default object is bound to the column.

• If you have a column that uses a user-defined datatype and the column has a default constraint or default object bound to it, binding a default to the datatype will not affect that column.

• If a column is created with a datatype to which a default is bound, and subsequently, a default is bound to the column, the default bound to the column will take precedence.