Table Level and Column Level Constraints

You may define constraints at either the column level or the table level. If a constraint references more than one column in a table, you must define the constraint at the table level. Defining constraints at the table level requires that you identify which column is being constrained; I prefer the column level constraint—less typing. I also find it clearer to review column-level constraints. This syntax difference has no effect on performance.

Naming Constraints

You may, optionally, provide a name for each of the constraints. I named the table-level constraints, in titles, to demonstrate this. Primary key constraints usually include the letters PK somewhere, to indicate the nature of the constraint; reference constraints include FK somewhere. The remainder of the name is often the name of an index on the table. If you do not name your constraints, the server will do it for you, usually incorporating the rules I mentioned as well as a seemingly random key composed of hexadecimal digits at the end. These are usually less beautiful than names you would choose, so naming your constraints is encouraged.

Some Keys Create an Index

When a primary key or unique key is defined, an index is created to enforce that key. For PKs, this is a clustered index by default, but you may specify the keyword CLUSTERED or NONCLUSTERED after the words primary key to define the type of index created. Primary and unique keys require this index to enforce the uniqueness of their data. There is no way to prevent index creation, nor can you drop them without first removing the constraint.

Reference Constraints Do Not Require Column

When defining a reference constraint, it is not necessary to define the column in the referenced table. Usually, the reference constraint references a primary key in another table. To take advantage of this, if you define a reference constraint on a table without specifying the column in that table, the server assumes you meant to define the key on the referenced table’s primary key.

Upper Limits

Table 9.4 provides the upper limits on constraints allowed in a single table.

Using Unique Constraints

Defining a unique constraint on a column is also called defining an alternate key. This is short for alternate primary key. If a column or set of columns can guarantee the uniqueness of a row, it can qualify for use as a primary key.

Unique columns are also useful on multiple columns. For example, the stores table could have a unique column defined on name, city, and state (or name and zip) to prevent two stores with the same name in the same city.

---

Tip:  Unique constraints allow a single null value. On multiple column constraints, the combination of columns cannot be null for all the columns more than once. This is a way to enforce that each row provides a value for at least one of a set of columns: after creating the unique constraint, insert a dummy row with nulls in all the columns participating in the constraint.

---

DRI Example

I’ll finish up this section with a complete example using a fictional database. The pubs schema includes DRI constraints on its tables, so you can try violating key constraints there as well, to see what happens.

You have been assigned the task of creating a few, simple tables containing declarative referential integrity constraints for a retail business. Like all retail businesses that are used for examples in instructional books, this company sells widgets.

Complete the following tasks:

•  Create a customer table to track customers. This table will hold name, address, and phone information.

•  Create an item table to track the different types of items sold. It should contain a description, price, and number in stock.

•  Create an invoices table with date sold, who bought it, and total price.

•  Create an invoice detail table listing each item that sold, the amount sold, and to what invoice it belongs.

•  After the tables have been created, a list of data needs to be entered.

Here is the order in which I’ll perform the tasks:

1.  Create the column definitions for the tables.

2.  Identify the primary key for the table. If there is no primary key column, figure out if that’s OK, or whether a key column should be added.

3.  Define reference constraints on each column.

4.  Submit the final tables to the server.

5.  Insert data.