Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 13 -
Managing and Using Rules, Constraints, and Defaults

Rules are used to enforce value restrictions on columns. Defaults suggest column values or provide a value when a column is inserted into a view that does not include that column.
Rules and defaults can be defined and stored in the database but are still not enforced on any columns. They must be bound to a column to enforce the rule or default.
Rules and defaults can be bound to multiple columns. It might be necessary to periodically review the use of rules and defaults.

You'll recall from previous chapters that maintaining referential integrity in your database tables is one of the biggest benefits of using SQL Server. It has the ability to manage the information flow into and out of the system by enforcing your criteria on the database side, rather than expecting the application to control this type of information. This leads to data independence from applications, making an open database system possible.

To make this possible, SQL Server implements several tools that help you manage the information in your system. In this chapter, you'll find out about three of these tools: rules, constraints, and defaults. In addition, you can use triggers to manage information as it flows through your system, and you should be sure to read up on and understand triggers as you design your system.


See Chapter 15, "Creating and Managing Triggers," for more information about Triggers.

Table 13.1 gives you an overview of what these terms mean and how you use them in your systems.

Table 13.1 Understanding Rules, Constraints, and Defaults

Type of Control Description
Rule Rules control the values that can be stored within table columns and within user-defined datatypes. Rules use expressions that are applied to columns or user-defined datatypes to restrict the allowable values that can be entered. A rule is stored as a separate database object. With this independence comes the ability to apply a rule not only to columns of a table but also to user-defined datatypes. Also, because a rule is stored separately from the table on which it's imposed, if a table on which a rule is applied is dropped, the rule remains available to be applied against other tables. A column can have only one rule.
Constraints Constraints are defined on a table column when the column is defined in a CREATE TABLE statement. Microsoft considers constraints preferable to rules as a mechanism for restricting the allowable values that can be entered into a table column because you can define multiple constraints on a column, but you can only define a single rule for a column. The type of constraint that is comparable to a rule is a CHECK constraint. When a table is dropped, its CHECK constraints are no longer available. A table can have more than one constraint applied to it.
Default Defaults supply a value for an inserted row when users do not supply one with their information to be inserted.


NOTE: You should consider the advantages of using constraints and rules. For example, a table column can have a rule, but only one, and several CHECK constraints defined on it. A table column is restricted by the combination of a rule and one or more CHECK constraints that apply to the column.


See "Creating and Using Constraints," for more information on constraints, Chapter 6.

Defining Rules

A rule provides a defined restriction on the values for a table column or a user-defined datatype. Any data that you attempt to enter into either a column or a user-defined datatype must meet the criteria defined for the user-defined datatype or column. You should use rules to implement business-related restrictions or limits.

Rules enable you to specify which tests should be performed in one of several ways. First, you can use a function to perform a test on the information. Functions are used to return a comparison value that you can use to validate the value in the column.

In addition, you can use comparison operators like BETWEEN, LIKE, and IN to complete the test on the value in the new data. Once again, your point is to test the new value and make sure it falls within the bounds you've set for it.

For example, you can use a rule to limit the values in a department column to only valid, allowable departments. If there are only four departments in which an employee can be a member, you can define a rule to limit the values entered into the department column to only the four department names. Use a rule to specify the range of allowable values for a column of user-defined datatype.


NOTE: SQL Server provides an automatic validation for datatypes. You'll receive an error message if you enter a value that is outside the range of allowable values for the datatype and if you enter a value that is incompatible with the datatype. For example, you can't enter alphabetic or special characters, such as an asterisk (*) and question mark (?), in an int integer datatype.

You should keep this in mind when you define a column or user-defined datatype. If you choose a correct datatype for a column or user-defined datatype, it may make the definition of the rule simpler or even unnecessary.


Remember, you can use a user-defined datatype to define a new datatype based on one of the system datatypes, such as CHAR and int, or specialized datatypes. You'll find that user-defined datatypes for table columns must be identically defined across tables. In addition, you can't define a rule for a system datatype, only for a user-defined datatype.

For example, instead of redefining a column, such as badge number, that is defined in multiple tables to be used for relational joins, you can define a user-defined datatype and use it as the datatype of badge in each table that it's defined. If the range of values can be identical for the badge number columns, you can define a rule for a user-defined datatype called badge and use it as the datatype for all badge number columns across tables.

Creating Rules

Remember, rules are a separate object in your database. Because of this, you have to first create the rule and then bind it to a column. You create a rule with a CREATE RULE statement. The syntax of the CREATE RULE statement is as follows:

