CREATE CONSTRAINT

The CREATE CONSTRAINT data description statement defines a referential constraint in the dictionary. A referential constraint establishes a relationship between two tables.

Using the CREATE CONSTRAINT statement, you can also specify how the constraint is implemented physically. It is also a CA IDMS extension of the SQL Standard.

Authorization

To issue a CREATE CONSTRAINT statement, you must:

Either hold the ALTER privilege on or own the referencing table in the constraint being defined
Hold the REFERENCES privilege on the referenced table in the constraint being defined

Syntax

►►─── CREATE CONSTRAINT constraint-name ──────────────────────────────────────►

                                            ┌────────── , ─────────┐
 ►─┬────────────────┬─ referencing-table ( ─▼─ foreign-key-column ─┴─ ) ──────►
   └─ schema-name. ─┘

 ►─── REFERENCES ─┬────────────────┬─ referenced-table ───────────────────────►
                  └─ schema-name. ─┘

         ┌───────── , ─────────┐
 ►─── ( ─▼─ referenced-column ─┴─ ) ──────────────────────────────────────────►

 ►─┬────────────────────────────────────┬─────────────────────────────────────►◄
   ├─ LINKED linked-constraint-options ─┤
   └─ UNLINKED ◄──┬─────────────┬───────┘
                  └─ CLUSTERED ─┘

Expansion of linked-constraint-options

►►─┬─ CLUSTERED ──────────────────────────────────────────────────┬───────────►
   └─ INDEX ─┬──────────────────┬─┬─────────────────────────────┬─┘
             ├─ COMPRESSED ─────┤ └─ index-block-specification ─┘
             └─ UNCOMPRESSED ◄ ─┘

 ►─┬─────────────────────────────────────────────────────────────┬────────────►◄
   │              ┌──────────── , ────────────┐                  │
   └─ ORDER BY ( ─▼─ sort-column ─┬─────────┬─┴─ ) ─┬──────────┬─┘
                                  ├─ ASC ◄ ─┤       └─ UNIQUE ─┘
                                  └─ DESC  ─┘

Expansion of index-block-specification

►►─── INDEX BLOCK CONTAINS key-count KEYs ────────────────────────────────────►

 ►─┬─────────────────────────────────────┬────────────────────────────────────►◄
   └─ DISPLACEMENT IS page-count PAGES ──┘

Parameters

constraint-name

Specifies the name of the referential constraint being created. Constraint-name must be a 1- through 18-character name that follows the conventions for SQL identifiers.

Constraint-name must be unique for the schema of the referencing table.

referencing-table

Specifies the referencing table in the constraint. Referencing-table must identify a base table defined in the dictionary.

If you specify CLUSTERED in the CREATE CONSTRAINT statement, referencing-table:

Cannot have a CALC key or clustered index defined on it
Cannot be the referencing table in another clustered constraint

If you specify LINKED in the CREATE CONSTRAINT statement, referencing-table:

Must be empty
Must not be the same table as referenced-table

schema-name

Identifies the schema associated with the referencing table.

If you do not specify schema-name, it defaults to:

The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
The schema associated with the access module used at runtime, if the statement is embedded in an application program

(foreign-key-column)

Specifies one or more columns that make up the foreign key in the referencing table. Foreign-key-column must identify a column in the referencing table and must be unique within the list of column names.

If you specify UNLINKED in a CREATE CONSTRAINT statement (or accept UNLINKED as the default), the foreign key must be a CALC key or an index key, as defined by a CREATE CALC or CREATE INDEX statement.

You can include from 1 through 32 columns in a foreign key.

REFERENCES referenced-table

Specifies the referenced table in the constraint. Referenced-table must identify a base table defined in the dictionary.

If you specify LINKED in a CREATE CONSTRAINT statement, referenced-table:

Must be empty
Must not be the same table as referencing-table

schema-name

Identifies the schema associated with the referenced table.

If you do not specify schema-name, it defaults to:

The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
The schema associated with the access module used at runtime, if the statement is embedded in an application program

(referenced-column)

Specifies one or more non-null columns that make up a unique key in the referenced table, as defined by a CREATE CALC or CREATE INDEX statement.

Referenced-column must identify a column in the referenced table and must be unique within the list of column names. The columns must be named in the CREATE CONSTRAINT statement in the same order in which they are named in the CREATE CALC or CREATE INDEX statement that defines the unique key.

You must specify the same number of referenced columns as the number of columns included in the foreign key of the referencing table. The corresponding referenced and foreign-key columns must have the same data type, length, precision, and scale.

LINKED

Directs CA IDMS to maintain a physical linkage between the rows in the referenced and referencing tables.

linked-constraint-options

Specifies additional characteristics of a linked constraint. Expanded syntax for linked-constraint-options is shown immediately following the CREATE CONSTRAINT syntax.

