A column constraint element specifies the conditions which apply to each column.
This element is part of the table column element.
Syntax:
|
constraint_identifier |
A valid identifier for a constraint conforming to the rules described in the section Identifiers. |
|
UNIQUE |
Only one UNIQUE constraint is allowed. |
|
PRIMARY KEY |
Only one PRIMARY KEY is allowed in a table. |
|
NULL/NOT NULL |
Indicates whether NULL values are permissible for this column. |
|
reference_clause |
Only allowed for subtables. The number of columns allowed in this particular case is one. For syntax regulations refer to Table Constraint Element. |
For more restrictions, see CREATE CLUSTER and CREATE CLUSTER DESCRIPTION .
A constraint is a base table sub-object that ensures actual data compliance with the specified conditions.
Adabas SQL Gateway Embedded SQL supports four different types of constraints:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
A simple column constraint can be defined within a table column element. If a constraint refers to more than one column, it must be defined by a table constraint element. The constraint name (constraint identifier) must be unique within the schema. It will be generated automatically, if not specified.
UNIQUE and PRIMARY KEY constraints are called `unique constraints'. A REFERENCES constraint is called `referential constraint'.
The following conventions hold true for the following explanations:
Let C be the column for which this constraint is specified.
Let T be the table where column C resides.
UNIQUE:
A table can have many UNIQUE keys.
The UNIQUE constraint ensures that no two rows of T have the same value in column C. Rows with NULL values in column C do not affect this constraint.
PRIMARY KEY:
A table can have only one PRIMARY KEY.
The PRIMARY KEY constraint ensures that no two rows of T carry the same value in column C. When specifying a PRIMARY KEY constraint without an explicit NOT NULL constraint, an implicit one is generated.
NULL:
The NULL constraint indicate that you can have null values in any row of the table for the column C.
NOT NULL:
The NOT NULL constraint indicates that cannot have null values in any row of the table for the column C.
REFERENCES:
For details on how to define the reference clause, see Table Constraint Element (especially the FOREIGN KEY section). The number of columns allowed in this particular case is one.
The CREATE CLUSTER and CREATE CLUSTER DESCRIPTION statements have the following restrictions:
A Column level REFERENCES constraint may only be used to build the referential constraint between tables of level 0 (base tables) and tables of level 1 (subtables).
Only use the REFERENCES clause in the CREATE CLUSTER/ CREATE CLUSTER DESCRIPTION statements.
There may be a maximum of one PRIMARY KEY for a bases table (this includes a table constraint of type PRIMARY KEY).
Do not use the SUPPRESSION attribute with a PRIMARY KEY constraint.
Do not use the NOT NULL attribute with a UNIQUE constraint
Do not use the NULL attribute with a UNIQUE or PRIMARY KEY constraint.
The default referential triggered action differs from the ANSI standard. The default is CASCADE and not NO ACTION.
The NULL constraint is not part of the Standard.
Only the CASCADE option is supported.
Example:
The following example defines a column constraint which disallows NULL values and values which are not unique:
CREATE TABLE contract (
contract_id integer NOT NULL UNIQUE );