CREATE RULE rule_name
AS condition_expression

If you create a rule in the current database, it applies to only columns or user-defined datatypes within the database in which it is defined. You can use any expression in a rule that is valid in a WHERE clause, and your rule can include comparison or arithmetic operators.

The conditional expression you use in a rule must be prefaced with the @ symbol. Use @ to specify a parameter that refers to the value later entered into a table column with either an UPDATE or INSERT statement.


NOTE: When you create a rule, be sure to pay close attention to the datatypes you use within it for the comparison. SQL Server does no datatype checking against the values that are using the rule, so you must ensure that the datatype is compatible with the values you'll be checking. Errors that result from datatype mismatches will not be evident until the rule runs for the first time, at which time you'll receive an error message.

In the following example, CREATE RULE is used to create a list of values using an IN keyword to form a condition expression. Although the parameter used in the condition expression is descriptively identical to the column name in the table to which it's later bound, the parameter can be defined using any set of alphanumeric characters.

create rule department_values
as @department in (`Sales','Field Service','Logistics','Software')


NOTE: If you add a rule and other database objects to the database Model, the rule is automatically available in any database that is created subsequently. When you create a new database, it's created using the Model database as a template. Any objects that are in the Model database are automatically duplicated in new database.

If you create a set of rules that can and should be used throughout all your databases, create the rules first in the Model database by using the database administrator's account (sa) for access before you create your databases.


The rule must restrict values to those that are compatible with the column datatype. You can't use constants, within a condition expression, that aren't compatible with the column or user-defined datatype to which the rule is subsequently applied. You can define the name of the rule so that it includes the name of the column or user-defined datatype to which it will be bound to make it descriptive.

You can also create a rule through the SQL Enterprise Manager by performing the following steps:

1. After you start the SQL Enterprise Manager, select the server and the database in which the rule is to be defined.

2. Expand the Objects folder and select Rules.

3. Click the right mouse button and select New Rule to bring up the Manage Rules dialog box. Alternatively, you can choose Rules from the Manage menu to bring up the Manage Rules dialog box.

4. Enter the description of the rule in the Description list box and a name for the rule in the Rule drop-down list box.

5. Click Add to create the new rule.

6. Click Close to close the Manage Rules dialog box.

Figure 13.1 shows the Manage Rules dialog box for the creation of the rule, department_values, through the SQL Enterprise Manager.

FIG. 13.1
The Rules page of this dialog box creates the rule but does not bind it to a column or user-defined datatype.

Binding Rules

The definition of a rule doesn't include the specification that applies the rule to either a table column or user-defined datatype. If you only define a rule, it's never in effect; it's only created as an object in the database.

After you define a rule, you must bind it to a column or user-defined datatypes. A rule bound to a column or user-defined datatype specifies that the rule is in effect for the column or user-defined datatype. All values that you enter into a column or user-defined datatype must satisfy the criteria defined by the rule.

You use sp_bindrule to bind a rule to a column or user-defined datatype. sp_bindrule uses the following syntax:

sp_bindrule rulename, table_name.column_name, [futureonly]

After you bind a rule to a column or user-defined datatype, information about the rule is entered into system tables. A unique rule ID number is stored in the syscolumns and systypes system tables. A rule has a row in syscolumns if it is bound to a column and in systypes if it is bound to a user-defined datatype.

The first parameter of sp_bindrule specifies the name of the rule. You can use as many as 30 characters for the name of the rule. If you wish, you can include the name of the table column or user-defined datatype within the name of the rule.

Enter the name of either the table column or the user-defined datatype to which the rule will be applied. You must enter the name of the table column preceded by the name of the table in which it's defined, enclosed in single quotation marks. If you enter only the name of an object, it is interpreted by SQL Server as the name of a user-defined datatype. When you enter a column name use a period to separate the table name from the column name to which the rule is to be bound. A rule that is bound to a datatype restricts the values that can be added to the table column defined with the user-defined datatype.

The third parameter, futureonly, is used only for the management of user-defined datatypes. futureonly prevents the rule from being applied to table columns that are already defined using the user-defined datatype. Use futureonly to specify that the rule only applies to columns that are subsequently created using the user-defined datatype to which the rule is bound.

You can also bind a rule to a table column or user-defined datatype using the SQL Enterprise Manager by performing the following steps:

1. After you start the SQL Enterprise Manager, select the server and the database in which the rule is defined.

2. Expand the Objects folder and select Rules.

3. Select the rule to be bound. Click the right mouse button and select Edit. Alternatively, you can choose Rules from the Manage menu to bring up the Manage Rules dialog box.

4. Click the Column Bindings or Datatype Bindings tab.

5. For a column binding, select the name of the table in the Table field, the column in the Column field, and the rule in the Bindings field. For a datatype binding, switch to the Datatype Bindings page. Select the user-defined datatype in the User-Defined Datatype field and the rule in the Binding column.

6. Click Bind.

7. Click Close to close the Manage Rules dialog box.

Figure 13.2 shows the Manage Rules dialog box for the binding of the department_values rule to the department column in the Employee table.

FIG. 13.2
You can bind a rule to the columns of multiple tables.


TIP: You can double-click a selected rule to bring up the Manage Rules dialog box.

Figure 13.3 shows the Manage Rules dialog box for the binding of rules to a user-defined datatype.

FIG. 13.3
You can bind a rule to a user-defined datatype when you create the datatype through the SQL Enterprise Manager.

You may have already realized that conflicts can occur with rules and some precedence conventions that are used to resolve the conflicts. You might encounter a situation in which you have a table column that is defined using a user-defined datatype and both the datatype and column have rules that are bound to them. The following list includes three precedence rules that apply to rule binding:

Table 13.2 Rule Precedence

New Rule Bound to... Old Rule Bound to User-Defined Datatype Old Rule Bound to Column
User-defined datatype Replaces old rule No change
Column Replaces old rule Replaces old rule

Rules don't apply to the data that has already been entered in the table. Values that are currently in tables don't have to meet the criteria specified by rules. If you want a rule to constrain the values entered in the table, define a rule directly or indirectly, through a user-defined datatype, before data is entered into a table.


CAUTION: When you copy information into your database using the BCP utility, rules are not enforced. This is because the BCP utility is meant to complete changes in a bulk fashion as quickly as possible. If you are concerned about the integrity of incoming information, consider importing the information into a working table and then writing an application that will insert each row into your production table, leaving the rules in force for the insertions.

In Listing 13.1, the procedure, sp_bindrule, is used to bind the rule, department_values, to a the column department in the table, Employees. A subsequent INSERT statement fails its attempt to enter a value in the table column that doesn't meet the criteria defined by the rule. SQL Server returns a descriptive error message that specifies that the attempted INSERT violates the rule bound on the table column.

Listing 13.1 Output Showing Details for a Rule

sp_bindrule department_values, `employees.department'
go
Rule bound to table column.
insert into employees
values (`Dan Duryea','Shipping',3321)
go
Msg 513, Level 16, State 1
A column insert or update conflicts with a rule imposed by a previous
CREATE RULE command. The command was aborted. The conflict occurred in
database `master', table `employees', column `department'
Command has been aborted.

Listing 13.2 defines a new user-defined datatype and rule that is later bound to the datatype:

Listing 13.2 Output Showing Details for a Rule and Datatype

sp_addtype badge_type2, int, `not null'
go
Type added.
create rule badgerule2
as @badge_type2 >000 and @badge_type2 <9999
go
This command did not return data, and it did not return any rows
sp_bindrule badgerule2, badge_type2
Rule bound to datatype.


TIP: You can restrict the range of allowable values by using the appropriate system datatype--for example, smallint instead of integer.


NOTE: Microsoft says that there are three types of rules that you can define: rules with a range, a list, or a pattern. The two previous examples use a range (...@badge_type2 >000 and @badge_type2 <9999) and a list (...@department in (`Sales','Field Service', 'Logistics')) to restrict values for the rule. The following example shows the third type of rule, which is a rule that uses a pattern to restrict values. The example restricts values to any number of characters that end with "S" through "U."

Create rule pattern_rule
@p like `%[S-U]'

You may find it easier to define and use rules if you understand the types of rules that you can create.


Displaying Rule Bindings

You can use the system procedure, sp_help, to display information about the user-defined datatypes or table columns that have rules bound to them. In the following example, information displayed about the user-defined datatype created in an earlier example includes the rule that is bound to the datatype:

sp_help badge_type2
Type_name  Storage_type  Length Nulls  Default_name Rule_name
--------------  --------------- ------  ----- ---------------
badge_type2  int          4      0     (null)     badgerule2

You can also display rule binding information by clicking Bindings in the Manage Rules dialog box. In Figure 13.4, the Manage Rule Info dialog box shows a rule bound to a table column.

FIG. 13.4
You can also unbind a rule from the Manage Rule Info dialog box.

Displaying Rules

sp_help displays information about a database object, such as a user-defined datatype or column, including the name of a rule that is bound to the datatype or column. sp_help doesn't show the rule itself when information about the object to which it's bound is shown.

You can use sp_help to display information about a rule. It doesn't, however, return much information about a rule. In the following example, sp_help returns information about the rule, badgerule2 and shows only its owner, the type of object, a defined segment on which it's located, and the date and time it was created:

sp_help badgerule2
Name               Owner                 Type
------------------------------ --------------
badgerule2         dbo                   rule
Data_located_on_segment      When_created
----------------------- ---------------------
not applicable         Oct 24 1994 10:40AM

You'll probably be more interested in displaying the rule itself instead of the characteristics of the rule as an object. To display the definition of a rule itself, use sp_helptext. The definition of a rule is saved as the row of a system table, so the definition of a rule is returned as the row of a table. The following example shows the rule that is used to constrain the range of allowable badge numbers defined in previous examples:

sp_helptext badgerule2
text
----------------------------
create rule badgerule2
as @badge_type2 >000 and @badge_type2 <9999
(1 row(s) affected)

You can also use the SQL Enterprise Manager to display rules. A rule definition is shown in the Description field of the Manage Rules dialog box. Double-click a selected rule or right-click and select Edit to bring up the description of a rule in the Manage Rules dialog box. Refer to Figure 13.1 to see the Manage Rules dialog box with the description of the rule within the Description field.

Finally, keep in mind that rules are defined within a set of system tables, which is local to each database. The rules defined within one database aren't available within another database. You can select the rule definition within an ISQL/w session, store it as a file, and then open the file to recover the rule. You can define the rule after you use a USE command to position yourself to the database in which the rule will be applied.

Unbinding Rules

At some point, you may no longer want the values that are entered into a column or user- defined datatype to be constrained by a rule. You can unbind a rule using sp_unbindrule, which removes the constraint from a bound column or user-defined datatype. Unbinding a rule makes it non-applicable to a column or user-defined datatype. The sp_unbindrule syntax is as follows:

sp_unbindrule table_name.column or user_datatype [, futureonly]

Like sp_bindrule, if the first parameter of sp_unbindrule is a column, it must be preceded by the name of the table in which it's defined and entered in single quotation marks. Otherwise, the first parameter is interpreted as the name of a user-defined datatype.

Use futureonly, the optional third parameter, only with rules that are bound to user-defined datatypes. Table columns that are already defined using the user-defined datatype have the rule applied to the columns unless the futureonly optional parameter is present. The futureonly option prevents existing columns from inheriting the rule. Only new columns that are defined using the user-defined datatype are affected by the rule.

You can also use the SQL Enterprise Manager to unbind a rule from a table column or user-defined datatype by clicking Unbind after selecting the rule in the Manage Rule Info dialog box (see Figure 13.5).

FIG. 13.5
The name of the rule is removed from the Bound Columns field of the Manage Rule Info dialog box.

In Listing 13.3, sp_help displays the Employees table, which has a rule that is defined on the department column. sp_unbindrule unbinds the rule from the department column of the Employees table. A subsequent display of the Employees table shows that the rule has been unbound from the table column.

Listing 13.3 Output Showing a Table and the Process of Removing a Rule from the Table

sp_help employees
go
Name          Owner                   Type
------------------------------ ------------
employees      dbo                   user table
Data_located_on_segment        When_created
------------------------------ -------------
default                May 12 1994 10:15AM
Column_name Type Length Nulls Default_name  Rule_name
--------------- --------------- ------ ----- -------
name     CHAR    20     0     (null)          (null)
department CHAR  20     0    (null) department_values
badge      int    4     0     (null)         (null)
Object does not have any indexes.
No defined keys for this object.
sp_unbindrule `employees.department'
go
Rule unbound from table column.
sp_help employees
go
Name             Owner                 Type
------------------------------ ------------
employees         dbo                 user table
Data_located_on_segment        When_created
------------------------------ ------------
default                    May 12 1994 10:15AM
Column_name  Type Length Nulls Default_name Rule_name
--------------- --------------- ------ ----- --------
name         CHAR   20     0     (null)     (null)
department   CHAR   20     0     (null)     (null)
badge        int    4      0     (null)     (null)
Object does not have any indexes.
No defined keys for this object.

You can also unbind a rule by replacing the current rule with a new one. sp_bindrule binds a new rule to that column or datatype. The old rule is automatically unbound from the user-defined datatype or table column.

In Listing 13.4, the attempted redefinition of the existing department_values rule is unsuccessful because a rule can't be replaced by one with the same name. A new rule is created, and it's bound to the same column to which the department_values rule is bound. The new rule replaces the old department_values rule.

Listing 13.4 You Can not Create a Rule with the Same Name as Another in the Database

create rule department_values
as @department in (`Sales','Field Service','Logistics','Shipping')
go
Msg 2714, Level 16, State 1
There is already an object named `department_values' in the database.
create rule depart2
as @department in (`Sales','Field Service','Logistics','Shipping')
go
This command did not return data, and it did not return any rows
sp_bindrule depart2, `employees.department'
go
Rule bound to table column.

In Listing 13.5, which is a continuation of the previous example, an INSERT into the Employees table demonstrates that the new rule has been bound to the department column. The old rule for department would have disallowed the addition of a row that contains the shipping department. A SELECT statement shows that the new row was added to the table. Finally, sp_help shows that the new depart2 rule is bound to the department column of the Employees table and replaces the old department_values rule.

Listing 13.5 Output Showing Table Definition

insert into employees
values (`Dan Duryea','Shipping',3321)
go
(1 row(s) affected)
select * from employees
go
name                 department           badge
-------------------- -------------------- -----------
Bob Smith            Sales                1234
Mary Jones           Sales                5514
Dan Duryea           Shipping             3321
(3 row(s) affected)
sp_help employees
go
Name                           Owner                          Type
------------------------------ ------------------------------ ------------
employees                      dbo                            user table
Data_located_on_segment        When_created
------------------------------ --------------------------
default                        May 12 1994 10:15AM
Column_name     Type            Length Nulls Default_name    Rule_name
-------------- --------------- ------ ----- --------------- ---------------
name            CHAR            20     0     (null)          (null)
department      CHAR            20     0     (null)          depart2
badge           int             4      0     (null)          (null)
Object does not have any indexes.
No defined keys for this object.

Renaming Rules

You can rename rules, like other objects, using sp_rename. You can also use sp_rename to rename other user objects, such as tables, views, columns, stored procedures, triggers, and defaults. The sp_rename syntax is as follows:

sp_rename object_name, new_name

In Listing 13.6, an existing rule is renamed. After the rule is renamed, a display of the Employees table shows that the new name of the rule is in effect for the department column.

Listing 13.6 Renaming Objects

sp_rename depart2, depart3
go
Object name has been changed.
sp_help employees
go
Name                           Owner                          Type
------------------------------ ------------------------------ ----------------
employees                      dbo                            user table
Data_located_on_segment        When_created
------------------------------ ---------------------------
default                        May 12 1994 10:15AM
Column_name     Type            Length Nulls Default_name    Rule_name
-------------- -------------- ------ ----- --------------- ---------------
name            CHAR            20     0     (null)          (null)
department      CHAR            20     0     (null)          depart3
badge           int             4      0     (null)          (null)
Object does not have any indexes.
No defined keys for this object.

You can also rename a rule by using the SQL Enterprise Manager with the Rename Object dialog box. Right-click a selected rule and select Rename. In the Rename Object dialog box, enter a new name in the New Name field. Click OK. Figure 13.6 shows the Rename Object dialog box.

FIG. 13.6
The new rule name immediately replaces the old name in the Server Manager dia- log box of the SQL Enterprise Manager.

Dropping Rules

You can use the DROP RULE statement to permanently remove a rule from a database. The rule is immediately removed if it's not bound to any columns or user-defined datatypes. If the rule is bound to a column or a datatype, you must first unbind the rule from all columns and user- defined datatypes to be able to drop the rule. You can drop multiple rules with a single DROP RULE statement. The DROP RULE syntax is as follows:

DROP RULE rule_name_1[,...rule_name_n]

In Listing 13.7, an initial attempt to remove a rule is unsuccessful because the rule is bound to a table column. After the rule is unbound from the table column, it's successfully removed. Sp_helptext demonstrates that the object is gone.

Listing 13.7 Removing a Rule

drop rule depart3
go
Msg 3716, Level 16, State 1
The rule `depart3' cannot be dropped because it is bound to one or more column.
sp_unbindrule `employees.department'
go
Rule unbound from table column.
drop rule depart3
go
This command did not return data, and it did not return any rows
sp_helptext depart3
go
No such object in the current database.

You can also drop rules through the SQL Enterprise Manager. Select the name of the rule in the Rule field of the Manage Rules dialog box (refer to Figure 13.1). Click Drop to remove the rule. Click Close to close the Manage Rules dialog box.


TIP: Keep in mind that you can only have a single rule bound to either a user-defined datatype or a table column. If you have a rule defined and then bind a new rule to the same column as the first, the first rule will be replaced.
In addition, if you bind a rule to a user-defined datatype without using futureonly, it effectively replaces the rule for all table columns defined from the user-defined datatype.

Defining Defaults

You can use defaults to define a value, which is automatically added to a column, if no value is explicitly entered. You bind a default to a column or user-defined datatype using sp_binddefault. You must define a default value that is compatible with the column datatype. A default can't violate a rule that is associated with a table column.

Default definitions, like rule definitions, are stored in the syscomments table. Also like rules, if you bind a new default to a column, it automatically overrides an old rule. A default bound to the column takes precedence over a default bound to the user-defined datatype.

Creating Defaults

You can define a default using the CREATE DEFAULT statement. The name used in the second parameter of the sp_bindefault is interpreted as a user-defined datatype unless it's preceded with the table name. It must be preceded by the name of a table to be interpreted as a column of a table. The CREATE DEFAULT syntax is as follows:

CREATE DEFAULT default_name AS constant value


CAUTION: If you define a default with a value that is longer than the table column to which it's subsequently bound, the default value entered into the column is truncated. Make sure the datatype and size of your column matches that of the default you're trying to establish.

You can also create a default using the SQL Enterprise Manager by performing the following steps:

1. After you start the SQL Enterprise Manager, select the server and the database in which the default is to be created.

2. Expand the Objects folder and select Defaults.

3. Click the right mouse button and select New Default to bring up the Manage Defaults dialog box. Alternatively, you can choose Defaults from the Manage menu to bring up the Manage Defaults dialog box.

4. Enter a value for the default in the Description field and a name for the default in the Default field.

5. Click Add to create the new default.

6. Click Close to close the Manage Defaults dialog box.

Figure 13.7 shows the Manage Defaults dialog box for the creation of the default department_rule through the SQL Enterprise Manager.

FIG. 13.7
You can also manage existing defaults using the Manage Defaults dialog box.

Binding Defaults

You can use the system procedure, sp_bindefault, to bind a default to a user-defined datatype or table column. The second parameter can be the name of a table column or a user-defined datatype. Use the third parameter to specify that the default value should only be applied to new columns of tables that are defined, not to existing columns of tables. The sp_bindefault syntax is as follows:

sp_bindefault default_name, table_name.column_name, [futureonly]

In Listing 13.8, a default is defined and bound to the department column of the Employees table. A row is inserted into the table that omits a value for the department column in the list of values. A subsequent SELECT statement demonstrates that the default value was added to the department column for the newly inserted row.

Listing 13.8 Defining a Default

create default Department_default as `Sales'
go
sp_bindefault Department_default, `employees.department'
go
Default bound to column.
insert into employees
(name, badge)
values (`John Garr',2221)
go
(1 row(s) affected)
select * from employees
where badge=2221
go
name                 department           badge
-------------------- -------------------- -----------
John Garr            Sales                2221
(1 row(s) affected)

In the following example, a default is defined and bound to a user-defined datatype. The second parameter of sp_bindefault is interpreted as a user-defined datatype because no table name precedes the object name. The third parameter isn't specified, so the default value is applied to any table columns that are defined using the user-defined datatype.

create default badge_default
as 9999
sp_bindefault badge_default, badge_type2
Default bound to datatype.


NOTE: When you define a table column that permits NULL values, a NULL is added to a row when the column isn't referenced at the time a row is inserted into the table. A NULL entry is automatically inserted, just as a default value is automatically inserted. The definition of a NULL remains the same. It's meaning, however, is still undefined, which is different from the automatic insertion of an actual value.


See "Understanding NULL and NOT NULL" Chapter 6.

You can use the SQL Enterprise Manager to bind a default to a table column or user-defined datatype by performing the following steps:

1. After you start the SQL Enterprise Manager, select the server and the database in which the default is defined.

2. Expand the Objects folder and select Defaults.

3. Select the default to be bound. Click the right mouse button and select Edit.
Alternatively, you can choose Defaults from the Manage menu to bring up the Manage Defaults dialog box.

4. Click the Column Bindings or Datatype Bindings tab.

5. For a column binding, select the name of the table in the Table field, the column in the Column field, and the default in the Binding field. For a datatype binding, switch to the Datatype Bindings page. Select the user-defined datatype in the User-Defined Datatype field and the default in the Binding column.

6. Click Bind.

7. Click Close to close the Manage Defaults dialog box.

Figure 13.8 shows the Manage Defaults dialog box for the binding of the department_default default to the department column in the Employee table.

FIG. 13.8
You can bind a default to the columns of multiple tables.

You can only bind a single default value to a given column. If you try to bind a default to a column on which one already exists, you receive an error message from SQL Server. You need to use the sp_unbindefault statement to remove the existing default and then apply the new default to the column.

Unlike rules, Defaults are applied during bulk copy operations. If you have both a default and a rule on a given column, the default is applied first and then the rule is checked. This helps keep your rule from having to be aware of NULL values because the default will have already updated the value appropriately.

In the next section you find out how to determine what defaults are already in place for a given column.

Displaying Bindings

You can use sp_help to display the defaults bound to either table columns or user-defined datatypes. In Listing 13.9, sp_help displays the default bound to the badge column:

Listing 13.9 Showing Defaults Bound to a Table

sp_help employees
go
Name                           Owner                          Type
------------------------------ ------------------------------ ----------
employees                      dbo                            user table
Data_located_on_segment        When_created
------------------------------ ---------------------------
default                        Oct 18 1994 12:52PM
Column_name     Type            Length Nulls Default_name    Rule_name
-------------- --------------- ------ ----- --------------- --------
name            CHAR            20     0     (null)          (null)
department      CHAR            20     0     (null)          (null)
badge           int             4      0     badge_default   (null)
Object does not have any indexes.
No defined keys for this object.

You can also display default bindings using the SQL Enterprise Manager. One way in which you can display default bindings through the SQL Enterprise Manager is from the Manage Default Info dialog box. Click Bindings to display default bindings in the Manage Default Info dialog box (see Figure 13.9).

FIG. 13.9
You can bind a default to both a user-defined datatype and a table column.


TIP: A default bound to a table column is also displayed in the Default file of the Manage Table dialog box in the SQL Enterprise Manager.

Displaying Defaults

You can use the procedure sp_helptext to display the value defined for a default. The definitions of defaults are stored as rows in the syscomments system table. The display of a default definition is shown as the row of a table. In the following example, the default for a table column is shown using sp_helptext:

sp_helptext Department_default
go
text-----------------------------------------
create default Department_default as `Sales'
(1 row(s) affected)

You can also use the SQL Enterprise Manager to display a default. A default definition is shown in the Description field of the Manage Defaults dialog box. Double-click a selected rule or right-click and select Edit to bring up the description of a default in the Manage Defaults dialog box. Refer to Figure 13.7 to see the Manage Defaults dialog box with the default values.

Unbinding Defaults

When you no longer want the default value automatically entered into a column or user-defined datatype, you must unbind the default by using sp_unbindefault, which removes the default from a bound column or user-defined datatype. Unbinding a default makes it non-applicable to a column or user-defined datatype. The sp_unbindefault syntax is as follows:

sp_unbindefault table_name.column_name [,futureonly]

Use the third parameter, which is optional, to specify that only new columns defined using the user-defined datatype aren't bound using the default. You only use the third parameter for user-defined datatypes. You don't use it for table columns. In Listing 13.10, a default is unbound from a table column. sp_help is first used to verify that the default is bound to the table column. Thereafter, sp_help is used after the default is unbound to verify that the default was unbound from the table column.

Listing 13.10 Verifying the Removal of a Default

sp_help employees
go
Name                           Owner                          Type
------------------------------ ------------------------------ ----------------
employees                      dbo                            user table
Data_located_on_segment        When_created
------------------------------ ---------------------------
default                        Oct 18 1994 12:52PM
Column_name     Type            Length Nulls Default_name    Rule_name
-------------- --------------- ------ ----- --------------- --------
name            CHAR            20     0     (null)          (null)
department      CHAR            20     0     (null)          (null)
badge           int             4      0     badge_default   (null)
Object does not have any indexes.
No defined keys for this object.
sp_unbindefault `employees.badge'
go
Default unbound from table column.
sp_help employees
go
Name                           Owner                          Type
------------------------------ ------------------------------ ----------
employees                      dbo                            user table
Data_located_on_segment        When_created
------------------------------ ---------------------------
default                        Oct 18 1994 12:52PM
Column_name     Type            Length Nulls Default_name    Rule_name
-------------- --------------- ------ ----- --------------- ----------
name            CHAR            20     0     (null)          (null)
department      CHAR            20     0     (null)          (null)
badge           int             4      0     (null)          (null)
Object does not have any indexes.
No defined keys for this object.

You can also use the SQL Enterprise Manager to unbind a default from a table column or user-defined datatype by clicking Unbind after selecting the default in the Manage Defaults Info dialog box. Figure 13.10 shows the Manage Default Info dialog box after the department_default default has been unbound from the user-defined datatype department.

FIG. 13.10
The name of the default is immediately removed from the Bound Columns or Bound Datatypes field of the Manage Default Info dialog box.

Renaming Defaults

You can use system procedure, sp_rename, to rename a default. In Listing 13.11, a default is renamed using sp_rename. After the default is renamed, the table in which the default is bound to a column is displayed using sp_help to confirm that the default was renamed.

Listing 13.11 Renaming a Default

sp_rename Department_default, dept_default
go
Object name has been changed.
sp_help employees
go
Name                           Owner                          Type
------------------------------ ------------------------------ ----------
employees                      dbo                            user table
Data_located_on_segment        When_created
------------------------------ ---------------------------
default                        May 12 1994 10:15AM
Column_name     Type            Length Nulls Default_name    Rule_name
-------------- --------------- ------ ----- --------------- --------
name            CHAR            20     0     (null)          (null)
department      CHAR            20     0     dept_default    (null)
badge           int             4      0     (null)          (null)
Object does not have any indexes.
No defined keys for this object.

You can also rename a default using the SQL Enterprise Manager with the Rename Object dialog box by right-clicking a selected default and selecting Rename. In the Rename Object dialog box (refer to Figure 13.6), enter a new name in the New Name field. Click OK.


NOTE: All database objects can be renamed using the sp_rename system procedure of the Rename Object dialog box in the SQL Enterprise Manager.

Dropping Defaults

You can permanently remove a default with the DROP DEFAULT statement. The default is immediately removed if it's not bound to any columns or user-defined datatypes. If the default is bound to a column or a datatype, you must first unbind the default from all columns and user datatypes before you can drop the default. You can drop multiple defaults with a single DROP DEFAULT statement. The DROP DEFAULT syntax is as follows:

DROP DEFAULT default_name_1 [,...default_name_n]

In the following example, an attempt to drop a default is unsuccessful because the default is bound to a table column. After the column is unbound from a table column, the default can be successfully dropped.

drop default dept_default
go
Msg 3716, Level 16, State 1
The default `dept_default' cannot be dropped because it is bound to one or  more columns.
sp_unbindefault `employees.department'
go
Default unbound from table column.
drop default dept_default
go
This command did not return data, and it did not return any rows
sp_helptext dept_default
No such object in the current database.

You can also drop defaults through the SQL Enterprise Manager. Select the name of the default in the Default field of the Manage Defaults dialog box (refer to Figure 13.7). Click Drop to remove the default. Click Close to close the Manage Defaults dialog box.

Reality Check

ODBC is both a blessing and a curse. With ODBC, you can query and work with databases from nearly any Microsoft Office application, Web sites, and more. This is now especially true with Office 97's capability to connect directly to database sources from within each of the applications. This is great for the user but can be disastrous for the developers because they no longer control the client-side application used to manipulate the database.

This is where rules, defaults, and constraints are useful. By implementing this low-level check on the database side of the equation where the information is ultimately stored, you remove the dependence on the client application. You also make your database strong enough that it can guarantee the relationships between tables.

In large-scale applications, rules and these types of server-side constraints play another, just as important, role. You should consider having a person or team of people that is responsible for the database engine and the implementation of the rules you're defining here. By centralizing the responsibility for these control mechanisms, you'll save a lot of heartache, especially compared to having this function distributed among departments.

In large development teams, it's difficult to make sure all of the developers know all of the different rules that pertain to the database on which they're working. Because you can implement these rules separately from the development work they're doing, you'll still be assured of a well-mannered application in the database sense, even if many different developers had their hand at writing the code to access the database. By placing the management of the database with a central person or team, you can be sure that no duplication will occur, and you'll be able to optimize the database to reflect the requirements of the team as a whole.

From Here...

Rules are very powerful tools used to enforce limitations on column and user-defined datatype values. After a rule is created, it must then be bound to columns or datatypes. Rules and datatypes can be bound to multiple table columns or user-defined datatypes. Defaults provide a way to provide an initial value to columns. Initial values can be used as suggestions or as a way to allow users, with a limited view of a table, to insert rows that contain data in columns to which they do not have access.

For information about the type of restrictions that are provided by constraints, see the following chapters:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.