UNLINKED

Directs CA IDMS not to physically link the referenced and referencing tables.

If you specify UNLINKED, the referencing table must have a CALC key or index defined on the foreign key and the order of columns of the CALC or index key must match the order of columns of the foreign key. The index or CALC key on the foreign key does not have to be unique.

A constraint in which a single table is the referencing table and the referenced table must be unlinked.

UNLINKED is the default when you specify neither LINKED nor UNLINKED.

Note: If you are using an index, it can contain additional columns that are not part of the foreign key. The foreign key columns must precede any additional columns in the index key.

CLUSTERED

Specifies each row of the referencing table is to be stored close to other rows of the referencing table that have the same non-null foreign-key value.

Parameters for Expansion of linked-constraint-options

INDEX

Directs CA IDMS to create an index between the referenced and referencing tables.

COMPRESSED

Directs CA IDMS to maintain index entries in a compressed form in the database.

UNCOMPRESSED

Directs CA IDMS to maintain index entries in an uncompressed form in the database.

UNCOMPRESSED is the default when you specify neither COMPRESSED nor UNCOMPRESSED.

index-block-specification

Establishes characteristics of the index created between the referenced and referencing tables.

Syntax for index-block-specification follows the syntax for linked-constraint-options.

ORDER BY (sort-column)

Specifies one or more columns that make up a sort key for a linked constraint. CA IDMS uses the sort key to determine the order in which the rows of the referencing table are to be linked within the referential constraint. Rows are linked in ascending or descending order, first by the first column specified, then by the second column specified within the ordering established by the first column, then by the third column specified, and so on.

Sort-column must identify a column in the referencing table and must be unique within the list of column names.

If you specify the UNIQUE option of the ORDER BY parameter, each column included in the sort key must be defined as NOT NULL.

You can specify a maximum of 32 sort columns.

ASC

Indicates that values in the named column are to be ordered in ascending sequence. ASC is the default when you specify neither ASC nor DESC.

DESC

Indicates that values in the named column are to be ordered in descending sequence.

UNIQUE

Specifies the sort-key value in any given row of the referencing table must be different from the sort-key value in any other row of the table that has the same non-null foreign-key value. A table with a unique sort key cannot contain duplicate rows.

Parameters for Expansion of index-block-specification

key-count KEYs

Establishes the maximum number of entries in each internal index record (SR8 system record).

Key-count must be an unsigned integer in the range 3 through 8130.

If you do not specify KEYS, key-count defaults to 10.

page-count PAGES

Indicates how far away from the referenced row the bottom-level index records are to be stored.

Page-count must be an unsigned integer in the range 0 through 32767.

If index-block-specification is omitted, the value of page-count is 0.

If the value of page-count is 0, the bottom-level internal index records cannot be displaced from the referenced row with which they are associated.

Usage

System-owned Tables

You cannot define a referential constraint where the referencing table or the referenced table is in the SYSTEM schema.

Specifying a Linked Constraint

A linked constraint (as opposed to an unlinked constraint) is used by the optimizer in determining the most efficient access for an SQL DML statement. It does not affect either the syntax or the semantics of the statement.

Dropping Tables

When you define a referential constraint, you restrict the conditions under which tables can be dropped.

Mixed Page Group

A constraint defined as linked clustered can not span page groups. The referencing and referenced tables of a constraint defined as linked clustered must be in the same page group.

Examples

Defining a self-referencing Constraint

The following CREATE CONSTRAINT statement defines a referential constraint in which the EMPLOYEE table is the referencing and the referenced table. This constraint directs CA IDMS to ensure that the value in the MANAGER_ID column in each row of the EMPLOYEE table matches the value in the EMP_ID column in another row of the table. By default, the constraint is unlinked. (Self-referencing constraints must be unlinked.)

create constraint manager_emp
   employee
      (manager_id)
   references employee
      (emp_id);

Defining a Linked Constraint

The following CREATE CONSTRAINT statement defines a referential constraint between the BENEFITS table and the EMPLOYEE table. This constraint directs CA IDMS to ensure that the value in the EMP_ID column in each row of the BENEFITS table matches the value in the EMP_ID column in a row of the EMPLOYEE table. The referential constraint is implemented with a linked index, with the index entries sorted in descending order by the value in the FISCAL_YEAR column.

create constraint emp_benefits
   benefits
      (emp_id)
   references employee
      (emp_id)
   linked index
      order by (fiscal_year desc);

More Information

For more information about dropping referential constraints, see DROP CONSTRAINT.
For more information about defining CALC keys, see CREATE CALC.
For more information about defining indexes, see CREATE INDEX.
For more information about implementing referential constraints, see the CA IDMS Database Design Guide.
For more information about dropping tables, see DROP TABLE.