A table constraint specifies a constraint for a list of columns.
This element is part of the table element.
|
constraint identifier |
A valid name for a constraint and must conform to the rules specified earlier in this section in section Identifiers. |
Shortname clause:
Reference clause:
|
table_specification |
Schema identifier. Table identifier is the expected format. |
|
column_identifier |
Optional. List of referenced columns. |
UNIQUE and PRIMARY KEY constraints are called "unique constraints." A REFERENCES constraint is called "referential constraint." A table constraint element defines a constraint across one or more columns.
The following conventions hold true for the following explanations:
Let CL = ( c1,... , cn ) be the column list of one or more columns for which this table constraint element is specified.
Let T be the table where the columns of CL reside.
PRIMARY KEY: There may only be a maximum of one PRIMARY KEY definition in a base table.
A PRIMARY KEY constraint ensures that there are no two rows of T having identical values in the columns of CL. Each column of CL implies a NOT NULL constraint, that, if not specified, is generated automatically.
PRIMARY KEYs on subtables of level one or two are limited to using all the columns of the associated FOREIGN KEY (the FOREIGN KEY that associates this level one or two table with its parent), plus a column of data type ISN_tablename, cnxarraycolumn and cnxarraycolumn_2 on the current level and any other columns of this level. The important point here is that only PRIMARY KEYs with a column of data type ISN_tablename, cnxarraycolumn and cnxarraycolumn_2 , for this table level, are classified as fulfilling the requirements for building a "unique constraint".
UNIQUE:
A UNIQUE constraint ensures that there are no two rows of T having identical values in the columns of C. Rows with NULL value(s), in any columns of C, do not effect this constraint.
FOREIGN KEY:
If specified, the REFERENCE's column list must conform to the following;
The number of columns in CL and the number of columns in the references column list must be equal.
The ith column of CL must be semantically the same as the ith column of the references list (i.e., the data type and attributes must match). The attributes UNIQUE and PRIMARY KEY should be converted to UQINDEX. The attribute REFERENCES is an exception to this rule.
The columns of the references clause must match those of a "unique constraint" in the referencing table.
All the columns of the "unique constraint" must have the attribute NOT NULL defined.
All columns of Cl must exist in the defining base table, and a column of T may not appear twice within Cl.
The FOREIGN KEY clause may only be used in a CREATE CLUSTER or CREATE CLUSTER DESCRIPTION statement.
The SHORTNAME clauses may only be used in a CREATE CLUSTER DESCRIPTION or CREATE TABLE DESCRIPTION statement.
There may be a maximum of one PRIMARY KEY for a bases table (this includes a column attribute of type PRIMARY KEY).
When using a PRIMARY KEY constraint the attribute SUPPRESSION is not permitted.
The NOT NULL attribute is not permitted when using a UNIQUE constraint in conjunction with a SUPPRESSION attribute.
The NULL attribute is not permitted when using a UNIQUE or PRIMARY KEY constraint in conjunction with a DEFAULT ADABAS attribute.
The columns of a UNIQUE constraint must under ANSI have the attribute NOT NULL specified.
None.