2.4 Constraints

Constraints allow you to automatically enforce the integrity of data and to filter the data that is placed in a database. In a sense, constraints are rules that define which data values are valid during INSERT, UPDATE, and DELETE operations. When a data modification transaction breaks the rules of a constraint, the transaction is rejected.

In the ANSI standard, there are four constraint types: CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY. (The RDBMS platforms may allow more. Refer to different platform sections of the CREATE/ALTER TABLE Statement to find out these exceptions.)

2.4.1 Scope

Constraints may be applied at the column-level or the table-level:

Column-level constraints: Declared as part of a column definition and apply only to that column.
Table-level constraints: Declared independently from any column definitions (traditionally, at the end of a CREATE TABLE statement) and may apply to one or more columns in the table. A table constraint is required when you wish to define a constraint that applies to more than one column.

2.4.2 Syntax

Constraints are defined when you create or alter a table. The general syntax for constraints is shown here:

CONSTRAINT [constraint_name] constraint_type [(column [,...])] 

[predicate] [constraint_deferment] [deferment_timing]

The syntax elements are as follows:

CONSTRAINT [constraint_name]: Begins a constraint definition and, optionally, provides a name for the constraint. When you omit constraint_name, the system will create one for you automatically. On some platforms, like DB2, you may omit the CONSTRAINT keyword as well. System-generated names are often incomprehensible. It is good practice to specify human-readable, sensible names for constraints.
constraint_type: Declares the constraint as one of the allowable types: CHECK, PRIMARY KEY, UNIQUE, or FOREIGN KEY. More information about each type of constraint appears later in this section.
column [,...]: Associates one or more columns with the constraint. Specify the columns in a comma-delimited list. The column list must be enclosed in parentheses. The column list should be omitted for column-level constraints. Columns are not used in every constraint. For example, CHECK constraints do not generally use column references.
predicate: Defines a predicate for CHECK constraints.
constraint_deferment: Declares a constraint as DEFERRABLE or NOT DEFERRABLE. When a constraint is deferrable, you can specify that it be checked for a rules violation at the end of a transaction. When a constraint is not deferrable, it is checked for a rules violation at the conclusion of every SQL statement.
deferment_timing: Declares a deferrable constraint as INITIALLY DEFERRED or INITIALLY IMMEDIATE. When set to INITIALLY DEFERRED, the constraint check time will be deferred until the end of a transaction, even if it is composed of many SQL statements. In this case, the constraint must also be DEFERRABLE. When set to INITIALLY IMMEDIATE, the constraint check time is at the end of every SQL statement. In this case, the constraint may be either DEFERRABLE or NOT DEFERRABLE. The default is INITIALLY IMMEDIATE.

Note that the vendor platforms may have some variations on this syntax. Check different platform sections of CREATE/ALTER TABLE Statement for more details.

2.4.3 PRIMARY KEY Constraints

A PRIMARY KEY constraint declares one or more columns whose values uniquely identify each record in the table. It is considered a special case of the UNIQUE constraint. Some rules about primary keys:

Only one primary key may exist on a table at a time.
Columns in the primary key cannot contain datatypes of BLOB, CLOB, NCLOB, or ARRAY.
Primary keys may be defined at the column level for a single column key or at the table level if multiple columns make up the primary key.
Values in the primary key column(s) must be unique and not NULL.
In a multicolumn primary key, called a concatenated key, the combination of values in all of the key columns must be unique and not NULL.
Foreign keys can be declared that reference the primary key of a table to establish direct relationships between tables (or possibly, though rarely, within a single table).

The following ANSI standard code includes the options for creating both a table- and column-level primary key constraint on a table called distributors. The first example shows a column-level primary-key constraint, while the second shows a table-level constraint:

-- Creating a column-level constraint

