SQL Server Magazine

This month, I look at another table-centric aspect of a topic from Exam 70-29, the "Implementing a Database on SQL Server 7.0" course exam. The topic is creating tables that enforce data integrity and referential integrity, and implementing constraints. That sentence looks like two topics, but constraints are the preferred way to enforce integrity. In this article, I look at the different types of constraints and when and why you use them. I discuss how to implement constraints, and when you might need to disable them.

Constraints and Integrity
A constraint is a limitation that you place on the data that users can enter into a column or group of columns. A constraint is part of the table definition; you can implement constraints when you create the table or later. You can remove a constraint from the table without affecting the table or the data, and you can temporarily disable certain constraints.

The distinction between column-level and table-level constraints is that you define a column-level constraint on one column only. Therefore, you can define it as part of the column definition. However, to define a constraint on more than one column—such as a primary-key constraint defined on two columns—you need to define the columns in the table first, then add the constraint definition at the end of the table definition. That constraint type is at the table level. In practice, you don't need to worry about the distinction because the syntax doesn't change.

You can ensure data integrity by various means, including the use of triggers and other procedural code. But in part because constraints are ANSI-SQL compliant, they're the method Microsoft recommends. Different types of constraints address different types of integrity, and looking at constraints in the context of the integrity types they support and enforce can be helpful.

The Primary-Key Constraint
The primary-key constraint designates a column, or a combination of columns, as the primary key for the table. This action enforces entity integrity, which requires each row to have a unique identifier, so that data modifications or queries always refer to a specific row without ambiguity. When you place a primary-key constraint on a column, you're requiring each row in that column to have a unique value, which can't be NULL. A primary-key constraint on multiple columns requires the combination of values in those columns to be unique. Examples of primary keys include invoice number, employee ID, purchase order number, and item or part number.

You can have only one primary-key constraint per table. When you define a primary-key constraint, SQL Server automatically builds an index on the designated column or columns. By default, the index is clustered, although you can specify a nonclustered index. This unique index enforces the entity integrity for the primary key. You can't have a primary-key constraint without the index, and you can't drop the index. To remove the index, you must drop the constraint.

When you define the constraint and build the index, any existing data must already meet the constraint requirements of no duplicate values and no nulls. If it doesn't, SQL Server can't build the index, and the constraint definition fails. So you can't disable the function that checks existing data when you add a primary-key constraint. After you define the constraint, all new data and any modified rows must satisfy the constraint. With a defined primary-key constraint, you don't need to add any code to enforce entity integrity. However, if you're accessing SQL Server through an application such as Microsoft Access or a Visual Basic (VB) application, that interface must be able to handle the returned condition when a constraint violation occurs. The syntax for adding a primary-key constraint, using an example from the Pubs database, is

ALTER TABLE [dbo].[authors] 
ADD CONSTRAINT [UPKCL_auidind] PRIMARY KEY
CLUSTERED ([au_id] ) 

Note the naming convention SQL Server uses: UPKCL means Unique, Primary Key, CLustered. Name your constraints when you create them, and use a naming convention you're comfortable with. If you don't, SQL Server will generate a name that includes a string of hexadecimal characters, such as UPKCL_authors_ auid_01a5b84e45d. This naming system makes it hard to refer to the constraint later.

The Unique Constraint
The unique constraint enforces uniqueness in a column or combination of columns. You'd typically use it for a candidate, or surrogate, key—a column or columns that you could have chosen as a primary key, but didn't. One example is a table that includes employee ID as the primary key but also enforces uniqueness on Social Security number (SSN). Another example is a delivery service storing drivers' license numbers, but because two states might have issued the same number, the company enforces the unique combination of state plus driver's license number.

The unique constraint also enforces uniqueness by building an index on the designated column or columns. The default is a nonclustered index, but you can specify a clustered index. A unique index permits one NULL value. A second NULL value produces a constraint violation error. As with the primary-key constraint, you can't drop this index unless you also drop the constraint. And you can't disable the checking of existing data because SQL Server can create the index only on data that contains no duplicate values. Because of the presence of this index, you can't temporarily disable checking on new data, either.

You can have multiple unique constraints on a table. For example, the employees table might have unique constraints on SSN, driver's license + state, telephone extension, and security badge number.

The Foreign-Key Constraint
The foreign-key constraint defines the relationship between a column or combination of columns in the current table and a column or combination of columns in another table. In other words, it enforces referential integrity. This relationship might be one to one, such as in the case of an employee in the payroll table who must already exist in the employees table. Or it could be a many-to-one relationship. A typical example of a many-to-one foreign-key relationship is the Customer ID in the invoices table. In the invoices table, the CustomerID column is the foreign key; it refers to the customers table's CustomerID column, which is the primary key. The invoices table might have several entries for one customer.

Placing a foreign-key constraint on CustomerID ensures that the CustomerID a user enters into an invoice is valid. In other words, you can't invoice a nonexistent customer. But it also enforces another rule: You can't delete a customer with an associated entry in the invoices table. This restriction removes the risk of orphaned records.

A foreign-key constraint in a table must refer to a column or columns in the referenced table that either form the primary key in that table or have a unique set of values that a unique constraint enforces. A foreign-key constraint doesn't automatically build an index. However, building one is a good idea because it speeds the process of referencing and joining the tables. The columns that participate in the foreign-key relationship are also the columns you use to join the tables. If you need a customer address on an invoice, you join the invoices table to the customers table by using CustomerID as the join column.

   Prev. page   [1] 2     next page