Constraints

Constraints offer a way to have Microsoft® SQL Server™ enforce the integrity of a database automatically. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity, preferred over triggers, rules, and defaults. They are also used by the query optimizer to improve performance in selectivity estimation, cost calculations, and query rewriting.

There are five classes of constraints.

• NOT NULL specifies that the column does not accept NULL values.
• CHECK constraints enforce domain integrity by limiting the values that can be placed in a column.

  A CHECK constraint specifies a Boolean (evaluates to TRUE or FALSE) search condition that is applied to all values entered for the column; all values that do not evaluate to TRUE are rejected. You can specify multiple CHECK constraints for each column. This sample shows the creation of a named constraint, chk_id, that further enforces the domain of the primary key by ensuring that only numbers within a specified range are entered for the key.

  CREATE TABLE cust_sample

  (

  cust_id                                int                PRIMARY KEY,

  cust_name                        char(50),

  cust_address                        char(50),

  cust_credit_limit        money,

  CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )

  )

    

• UNIQUE constraints enforce the uniqueness of the values in a set of columns.

  No two rows in the table are allowed to have the same nonNULL values for the columns in a UNIQUE constraint. Primary keys also enforce uniqueness, but primary keys do not allow NULL values. A UNIQUE constraint is preferred over a unique index.

• PRIMARY KEY constraints identify the column or set of columns whose values uniquely identify a row in a table.

  No two rows in a table can have the same primary key value. You cannot enter a NULL value for any column in a primary key. NULL is a special value in databases that represents an unknown value, which is distinct from a blank or 0 value. Using a small, integer column as a primary key is recommended. Each table should have a primary key.

  A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. The database administrator picks one of the candidate keys to be the primary key. For example, in the part_sample table both part_nmbr and part_name could be candidate keys, but only part_nmbr is chosen as a primary key.

  CREATE TABLE part_sample

  (part_nmbr                int                        PRIMARY KEY,

  part_name                char(30),

  part_weight                decimal(6,2),

  part_color                char(15) )

    

• FOREIGN KEY constraints identify the relationships between tables.

  A foreign key in one table points to a candidate key in another table. You cannot insert a row with a foreign key value (except NULL) if there is no candidate key with that value. You cannot delete a row from the referenced table if there are any foreign key values referencing that candidate key. In the following sample, the order_part table establishes a foreign key referencing the part_sample table defined earlier. Normally, order_part would also have a foreign key against an order table, but this is a simple example.

  CREATE TABLE order_part

  (order_nmbr                int,

  part_nmbr                int

  FOREIGN KEY REFERENCES part_sample(part_nmbr),

  qty_ordered                int)

  GO

Constraints can be column constraints or table constraints:

• A column constraint is specified as part of a column definition and applies only to that column (the constraints in the earlier samples are column constraints).
• A table constraint is declared independently from a column definition and can apply to more than one column in a table.

Table constraints must be used when more than one column must be included in a constraint.

For example, if a table has two or more columns in the primary key, you must use a table constraint to include both columns in the primary key. Consider a table that records events happening in a machine in a factory. Assume that events of several types can happen at the same time, but that no two events happening at the same time can be of the same type. This can be enforced in the table by including both the type and time columns in a two-column primary key.

CREATE TABLE factory_process

    (event_type    int,

    event_time    datetime,

    event_site    char(50),

    event_desc    char(1024),

CONSTRAINT event_key PRIMARY KEY (event_type, event_time) )