CREATE TABLE distributors

    (dist_id       CHAR(4)    NOT NULL PRIMARY KEY,

     dist_name     VARCHAR(40),

     dist_address1 VARCHAR(40),

     dist_address2 VARCHAR(40),

     city          VARCHAR(20),

     state         CHAR(2)    ,

     zip           CHAR(5)    ,

     phone         CHAR(12)   ,

     sales_rep     INT        );



-- Creating a table-level constraint

CREATE TABLE distributors

    (dist_id       CHAR(4)     NOT NULL,

     dist_name     VARCHAR(40),

     dist_address1 VARCHAR(40),

     dist_address2 VARCHAR(40),

     city          VARCHAR(20),

     state         CHAR(2)    ,

     zip           CHAR(5)    ,

     phone         CHAR(12)   ,

     sales_rep     INT        ,

CONSTRAINT     pk_dist_id  PRIMARY KEY (dist_id));

In the example showing a table-level primary key, we could easily have created a concatenated key by listing several columns separated by commas between each one.

2.4.4 FOREIGN KEY Constraints

A FOREIGN KEY constraint defines one or more columns in the table as referencing columns to a UNIQUE or PRIMARY KEY in another table. (A foreign key can reference a unique or primary key in the same table as the foreign key itself, but such foreign keys are rare.) Foreign keys can then prevent the entry of data into a table where there is no matching value in the related table. They are the primary means of identifying the relationship between tables in a relational database. Some rules about foreign keys:

Many foreign keys may exist on a table at a time.
A foreign key can be declared to reference either the primary key or a unique key of another table to establish a direct relationship between the two tables.

The full SQL2003 syntax for foreign keys is more elaborate than the general syntax for constraints shown earlier, and is dependent on whether you are making a table-level or column-level declaration:

-- Table-level foreign key

[CONSTRAINT [constraint_name] ] 

FOREIGN KEY (local_column [,...] )

REFERENCES referenced_table [ (referenced_column [,...]) ]

[MATCH {FULL | PARTIAL | SIMPLE} ]

[ON UPDATE {NO ACTION | CASCADE | RESTRICT | 

   SET NULL | SET DEFAULT} ]

[ON DELETE {NO ACTION | CASCADE | RESTRICT | 

   SET NULL | SET DEFAULT} ]

[constraint_deferment] [deferment_timing]



-- Column-level foreign key

[CONSTRAINT [constraint_name] ] 

REFERENCES referenced_table [ (referenced_column [,...]) ]

[MATCH {FULL | PARTIAL | SIMPLE} ]

[ON UPDATE {NO ACTION | CASCADE | RESTRICT | 

   SET NULL | SET DEFAULT} ]

[ON DELETE {NO ACTION | CASCADE | RESTRICT | 

   SET NULL | SET DEFAULT} ]

[constraint_deferment] [deferment_timing]

Keywords common to a standard constraint declaration are described above, under the "Syntax" section. Keywords specific to foreign keys are described in the following list:

FOREIGN KEY (local_column [,...] )

Declares one or more columns of the table being created or altered and are subject to the foreign key constraint. This syntax is used only in table-level declarations and is excluded from column-level declarations. We recommend that the ordinal position and datatype of each column in the local_column list match comparably with the ordinal position and datatype of the columns in the referenced_column list.

REFERENCES referenced_table [ (referenced_column [,...]) ]

Names the table and, where appropriate, the column(s) that hold the valid list of values for the foreign key. A referenced_column must already be named in a NOT DEFERRABLE PRIMARY KEY or NOT DEFERRABLE UNIQUE KEY. The table types must match. For example, if one is a local temporary table then both must be local temporary tables.

MATCH {FULL | PARTIAL | SIMPLE}

Defines the degree of matching required between the local and referenced columns in foreign key constraints when NULLs are present.

FULL: Declares that a match is acceptable when: 1) none of the referencing columns are NULL and match all of the values of the referenced column, or 2) all of the referencing columns are NULL. In general, you should use MATCH FULL or else ensure that all columns involved have NOT NULL constraints.
PARTIAL: Declares that a match is acceptable when at least one of the referenced column is NULL and the others match the corresponding referenced columns.
SIMPLE: Declares that a match is acceptable when any of the values of the referencing column are NULL or a match. This is the default.

