SQL Statements › CREATE TABLE › Table Constraint Definition › Description

Description

UNIQUE

The UNIQUE table-level constraint specifies that the combination of values assigned to all the listed columns is to be unique.

Individual columns on which the column-level UNIQUE is specified can also be included in the column list for a table-level UNIQUE.

If you use the table-level UNIQUE constraint, CA Datacom Datadictionary generates a KEY entity-occurrence with the UNIQUE attribute to enforce uniqueness. This key may not be deleted directly. You must DROP the UNIQUE constraint to delete the key. The CA Datacom Datadictionary name for the key is the same as the table, followed by a number which makes the name unique. The 5-character CA Datacom/DB name of the key is SQnnn, where nnn is a sequential number unique to each database.

Note: Uniqueness is enforced at the key level, not at the column level, that is to say, UNIQUE forces unique values for the entire key and not for the individual columns making up the key.

PRIMARY KEY

Same as UNIQUE, except for the following:

• Only one primary key is allowed per table, whether specified at the column or table level.
• When the referential constraint ref-col-list is omitted, the columns of the primary key are implied by default.

(column-list)

Specify the column-list for the table-level UNIQUE or PRIMARY KEY constraint by enclosing one or more column names in parentheses. Use a comma to separate column names.

For example, if you specify UNIQUE (COLUMN_A, COLUMN_B) when you create a table, the following value assignments are valid because the combination of values for these two columns are unique in each row.

The total length of all columns listed in each table-level constraint cannot exceed 180 bytes.

referential constraint definition

See Referential Constraint Definition.

CHECK (search condition)

Allows you to check domain constraints specified in the search conditions.

The search condition must be enclosed in parentheses.

When you define a constraint through the CREATE TABLE or ALTER TABLE statements, do not include a function, special register, host variable, subquery, or external table reference in the search condition. If you do, CA Datacom/DB issues an error message informing you that none of those are allowed.

You may define multiple CHECK constraints to indicate exactly which constraint has been violated, since the constraint name is returned in the SQL Error Message.

For information on the search condition, see Search Conditions.

CONSTRAINT constraint-name

Allows you to specify a constraint-name. Constraint names must be unique within all constraints defined in the same schema. A fully qualified constraint name contains the schema ID, that is to say, the authorization ID or creator ID, and a 32-byte name.

If the name you specify already exists, CA Datacom/DB issues an error message. Query the SYSCONSTRSRC table (see Schema Information Tables (SIT)) to see which names are already in use.

If you do not specify a constraint-name, the system generates a name in the form CONSTRAINT_nnnn where nnnn is 0001 to 9999. For example, the first constraint-name generated by the system would be CONSTRAINT_0001, the second would be CONSTRAINT_0002, and so on.

Constraint names are returned in the SQLCA Error Message to indicate which of possibly several constraints were violated, and in the ALTER TABLE DROP statement to specify which constraint is to be dropped.