Constraints

Constraints let you define the way the SQL Server 2005 Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.

Classes of Constraints

SQL Server 2005 supports the following classes of constraints:

• NOT NULL specifies that the column does not accept NULL values. For more information, see Allowing Null Values.

• CHECK constraints enforce domain integrity by limiting the values that can be put in a column. For more information, see CHECK Constraints.
  A CHECK constraint specifies a Boolean (evaluates to TRUE, FALSE, or unknown) search condition that is applied to all values that are entered for the column. All values that evaluate to FALSE are rejected. You can specify multiple CHECK constraints for each column. The following sample shows creating the constraint chk_id. This constraint additionally enforces the domain of the primary key by making sure 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.
  In a UNIQUE constraint, no two rows in the table can have the same value for the columns. Primary keys also enforce uniqueness, but primary keys do not allow for NULL as one of the unique values. For more information, see UNIQUE Constraints.

• PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. For more information, see PRIMARY KEY Constraints.
  No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key. We recommend using a small, integer column as a primary key. Each table should have a primary key. A column or combination of columns that qualify as a primary key value is referred to as a candidate key.
  The following example creates the part_sample table and specifies the part_nmbr field as the 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 and enforce the relationships between tables. For more information, see FOREIGN KEY Constraints.
  A foreign key in one table points to a candidate key in another table. In the following example, the order_part table establishes a foreign key that references the part_sample table defined previously.

  CREATE TABLE order_part
        (order_nmbr      int,
        part_nmbr      int
           FOREIGN KEY REFERENCES part_sample(part_nmbr)
              ON DELETE NO ACTION,
        qty_ordered      int);
  GO
  

  You cannot insert a row with a foreign key value, except NULL, if there is no candidate key with that value. The ON DELETE clause controls what actions are taken when you try to delete a row to which existing foreign keys point. The ON DELETE clause has the following options:

  • NO ACTION specifies that the deletion fails with an error.
  • CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also deleted.
  • SET NULL specifies that all rows with foreign keys pointing to the deleted row are set to NULL.
  • SET DEFAULT specifies that all rows with foreign keys pointing to the deleted row are set to their default value. For more information, see Defaults.

  The ON UPDATE clause defines the actions that are taken if you try to update a candidate key value to which existing foreign keys point. This clause also supports the NO ACTION, CASCADE, SET NULL and SET DEFAULT options.

Column and Table Constraints

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 previous examples 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 occurring in a computer in a factory. Assume that events of several types can occur at the same time, but that no two events occurring at the same time can be of the same type. This can be enforced in the table by including both the event_type and event_time columns in a two-column primary key, as shown in the following example.

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) )

See Also

Other Resources

CREATE TABLE (Transact-SQL)
Enforcing Data Integrity

Help and Information

Getting SQL Server 2005 Assistance