CONNX Data Integration Suite 14.8.0 | Reference Guide | SQL Grammar | SQL Language Elements | Table Elemenets Exclusive to Adabas | Table Constraint Element
 
Table Constraint Element
Function
A table constraint specifies a constraint for a list of columns.
Invocation
This element is part of the table element.
Syntax
The syntax is CONSTRAINT constraint_identifier, followed by UNIQUE (column_identifier) with optional SHORTNAME clause. Instead of UNIQUE, you can use PRIMARYKEY with (column_identifier) with optional SHORTNAME clause. You can also use FOREIGN KEY (column_identifier) PREFERENCES clause.
Where constraint_identifier is a valid name for a constraint and must conform to the rules about identifiers specified in SQL Tokens, Identifiers, Delimiters.
Syntax for the SHORTNAME clause:
SHORTNAME [shortname_identifier | shortname_string]
Syntax for the REFERENCES clause:
REFERENCES table_specification [(column_identifier [,]) ]
where:
table_specification
Schema identifier. Table identifier is the expected format.
column_identifier
Optional. List of referenced columns.
Description
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.
*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".
*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.
Limitations
*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.
ANSI Specifics
Under ANSI, the columns of a UNIQUE constraint must have the attribute NOT NULL specified.