Managing and Using Rules and Defaults
- How to use rules and defaults - Rules are used to enforce value restrictions on columns. Defaults encourage column values or provide a value when a column is inserted into a view that does not include that column.
- What associates rules and defaults with columns - 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.
- How to display information about rules and defaults - Rules and defaults can be bound to multiple columns. It might be necessary to periodically review the use of rules and defaults.
Rules constrain 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 default supplies a value for an inserted row when a user doesn't supply one.
You can also restrict the values that are entered in columns of a table
using constraints. Constraints are defined on a table column when the column
is defined in a CREATE TABLE statement. The type of constraint
that is comparable to a rule is a CHECK constraint.
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 constrains on a column while you can only define a single rule for a column.
However, a rule is stored as a separate database object and can be applied to both columns of a table as well as user-defined datatypes. When a table is dropped, its CHECK constraints are no longer available. A rule, as a separate object, is still available though the column of a table that it's defined on is dropped.
You should consider the advantages of using both constraints and rules.
You might even use both constraints and rules. For example, a table column
can have both a rule (only one, remember) 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 the Chapter 5 section
entitled Creating
and Using Constraints>Creating and Using Constraints
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.
For example, you can use a rule that is defined on a column to limit the values added into a column that records the departments of the company to only the allowable departments. If there are only four departments of 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.
You should recall that SQL Server provides an automatic validation
for datatypes. You'll receive an error 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 columns across tables.
Creating Rules
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
Create a rule in the current database, and 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 prefixed by the symbol @ (at). Use @ to specify a parameter that refers to the value later entered into a table column with either an UPDATE or INSERT statement.
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')
If you add a rule (and other database objects) to the database model,
the rule will be automatically available in any database that is created
subsequently. When you create a new database, it's created using the model
database as an 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 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:
- After your start the SQL Enterprise Manager, select the server and the database in which the rule is to be defined.
- Expand the Objects folder and select Rules.
- 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.
- Enter the description of the rule in the Description field and a name for the rule in the Rule field.
- Click Add to create the new rule.
- Click Close to close the Manage Rules dialog box.
Figure 11.1 shows the Manage Rules dialog box for the creation of the rule, department_values, through the SQL Enterprise Manager.
Fig. 11.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. Once 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 can use sp_bindrule to bind a rule to a column or user-defined datatype, which 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, so you may be able to 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's 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 that is defined using the user-defined datatype.
The third parameter, futureonly, is used only for the definition 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:
- After your start the SQL Enterprise Manager, select the server and the database in which the rule is defined.
- Expand the Objects folder and select Rules.
- 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.
- Click the Column Bindings or Datatype Bindings tab.
- 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, select the user-defined datatype in the User-Defined Datatype field and the rule in the Binding column.
- Click Bind.
- Click Close to close the Manage Rules dialog box.
Figure 11.2 shows the Manage Rules dialog box for the binding of the department_values rule to the department column in the employee table.
Fig. 11.2 - You can bind a rule to the columns of multiple tables.
You can double-click the left mouse button on a selected rule to bring
up the Manage Rules dialog box.
Figure 11.3 shows the Manage Rules dialog box for the binding of rules to a user-defined datatype.
Fig. 11.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:
- Rules that you've bound to columns take precedence over rules bound to datatypes. If rules are bound to both a table column and the user-defined datatype with which the column is defined, the rule that is bound to the table columns is used. If you bind a new rule to a table column, it also overrides a rule bound to the user-defined datatype to which the column is defined.
- If you bind a new rule to a column or datatype, the new rule replaces the old one. You can have only a single rule bound to a column or user-defined datatype.
- If you bind a rule to a user-defined datatype, it doesn't replace a rule bound to a column of that datatype (which isn't surprising). Table 11.1 summarizes rule precedence.
| New Rule | Old Rule Bound to User datatype | Old Rule Bound to Column |
|---|---|---|
| User 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 you allow data to be entered into a table.
In the following example, 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 the attempted INSERT violates the rule bound on the table column.
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.
The following example defines a new user-defined datatype and rule that is later bound to the 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.
You can restrict the range of allowable values by using the appropriate
system datatype, for example, smallint instead of integer.
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: 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 11.4, the Manage Rule Info dialog box shows a rule bound to a table column.
Fig. 11.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. However, it doesn't 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're probably more interested in displaying the rule itself rather than 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. Figure 11.1 shows the Manage Rules dialog box with the description of the rule within the description field.
I created a rule and bound it to a table column. When I tried to
bind the rule to a column in a table in another database, I couldn't reference
the rule.
Rules are defined within a set of system tables that 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 once you use a USE command to position
yourself to the database in which the rule will be used.
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 Rules Info dialog box (see fig. 11.5).
Fig. 11.5 - The name of the rule is removed from the Bound Columns field of the Manage Rules Info dialog box.
In the following example, 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.
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 the following example, 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.
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 the following example (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.
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 the following example, an existing rule is renamed. Once 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.
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 using the SQL Enterprise Manager with the Rename Object dialog box by clicking the right mouse button on a selected rule and selecting Rename. In the Rename Object dialog box, enter a new name in the New Name field. Click OK. Figure 11.6 shows the Rename Object dialog box.
Fig. 11.6 - The new rule name immediately replaces the old name in the Server Manager dialog 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 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 the following example, an initial attempt to remove a rule is unsuccessful because the rule is bound to a table column. Once the rule is unbound from the table column, it's successfully removed. Sp_helptext demonstrates that the object is gone.
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. Click Drop to remove the rule (refer to fig. 11.1). Click Close to close the Manage Rules dialog box.
I defined a rule and bound it to a user-defined datatype. I was
surprised that a rule that I had previously bound to the same datatype
isn't in effect any longer.
You can only have a single rule bound to either a user-defined
datatype or a table column. 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 that 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 also can't violate a rule that is associated with a table column.
Default definitions are stored in the syscomments table like rule definitions. 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
If you define a default with a value that's longer that the table column
to which it's subsequently bound, the default value entered into the column
is truncated.
You can also create a default using the SQL Enterprise Manager by performing the following steps:
- After your start the SQL Enterprise Manager, select the server and the database in which the default is to be created.
- Expand the Objects folder and select Defaults.
- 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.
- Enter a value for the default in the Description field and a name for the default in the Defaults field.
- Click Add to create the new default.
- Click Close to close the Manage Defaults dialog box.
Figure 11.7 shows the Manage Defaults dialog box for the creation of the default department through the SQL Enterprise Manager.
Fig. 11.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 the following example, 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.
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.
When you define a table column that permits NULL values, a
NULL is added to a row when the column isn't referenced when 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; however, its meaning is still undefined,
which is different from the automatic insertion of an actual value.
See the Chapter 5 section
entitled Understanding
NULL and NOT NULL
You can also bind a default to a table column or user-defined datatype using the SQL Enterprise Manager by performing the following steps:
- After your start the SQL Enterprise Manager, select the server and the database in which the default is defined.
- Expand the Objects folder and select Defaults.
- 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 Rules dialog box.
- Click the Column Bindings or Datatype Bindings tab.
- 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 Bindings field. For a datatype binding, select the user-defined datatype in the User-Defined Datatype field and the default in the Binding column.
- Click Bind.
- Click Close to close the Manage Defaults dialog box.
Figure 11.8 shows the Manage Defaults dialog box for the binding of the department_default default to the department column in the employee table.
Fig. 11.8 - You can bind a default to the columns of multiple tables.
Displaying Bindings
You can use sp_help to display the defaults bound to either table columns or user-defined datatypes. In the following example, sp_help displays the default that's bound to the Badge 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 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 dialog box. Click Bindings to display default bindings in the Manage Default Info dialog box (see fig. 11.9).
Fig. 11.9 - You can bind a default to both a user-defined datatype and a table column.
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 that's defined for the default. The definition 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. Figure 11.7 shows the Manage Defaults dialog box with the default value.
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 the following example, 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.
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 11.10 shows the Manage Default Info box after the department_default default has been unbound from the user-defined datatype department.
Fig. 11.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 the following example, 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.
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 clicking the right mouse button on a selected default and selecting Rename. In the Rename Object dialog box, enter a new name in the New Name field (refer to fig. 11.6). Click OK.
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 to be able to 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. Once the column is unbound from a table column, the default is 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. Click Drop to remove the default (refer to fig. 11.7) Click Close to close the Manage Defaults dialog box.
From Here...
Rules are very powerful tools used to enforce limitations on column and user-defined datatype values. Once a rule is created it must then be bound to columns and/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 will 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 chapter:
Chapter 5, "Creating Database Tables and Using Datatypes" teaches you how to define data columns and user-defined datatypes. You'll also learn the allowable range of values for each system datatype.
QUE Home Page
For technical support For our books And software contact support@mcp.com
Copyright © 1996, Que Corporation
