SQL in a Nutshell, 2nd Edition [Electronic resources] نسخه متنی

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