Column constraints and rules are important aspects of database administration. You use constraints to control the way column values are used, such as whether a value must be unique or whether it must have a specific format. Although you usually apply constraints directly to a specific column, you also can use rules to create constraints that you can apply to multiple tables in a database.
SQL Server enforces the uniqueness of column values using unique and primary key constraints. You will often use unique constraints to create secondary keys (for nonclustered indexes) that you can use in conjunction with the primary key. Foreign key constraints identify the relationships between tables and ensure that referential integrity is maintained. Other types of constraints that you may want to use are check and not null constraints. Check constraints restrict the format or range of acceptable values for columns. Not null constraints prevent null values in a column.
Constraints can apply to columns or to entire tables. A column constraint is specified as part of a column definition and applies only to that column. A table constraint is declared independently from a column definition and can apply to several columns in the table. You must use table constraints when you want to include more than one column in a constraint. For example, if a table has three columns in the primary key, you must use a table constraint to include all three columns in the primary key.
When you set a unique constraint on a column or columns, SQL Server automatically creates a unique index, and then checks for duplicate values. If duplicate key values exist the index creation operation is cancelled and an error message displays. SQL Server also checks the data each time you add data to the table. If the new data contains duplicate keys, the insert or update operation is rolled back and an error message is generated. You can specify that duplicate keys should be ignored by using the IGNORE_DUP_KEY option.
In SQL Server Management Studio, you make a unique index by setting the option Is Unique to Yes when creating the index, as described in the section titled "Creating Indexes" earlier in this chapter, or by selecting the Unique check box on the General page of the Index Properties dialog box. In Transact-SQL, you can set the unique constraint when you create the index, as shown in the following example:
USE OrderSystemDB CREATE UNIQUE INDEX [Cust ID Index] ON Sales.Customers(cust_id)
A nonclustered index is created unless a clustered index is explicitly specified, such as:
USE Customer CREATE UNIQUE CLUSTERED INDEX [Cust ID Index] ON Sales.Customers(cust_id)
SQL Server also allows you to designate any column or group of columns as a primary key, but primary keys are often defined for identity columns. A table can have only one primary key, and because unique values are required, no primary key column can accept null values. Also, when you use multiple columns, the values of all the columns are combined to determine uniqueness.
As with unique constraints, SQL Server creates a unique index for the primary key columns. With primary key constraints, however, the index is created as a clustered index—unless a clustered index already exists on the table or a nonclustered index is explicitly specified.
In SQL Server Management Studio, you set the primary key when designing a new table or modifying an existing table by completing the following steps:
Clear the Allow Nulls option check box for any columns that will be used in the primary key.
Select the column or columns that you want to use as the primary key by pressing Ctrl and clicking the shaded box to the left of the column name.
Click Set Primary Key on the toolbar or select Set Primary Key on the Table Designer menu.
You can also set the primary key when you create or alter tables using Transact-SQL. Examples are shown in Example 9-13.
Example 9-13. Creating a Table and Its Columns with a Primary Key Constraint
USE CUSTOMER CREATE TABLE Sales.Customers (cust_id int NOT NULL, cust_lname varchar(40) NOT NULL, cust_fname varchar(20) NOT NULL, phone char(12) NOT NULL, CONSTRAINT PK_Cust PRIMARY KEY (cust_id)) USE CUSTOMER ALTER TABLE Sales.Customers ADD CONSTRAINT PK_Cust PRIMARY KEY (cust_id)
Foreign key constraints identify the relationships between tables and ensure that referential integrity is maintained. A foreign key in one table points to a candidate key in another table. Foreign keys prevent changes that would leave rows with foreign key values when there are no candidate keys with that value in the related table. You cannot insert a row with a foreign key value if there is no candidate key with that value. The exception is when you insert a null foreign key value.
In the following example, the Orders table establishes a foreign key referencing the Customer table defined earlier:
CREATE TABLE Sales.Orders
(order_nmbr int,
order_item varchar(20),
qty_ordered int,
cust_id int
FOREIGN KEY REFERENCES Sales.Customers(cust_id)
ON DELETE NO ACTION
)The ON DELETE clause defines that actions are taken if you try to delete a row to which existing foreign keys point. The ON DELETE clause has several options:
NO ACTION. Specifies that the deletion fails with an error and the delete action on the row is rolled back.
CASCADE. Specifies that all rows with foreign keys pointing to the deleted row are to be deleted as well. (CASCADE cannot be used if there is an INSTEAD OF trigger ON DELETE.)
SET NULL. Specifies that all values that make up the foreign key are set to NULL if the corresponding row in the parent table is deleted. (Foreign key columns must be nullable.)
SET DEFAULT. Specifies that all the values that make up the foreign key are set to their default values if the corresponding row in the parent table is deleted. (Foreign key columns must have default definitions. If a column is nullable and there is no explicit default, the column is set to NULL.)
You can also set an ON UPDATE clause in Transact-SQL, as shown in the following example:
CREATE TABLE Sales.Orders (order_nmbr int, order_item varchar(20), qty_ordered int, cust_id int FOREIGN KEY REFERENCES Sales.Customers(cust_id) ON UPDATE CASCADE )
The ON UPDATE clause defines the actions that are taken if you try to update a row to which existing foreign keys point. The clause also supports the NO ACTION, CASCADE, SET NULL, and SET DEFAULT options.
Check constraints allow you to control the format or range of values, or both, that are associated with tables and columns. For example, you could use this type of constraint to specify that postal codes must be entered in the format 99999 or that phone numbers must be entered as 9999999999.
In SQL Server Management Studio, you set check constraints when designing a new table or modifying an existing table by completing the following steps:
Select Check Constraints from the Table Designer menu. This displays the Check Constraints dialog box shown in Figure 9-14.
You can now:
Edit an existing constraint. First select it in the Selected Check Constraint selection list, and then modify the existing constraint expression and definition using the boxes provided.
Delete a constraint. First select it in the Selected Check Constraint selection list, and then click Delete. In the Delete Object dialog box, confirm the deletion by clicking OK.
Create a new constraint. Click Add, and then type a name and description of the constraint in the boxes provided. Click the button to the right of the Expression box, enter the check expression, and then click OK.
Click Close when you are finished working with check constraints.
Check constraint expressions specify the permitted characters using regular expressions:
Use [0-9] to indicate that any numeral from 0 to 9 is permitted in the designated position. As an example, to format a column to accommodate a nine-digit postal code, you would use the following expression:
PostalCode LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
Use [a-z] or [A-Z] to indicate that any lowercase letter from a to z or any uppercase letter from A to Z is permitted in the designated position. As an example, to format a column to accommodate any five-letter word with the first letter capitalized, you would use the following expression:
PostalCode LIKE '[A-Z][a-z][a-z][a-z][a-z]'
Use [a-zA-Z0-9] to indicate that any letter or numeral is permitted in the designated position. As an example, to format a column to accommodate any five-character value, you would use the following expression:
PostalCode LIKE '[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]'
You can also add and remove constraints in Transact-SQL by using the CREATE TABLE or ALTER TABLE command, such as in the following example:
USE CUSTOMER ALTER TABLE Sales.Customers ADD CONSTRAINT CheckZipFormat CHECK (([PostalCode] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] [0-9]'))
Not null constraints specify that the column does not accept null values. Normally, you set not null constraints when you create the table. You can also set not null constraints when you alter a table. In SQL Server Management Studio, the Allow Nulls column in the Table view controls the use of this constraint. If the Allow Nulls column is cleared, the related table column does not accept nulls.
A rule is a constraint that you can apply to multiple columns or tables. Rules perform the same function as check constraints and are maintained in SQL Server 2005 for backward compatibility with earlier versions of SQL Server. Microsoft recommends that you use check constraints rather than rules. Check constraints are more customizable and more concise than rules. For example, although you can apply only one rule to a column, you can apply multiple check constraints to a column.
Rules can be useful in certain situations, however. Constraints are defined within table definitions, whereas rules are independently defined objects, and therefore, are not limited to only one particular table. Rules are also bound to a table after the table is created, and they are not deleted if the table is deleted. Another advantage of rules is that they can be bound to any user-defined data type.
If you use care when you apply rules, you can still use rules in situations that make them a better choice than constraints. To view existing rules in SQL Server Management Studio, complete the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer view, expand the Databases node, and then expand the database to show its resource nodes.
Expand the Programmability and Rules nodes. You will see any existing rules listed.
The Transact-SQL commands for creating and managing rules are CREATE RULE and DROP RULE. You can use CREATE RULE as follows:
CREATE RULE CheckFormatZip AS @value LIKE '[09][09][09][09][09][09][09][09][09]'
After you have created a rule, you must activate the rule in order to use it. You use a special stored procedure called sp_bindrule to bind the rule to a particular table column or user-defined data type. You can also use sp_unbindrule to remove a rule that is bound to a table column or user-defined data type. Use the following syntax when binding and unbinding rules:
sp_bindrule <'rule'>, '<object_name'>, [<'futureonly_flag'>] sp_unbindrule 'object name'