ON UPDATE

Specifies that, when an UPDATE operation affects one or more referenced_columns of the primary or unique key on the referenced table, a corresponding action should be taken to ensure the foreign key does not lose data integrity. ON UPDATE may be declared independently of or with the ON DELETE clause. When omitted, the default is ON UPDATE NO ACTION.

ON DELETE

Specifies that when a DELETE operation affects one or more referenced_columns of the primary or unique key on the referenced table, that a corresponding action should be taken to ensure the foreign key does not lose data integrity. ON DELETE may be declared independent of or with the ON UPDATE clause. When omitted, the default for the ANSI standard is ON DELETE NO ACTION.

NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT

Defines the action the database takes to maintain data integrity of the foreign key when a referenced primary or unique key constraint value is changed or deleted.

NO ACTION: Tells the database to do nothing when a primary key or unique key value, referenced by a foreign key, is changed or deleted.
CASCADE: Tells the database to perform the same action (i.e., DELETE or UPDATE) on the matching foreign key when a primary key or unique key value is changed or deleted.
RESTRICT: Tells the database to prevent changes to the primary key or unique key value, referenced by the foreign key.
SET NULL: Tells the database to set the value in the foreign key to NULL when a primary key or unique key value is changed or deleted.
SET DEFAULT: Tells the database to set the value in the foreign key to the default (using default values you specify for each column) when a primary key or unique key value is changed or deleted.

As with the code example for primary keys, you can adapt this generic syntax to both column-level and table-level foreign key constraints. Note that column-level and table-level constraints perform their function in exactly the same way. They are merely defined at different levels of the CREATE TABLE command. In the following example, we create a single-column foreign key on the salesrep column referencing the empid column of the employee table. We create the foreign key two different ways, the first time at the column level, the second time at the table level:

-- Creating a column-level constraint

CREATE TABLE distributors

    (dist_id       CHAR(4)    PRIMARY KEY,

     dist_name     VARCHAR(40),

     dist_address1 VARCHAR(40),

     dist_address2 VARCHAR(40),

     city          VARCHAR(20),

     state         CHAR(2)    ,

     zip           CHAR(5)    ,

     phone         CHAR(12)   ,

     sales_rep     INT        NOT 

     NULL REFERENCES employee(empid));



-- Creating a table-level constraint

CREATE TABLE distributors

    (dist_id       CHAR(4)    NOT NULL,

     dist_name     VARCHAR(40),

     dist_address1 VARCHAR(40),

     dist_address2 VARCHAR(40),

     city          VARCHAR(20),

     state         CHAR(2)    ,

     zip           CHAR(5)    ,

     phone         CHAR(12)   ,

     sales_rep     INT        ,

CONSTRAINT pk_dist_id  PRIMARY KEY (dist_id),

CONSTRAINT fk_empid   FOREIGN KEY (sales_rep) 

   REFERENCES employee(empid));

2.4.5 UNIQUE Constraints

A UNIQUE constraint, sometimes called a candidate key, declares that the values in one column, or the combination of values in more than one column, must be unique. Rules concerning unique constraints include:

Columns in a unique key cannot contain datatypes of BLOB, CLOB, NCLOB, or ARRAY.
The column or columns in a unique key may not be identical to those in any other unique keys, or to any columns in the primary key of the table.
A single NULL value, if the unique key allows NULL values, is allowed.
SQL2003 allows you to substitute the column list, shown in the general syntax diagram for constraints, with the keyword (VALUE). UNIQUE (VALUE) indicates that all columns in the table are part of the unique key. The VALUE keyword also disallows any other unique or primary keys on the table.

In the following example, we limit the number of distributors we do business with to only one distributor per ZIP Code. We also allow one (and only one) "catch-all" distributor with a NULL ZIP Code. This functionality can be implemented easily using a UNIQUE constraint, either at the column or the table level:

