SQL in a Nutshell, 3rd Edition

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 a name for you automatically. On some platforms, you may omit the CONSTRAINT keyword as well.

Tip

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, 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 ...