CREATE CONSTRAINT

The CREATE CONSTRAINT data description statement defines a referential constraint in the dictionary. A referential constraint establishes a relationship between two tables.
idmscu19
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.
This article describes the following information:
2
2
Authorization
To issue a CREATE CONSTRAINT statement, you must do the following:
  • 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
The following diagram shows the CREATE CONSTRAINT 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
The following diagram shows the syntax for the Expansion of linked-constraint-options:
  ►►─┬─ CLUSTERED ──────────────────────────────────────────────────┬───────────►    └─ INDEX ─┬──────────────────┬─┬─────────────────────────────┬─┘              ├─ COMPRESSED ─────┤ └─ 
index-block-specification
 ─┘              └─ UNCOMPRESSED ◄ ─┘  ►─┬─────────────────────────────────────────────────────────────┬────────────►◄    │              ┌──────────── , ────────────┐                  │    └─ ORDER BY ( ─▼─ 
sort-column
 ─┬─────────┬─┴─ ) ─┬──────────┬─┘                                   ├─ ASC ◄ ─┤       └─ UNIQUE ─┘                                   └─ DESC  ─┘  
Expansion of index-block-specification
 
The following diagram shows the syntax for the Expansion of index-block-specification:
  ►►─── INDEX BLOCK CONTAINS 
key-count
 KEYs ────────────────────────────────────►  ►─┬─────────────────────────────────────┬────────────────────────────────────►◄    └─ DISPLACEMENT IS 
page-count
 PAGES ──┘  
Parameters
Following is a list of the CREATE CONSTRAINT syntax parameters:
  • constraint-name
    The name of the referential constraint being created.
    Constraint-name
    must be a 1- to 18-character name that follows the conventions for SQL identifiers.
    Constraint-name
    must be unique for the schema of the referencing table and cannot share a name with an index on the schema.
  • referencing-table
    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, note the following about
    referencing-table
    :
    • It cannot have a CALC key or clustered index defined on it
    • It cannot be the referencing table in another clustered constraint
    If you specify LINKED in the CREATE CONSTRAINT statement, note the following about
    referencing-table
    :
    • It must be empty
    • It must not be the same table as
      referenced-table
  • schema-name
    The schema associated with the referencing table.
    If you do not specify
    schema-name
    , it defaults to the following:
    • 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
    )
    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 to 32 columns in a foreign key.
  • REFERENCES
    referenced-table
    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, note the following about
    referenced-table
    :
    • It must be empty
    • It must not be the same table as
      referencing-table
  • schema-name
    The schema associated with the referenced table.
    If you do not specify
    schema-name
    , it defaults to the following:
    • 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
    )
    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
    Additional characteristics of a linked constraint. Expanded syntax for
    linked-constraint-options
    is shown immediately following the CREATE CONSTRAINT syntax, above.
  • 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.
    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 that 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
Following is a list of parameters for the 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.
    The default is UNCOMPRESSED, when you do not specify COMPRESSED or UNCOMPRESSED.
  • index-block-specification
    Establishes characteristics of the index created between the referenced and referencing tables.
    The 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 the following ascending or descending order:
    • The first column specified
    • The second column specified within the ordering established by the first column
    • 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
Following is a list of the parameters for Expansion of index-block-specification:
  • key-count
    KEYs
    The maximum number of entries in each internal index record (SR8 system record).
    Key-count
    must be an unsigned integer in the range 3 to 8130.
    The default is 10, if you do not specify KEYS.
  • 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 to 32767.
    If
    index-block-specification
    is omitted, the value of
    page-count
    is zero.
    If the value of
    page-count
    is zero, 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 cannot 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