-- Creating a column-level constraint

CREATE TABLE distributors

    (dist_id       CHAR(4)    PRIMARY KEY,

     dist_name     VARCHAR(40),

     dist_address1 VARCHAR(40),

     dist_address2 VARCHAR(40),

     city          VARCHAR(20),

     state         CHAR(2)    ,

     zip           CHAR(5)    UNIQUE ,

     phone         CHAR(12)   ,

     sales_rep     INT        NOT NULL 

   REFERENCES employee(empid));



-- Creating a table-level constraint

CREATE TABLE distributors

    (dist_id       CHAR(4)    NOT NULL,

     dist_name     VARCHAR(40),

     dist_address1 VARCHAR(40),

     dist_address2 VARCHAR(40),

     city          VARCHAR(20),

     state         CHAR(2)    ,

     zip           CHAR(5)    ,

     phone         CHAR(12)   ,

     sales_rep     INT        ,

CONSTRAINT pk_dist_id  PRIMARY KEY (dist_id),

CONSTRAINT fk_emp_id   FOREIGN KEY (sales_rep) 

   REFERENCES employee(empid),

CONSTRAINT unq_zip     UNIQUE (zip) );

2.4.6 CHECK Constraints

CHECK constraints allow you to perform comparison operations to ensure that values match specific conditions that you set out. The syntax for a check constraint is very similar to the general syntax for constraints:

[CONSTRAINT] [constraint_name] CHECK (search_conditions)

[constraint_deferment] [deferment_timing]

Other elements of the constraint are introduced earlier in this section. The following element is unique to the CHECK constraint:

search_conditions: Specifies one or more search conditions that constrain the values inserted into the column or table, using one or more expressions and a predicate. Multiple search conditions may be applied to a column in a single check constraint using the AND and OR operators. (Think of a WHERE clause.)

A check constraint is considered matched when the search conditions evaluate to TRUE or UNKNOWN. Check constraints are limited to Boolean operations (e.g., =, >=, <=, or <>), though they may include any SQL2003 predicates such as IN or LIKE. Check constraints may be appended to one another (when checking a single column) using the AND and OR operators. Some other rules about CHECK constraints:

A column or table may have one or more CHECK constraints.
A search condition cannot contain aggregate functions, except in a subquery.
A search condition cannot use non-deterministic functions or subqueries.
A check constraint can only reference like objects. So if a check constraint is declared on a global temporary table, it cannot then reference a permanent table.
A search condition cannot reference these ANSI functions: CURRENT_USER, SESSION_USER, SYSTEM_USER, USER, CURRENT_PATH, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP.

The following example adds a check constraint to the dist_id and zip columns. (This example uses generic code run on SQL Server.) The ZIP Code must fall into the normal ranges for postal ZIP Codes, while the dist_id values are allowed to contain either four alphabetic characters or two alphabetic and two numeric characters:

-- Creating column-level CHECK constraints

CREATE TABLE distributors

   (dist_id       CHAR(4)     

      CONSTRAINT pk_dist_id PRIMARY KEY 

      CONSTRAINT ck_dist_id CHECK 

         (dist_id LIKE '[A-Z][A-Z][A-Z][A-Z]' OR

         dist_id LIKE '[A-Z][A-Z][0-9][0-9]'),

    dist_name     VARCHAR(40),

    dist_address1 VARCHAR(40),

    dist_address2 VARCHAR(40),

    city          VARCHAR(20),

    state         CHAR(2) 

      CONSTRAINT def_st DEFAULT ("CA"),

    zip           CHAR(5)    

      CONSTRAINT unq_dist_zip UNIQUE 

      CONSTRAINT ck_dist_zip CHECK

         (zip LIKE '[0-9][0-9][0-9][0-9][0-9]'),

    phone         CHAR(12),

    sales_rep     INT        

      NOT NULL DEFAULT USER REFERENCES employee(emp_id))

Table of Contents