Creating SQL Objects › Creating a Table › Key Creation

Key Creation

You can create CA Datacom Datadictionary KEY entity-occurrences in several ways with parameters in your SQL statements and by using the CREATE INDEX statement. See CREATE TABLE and Creating an Index for details. For information about specifying an SQL key selection override key in either the correlation name or synonym name, see Overriding SQL Key Selection.

The following are four of the ways KEY entity-occurrences can be defined by CA Datacom Datadictionary as a result of SQL processing. The key SQL name is created by CA Datacom Datadictionary. The name consists of the DATACOM-NAME attribute-value of the key followed by the underscore character followed by the concatenation of the DATACOM-NAME attribute-value of the table and the DATACOM-ID attribute-value of the database containing the key. For example, a key with DATACOM-NAME SQ032 in table INV in database 16 has the SQLNAME attribute-value SQ032_INV00016.

• The first column you specify in the CREATE TABLE statement becomes the CA Datacom/DB Master and Native Key for the table except when a primary or unique key has been defined. CA Datacom Datadictionary automatically generates this KEY entity-occurrence and gives it the same name as the CA Datacom/DB five-character name it generates (SQnnn) and SQLNAME=SQLKEY.

  Note: The DUPE-MASTER-KEY and CHNG-MASTER-KEY attributes of the TABLE entity-occurrence are set to Y, indicating that the value of the Master Key can be duplicated and/or changed.

• A foreign key can be generated. Its name is the same as the constraint name. A physical key will not be generated in the Directory.
• If you use the column-level UNIQUE constraint (after an individual column name), a KEY entity-occurrence is generated. The CA Datacom Datadictionary name for the key is the same as the CA Datacom/DB five-character name it generates (SQnnn).

  Note: Uniqueness is enforced at the key level, not at the column level, that is to say, UNIQUE forces unique values for the entire key and not for the individual columns making up the key.

• If you use the table-level UNIQUE constraint or primary key (followed by a list of one or more column names enclosed in parentheses), a KEY entity-occurrence is generated. The CA Datacom Datadictionary name of the key is the same as the CA Datacom/DB five-character name it generates (SQnnn).

  Note: Uniqueness is enforced at the key level, not at the column level, that is to say, UNIQUE forces unique values for the entire key and not for the individual columns making up the key.

The five-character DATACOM name of each key uses SQ followed by three digits for uniqueness.