When a base table is created or updated (CREATE TABLE, ALTER TABLE), table constraints can be specified. A table constraint is an integrity constraint which can refer to more than one column in the base table. None of the columns can be a multiple column.
For CALL DML tables, only the integrity constraint PRIMARY KEY can be defined.
table_constraint ::=
{
UNIQUE ( column ,...) |
PRIMARY KEY ( column ,...) |
FOREIGN KEY ( column ,...) REFERENCES table [( column ,...)] |
CHECK ( search_condition )
}
UNIQUE (column,...)
UNIQUE constraint.
The combination of values for the columns specified must be unique within the table in the case that none of the values is equal to the NULL value.
The length of the columns must observe the restrictions that apply to an index (see the CREATE INDEX statement, "CREATE INDEX - Create index").
A column cannot be specified more than once in the column list.
The sequence of columns specified with the column list must differ from the sequence of columns specified with the column list of another UNIQUE constraint or of a PRIMARY KEY constraint for the same table.
PRIMARY KEY (column,...)
PRIMARY KEY constraint.
The specified columns together constitute the primary key of the table.
The set of column values must be unique. Only one primary key can be defined for each table.
None of the columns can be VARCHAR or NVARCHAR columns. The sum of the column lengths must not exceed 256 characters.
A column cannot be specified more than once in the column list.
The sequence of columns specified with the column list must differ from the sequence of columns specified with the column list of any UNIQUE constraint for the same table.
The NOT NULL constraint applies implicitly to the primary key columns.
FOREIGN KEY ... REFERENCES
Referential constraint.The referencing columns can only contain a set of values that does not include any NULL values if the set of values also occurs in the referenced columns.You must specify the same number of columns in the referencing and referenced table. The data types of the corresponding columns must be exactly the same.
The current authorization identifier must have the REFERENCES privilege for the referenced column.
FOREIGN KEY (column,...)
Columns of the referencing table whose sets of values should be contained in the referenced base table.
A column cannot be specified more than once in the column list.
REFERENCES 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 catalog name must be the same as the catalog name of the referencing table.
(column,...)
Names of the referenced columns.
A UNIQUE or primary key constraint that uses the same columns and the same order must be defined for these columns. None of the columns can be a multiple column.
A column cannot be specified more than once in the column list.
(column,...) omitted:
The primary key of the referenced table is used as the referenced column.
CHECK (search_condition)
Check constraint.
The search condition search_condition must return the truth value true or undefined (but not the truth value false) for each row in the table.
The following restrictions apply to search_condition:
search_condition cannot contain any host variables.
search_condition cannot contain any aggregate functions.
search_condition cannot include any subqueries, i.e. search_condition can only reference columns 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 may not contain a User Defined Function (UDF).
Special considerations for CALL DML tables
The following restrictions must be taken into account for table 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 the table constraint.
The data type of the column with PRIMARY KEY must be CHAR, NUMERIC, INTEGER or SMALLINT. In the case of NUMERIC, decimal places are not permitted.
The sum of the column lengths must be between 4 and 256 characters.
The table constraint defines a compound primary key. The name corresponds to the verbal attribute name of the compound primary key in SESAM/SQL V1.x.
Table constraints and indexes
If you define a UNIQUE constraint, an index with the columns specified for UNIQUE is used:
If you have already defined an index with CREATE INDEX that contains these columns, 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.
Example of a table constraint
The example shows part of the CREATE TABLE statement used to create the CUSTOMERS table of the ORDERCUST database.
 | CREATE TABLE customers ... CONSTRAINT PlausZip CHECK ((country = 'D' AND zip >= 00000) OR (country <> 'D')) ... |