Column constraints

When a base table is created or updated (CREATE TABLE, ALTER TABLE), column constraints can be specified in the column definitions for the individual columns. The column cannot be a multiple column.

A column constraint is an integrity constraint on a single column. All the values in the column must satisfy the integrity constraint.

For CALL DML tables, only the integrity constraint PRIMARY KEY can be defined.

---

col_constraint ::=

{

   NOT NULL |

   UNIQUE |

   PRIMARY KEY |

   REFERENCES  table [( column )] |

   CHECK ( search_condition )

}

---

NOT NULL

NOT NULL constraint.
The column cannot contain any NULL values.

The NOT NULL constraint is stored as a check constraint (column IS NOT NULL).

UNIQUE

UNIQUE constraint.
Non-null column values must be unique.

The column length must observe the restrictions that apply to an index (see CREATE INDEX statement, "CREATE INDEX - Create index").

PRIMARY KEY

PRIMARY KEY constraint.
The column is the primary key of the table. The values in the column must be unique. Only one primary key can be defined for each table.

The column cannot have the data type VARCHAR or NVARCHAR. In a CALL DML table, the column length must be between 4 and 256 characters. In an SQL table, there is no minimum column length.

The NOT NULL constraint applies implicitly to a primary key column.

REFERENCES

Referential constraint.
The column of the referencing table can only contain a non-NULL value if the same value is included in the referenced column of the referenced table.

The current authorization identifier must have the REFERENCES privilege for the referenced column.

table

Name of the referenced base table.The referenced base table must be an SQL table. The name of the referenced base table can be qualified by a database or schema name. The database name must be the same as the database name of the referencing table.

(column)

Name of the referenced column.
The referenced column must be defined with UNIQUE or PRIMARY KEY. The referenced column cannot be a multiple column. The referencing column and referenced column must have exactly the same data type.

(column) omitted:
The primary key of the referenced table is used as the referenced column. The referencing column and referenced column must have exactly the same data type.

CHECK (search_condition)

Check constraint.
Each value in the column must accept the truth value true or unknown, but not, however, the truth value false for the search condition search_condition.

The following restrictions apply to search_condition:

• • search_condition cannot contain any host variables.

  • search_condition cannot contain any aggregate functions.

  • search_condition cannot contain any subqueries, i.e. it can only reference the column of the table to which the column constraint belongs.

  • search_condition cannot contain a time function.

  • search_condition cannot contain special variables.

  • search_condition cannot contain any transliteration between EBCDIC and Unicode.

  • search_condition cannot contain any conversion of uppercase letters to lowercase letters or of lowercase letters to uppercase letters if the string to be converted is a Unicode string.

  • search_condition cannot be a multiple column.

  • search_condition may not contain a User Defined Function (UDF).

Special considerations for CALL DML tables

The following restrictions must be taken into account for column constraints in CALL DML tables:

• A CALL DML table must contain exactly one primary key as a column or table constraint.

• Only PRIMARY KEY is permitted as a column constraint.

• The data type of the column with PRIMARY KEY must be CHAR with a length of at least 4 characters.

Column constraints and indexes

If you define a UNIQUE constraint, an index with the column specified for UNIQUE is used:

• If you have already defined an index with CREATE INDEX that contains this column, this index is also used for the UNIQUE constraint.

• Otherwise, the required index is generated implicitly. The name of the implicitly generated index starts with UI and is followed by a 16-digit number.
  The index is stored in the space for the base table. In the case of a partitioned table the index is stored in the space of the table’s first partition.

Examples of column constraints

The example shows part of the CREATE TABLE statement used to create the SERVICE table in the ORDERCUST database. A check constraint is defined for the column service_total.

CREATE TABLE service (..., service_total INTEGER CONSTRAINT service_total_pos CHECK (service_total > 0))

A Non-NULL constraint with an explicitly specified name is defined for the COMPANY column. CUST_NUM is defined as the primary key in the column constraint CUST_NUM_PRIMARY.

CREATE TABLE customers (cust_num INTEGER CONSTRAINT cust_num_primary PRIMARY KEY, company CHAR(40) CONSTRAINT company_notnull NOT NULL)

A referential constraint FOREIGN1 is defined for the ORDERS table. The foreign key ORDERS.CUST_NUM references the column CUSTOMERS.CUST.NUM.

ALTER TABLE orders ADD CONSTRAINT foreign1 FOREIGN KEY(cust_num) REFERENCES customers(cust_num)