Constraints

Constraints allow you to define the way Microsoft® SQL Server™ 2000 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 triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.

Classes of Constraints

SQL Server 2000 supports 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 not null 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 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. Foreign keys prevent actions that would leave rows with foreign key values when there are no candidate keys with that value. In the following sample, the order_part table establishes a foreign key referencing the part_sample table defined earlier. Usually, 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)
                  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 if you attempt to delete a row to which existing foreign keys point. The ON DELETE clause has two 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.

  The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing foreign keys point. It also supports the NO ACTION and CASCADE 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 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 computer 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) )

See Also

CREATE TABLE

Creating and Modifying a Table