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.
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.
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.
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:
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.
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:
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:
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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:
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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:
© Copyright, Macmillan Computer Publishing. All rights reserved.