Single-Column Constraint Format

Use the Single-Column Constraint format to define and declare the name of at least one constraint on a single column, and to specify the mode of each constraint.

The Single-Column Constraint format can associate one or more constraints with a column, in order to perform any of the following tasks:
  • Create one or more data-integrity constraints for a column.
  • Specify a meaningful name for a constraint.
  • Specify the constraint-mode that controls the behavior of a constraint during insert, delete, and update operations.

This syntax fragment is part of the Column definition.

(1)
Single-Column Constraint Format

1! + 
2.2.1 NULL
2.2.1 NOT NULL
2.2.1 1 DISTINCT
2.2.1 UNIQUE
2.2.1 PRIMARY KEY
2.2.1  %REFERENCES Clause2
2.2.1  %CHECK Clause3
1? 1 %Constraint Definition4
Notes:

The NULL constraint specifies that the column can store NULL values. It is not valid for columns of serial or complex data types. The CREATE TABLE statement fails with an error if you specify both NOT NULL and NULL constraints on the same column.

The following example creates a standard table with two constraints: num, a primary-key constraint on the acc_num column; and code, a unique constraint on the acc_code column: 
CREATE TABLE accounts (
   acc_num   INTEGER PRIMARY KEY CONSTRAINT num,
   acc_code  INTEGER UNIQUE CONSTRAINT code,
   acc_descr CHAR(30));

The types of constraints used in this example are defined in sections that follow.