Results of Defining Structures Using SQL Statements › CREATE TABLE Statement

CREATE TABLE Statement

CREATE TABLE table-name

Creates a table, a key, and an element for the entire table. It relates the TABLE entity-occurrence to the AUTHORIZATION entity-occurrence (the schema).

The following are the significant TABLE entity-occurrence attributes defined:

• AUTHID= The schema (authorization identifier) specified with a qualified table-name in the statement or the default schema.
• CHNG-MASTER-KEY=Y
• COMPRESSION=N
• CONSTRAINT=Y if the table or any of the columns has a constraint defined; otherwise, CONSTRAINT=N.
• DATACOM-ID= A 3-digit ID generated by CA Datacom Datadictionary that is unique within the database.
• DATACOM-NAME= An internally generated unique name in the form Bnn, Cnn, or Dnn where nn is 00 to 99, or the first three characters of the SQLNAME, if that is unique.
• DDD-SYNCH=Y
• DUPE-MASTER-KEY=Y
• ENTITY-NAME= An internally generated unique name consisting of the schema (the AUTHID used when creating the table) followed by a hyphen and the name specified in the CREATE statement. The name must be unique. See the Occurrence Names Created from SQL Names section in the CA Datacom Datadictionary User Guide for details on actions taken by CA Datacom Datadictionary to create unique names.
• FIPS= Indicates if the procedure is valid in FIPS processing. Y indicates yes, N indicates no, and S indicates no because of syntax.
• LOGGING=Y
• PIPELINE-OPTION=Y
• RECOVERY=Y
• SQL-INTENT=Y
• SQLNAME= The table-name provided in the CREATE TABLE statement.

The element contains all the columns defined to the table. The following are the significant ELEMENT entity-occurrence attributes defined:

• DATACOM-NAME=SQLEL
• DISP-IN-TABLE=0
• ENTITY-NAME=SQLEL
• FIRST-FIELD= The occurrence-name of the first field in the table.
• LAST-FIELD= The occurrence-name of the last field in the table.
• LENGTH= The length of the table including null indicators for columns defined to accept null values.

The first column named in the CREATE TABLE statement becomes the CA Datacom/DB Master and Native Key for the table except when a primary key is defined for the table. The following are the significant KEY entity-occurrence attributes defined:

• AGR-SQLNAME= The SQL name of the table.
• AUTHID= The schema (authorization ID) of the table.
• DATACOM-NAME= SQ followed by three digits selected by CA Datacom Datadictionary for uniqueness within the database (same name as used for ENTITY-NAME).
• DATACOM-ID= An available 3-digit ID selected by CA Datacom Datadictionary for uniqueness within the database.
• ENTITY-NAME= SQ followed by three digits selected by CA Datacom Datadictionary for uniqueness within the database (same name as used for DATACOM-NAME).
• FOREIGN=N
• INCLUDE-NIL-KEY=Y
• MASTER-KEY=Y unless a primary key is defined, in which case N.
• MAX-KEY-LENGTH=0
• NATIVE-KEY=Y unless a primary key is defined, in which case N.
• SQLNAME= The key's DATACOM-NAME followed by an underscore character followed by the concatenation of the DATACOM-NAME of the table and the DATACOM-ID of the database in which the key is defined (for example, SQ032_INV00016).
• UNIQUE=Y unless a primary key is defined, in which case N.

IN area-name

Relates the TABLE entity-occurrence created to the AREA entity-occurrence named. If you do not include this parameter, the table is placed in the SQL default area (see the CA Datacom/DB Database and System Administration Guide).

CREATE TABLE table-name column-name

Defines a column (FIELD entity-occurrence) in the table. The following are the significant FIELD entity-occurrence attributes defined:

• AFTER=START for first column; otherwise, the ENTITY-NAME of the column it follows.
• AGR-SQLNAME= The SQL name of the table.
• AUTHID= The schema (authorization ID) of the table.
• CLASS=S
• ENTITY-NAME = The column-name specified in the CREATE TABLE statement (the same name as used for SQLNAME).
• LENGTH= The length of the column.
• PARENT=START
• SQLNAME= The column-name specified in the CREATE TABLE statement (the same name as used for ENTITY-NAME).

Additional attributes are defined based on the column definition syntax as follows.

datatype

Defines the column's data type through FIELD entity-occurrence attributes. If the column's data type is DATE, TIME, or TIMESTAMP:

• SEMANTIC-TYPE= This attribute is defined as SQL-DATE, SQL-TIME, or SQL-STMP, respectively.
• SIGN=N
• TYPE=B
  • length 4 for SQL-DATE
  • length 3 for SQL-TIME
  • length 10 for SQL-STMP
• TYPE-NUMERIC=C

FOR BIT DATA

For the FIELD entity-occurrence, specifies SEMANTIC-TYPE=BITDATA (only valid for character, VARCHAR, and LONG VARCHAR data types).

DEFAULT literal

For the FIELD entity-occurrence, defines a literal value in the VALUE attribute and sets the DEFAULT-INSERT attribute to O.

DEFAULT USER

For the FIELD entity-occurrence, sets the DEFAULT-INSERT attribute to U.

DEFAULT NULL

For the FIELD entity-occurrence, sets the NULL-INDICATOR attribute to Y and the DEFAULT-INSERT attribute to N.

DEFAULT SYSTEM USER

For the FIELD entity-occurrence, sets the DEFAULT-INSERT attribute to S.

NOT NULL

For the FIELD entity-occurrence, sets the NULL-INDICATOR attribute to N.

NOT NULL WITH DEFAULT

For the FIELD entity-occurrence, sets the DEFAULT-INSERT attribute to D.

WITH DEFAULT

For the FIELD entity-occurrence, sets the DEFAULT-INSERT attribute to D.

PRIMARY KEY

Creates a key consisting of this column or columns and a constraint. The following are the significant KEY entity-occurrence attributes defined:

• AGR-SQLNAME= The SQL name of the table.
• AUTHID= The schema (authorization ID) of the table.
• DATACOM-ID= An available 3-digit ID selected by CA Datacom Datadictionary for uniqueness within the database.
• DATACOM-NAME= SQ followed by three digits selected by CA Datacom Datadictionary for uniqueness within the database (same name as used for the ENTITY-NAME).
• ENTITY-NAME= SQ followed by three digits selected by CA Datacom Datadictionary for uniqueness within the database (same name as used for the DATACOM-NAME).
• FOREIGN=N
• INCLUDE-NIL-KEY=Y
• MASTER-KEY=Y
• MAX-KEY-LENGTH=0
• NATIVE-KEY=Y
• SQLNAME= The key's DATACOM-NAME followed by an underscore character followed by the concatenation of the DATACOM-NAME of the table and the DATACOM-ID of the database in which the key is defined (for example, SQ032_INV00016).
• UNIQUE=Y

The following are the significant CONSTRAINT entity-occurrence attributes defined:

• AUTHID= The schema (authorization ID) associated with the table.
• ENTITY-NAME= An internally generated unique name consisting of the schema (the AUTHID) used when creating the table followed by a hyphen and the SQLNAME.
• SQLNAME= The name given to the constraint in the statement or an internally created name in the form CONSTRAINT_nnnn where the nnnn are four digits selected for uniqueness within the database.

The TABLE entity-occurrence has the following attributes that pertain to constraints:

• CNS-UNIQUE=Y
• CONSTRAINT=Y

UNIQUE

Defines a key consisting of the identified column or columns and a constraint. The following are the significant KEY entity-occurrence attributes defined:

• AGR-SQLNAME= The SQL name of the table.
• AUTHID= The schema (authorization ID) of the table.
• DATACOM-ID= An available 3-digit ID selected by CA Datacom Datadictionary for uniqueness within the database.
• DATACOM-NAME= SQ followed by three digits for uniqueness within the database (same name as used for the ENTITY-NAME).
• ENTITY-NAME= SQ followed by three digits for uniqueness within the database (same name as used for the DATACOM-NAME).
• FOREIGN=N
• INCLUDE-NIL-KEY=Y
• MASTER-KEY=N if this is not the first unique key of the table and a primary key is not defined; otherwise, MASTER-KEY=Y.
• MAX-KEY-LENGTH=0
• NATIVE-KEY=N if this is not the first unique key of the table and a primary key is not defined; otherwise, NATIVE-KEY=Y.
• SQLNAME= The key's DATACOM-NAME followed by an underscore character followed by the concatenation of the DATACOM-NAME of the table and the DATACOM-ID of the database in which the key is defined (for example, SQ032_INV00016).
• UNIQUE=Y

The following are the significant CONSTRAINT entity-occurrence attributes defined:

• AUTHID= The schema (authorization ID) associated with the table.
• ENTITY-NAME= An internally generated unique name consisting of the schema (the AUTHID used when creating the table) followed by a hyphen and the SQLNAME.
• SQLNAME= The name given to the constraint in the statement or an internally created name in the form CONSTRAINT_nnnn where the nnnn are four digits selected for uniqueness within the database.

The TABLE entity-occurrence has the following attributes that pertain to constraints:

• CNS-UNIQUE=Y
• CONSTRAINT=Y

REFERENCES table-name (column-list)

Sets the TABLE entity-occurrence CNS-REFERS= and CONSTRAINT= attributes to Y for this table.

Sets the referenced TABLE entity-occurrence CNS-REFERENCED= and CONSTRAINT= attributes to Y.

Also establishes a foreign key occurrence (see following page).

CHECK (search condition)

Creates a constraint. The following are the significant CONSTRAINT entity-occurrence attributes defined:

• AUTHID= The schema (authorization ID) associated with the table.
• ENTITY-NAME= An internally generated unique name consisting of the schema (the AUTHID used when creating the table) followed by a hyphen and the SQLNAME.
• SQLNAME= The name given to the constraint in the statement or an internally created name in the form CONSTRAINT_nnnn where the nnnn are four digits selected for uniqueness within the database.

The TABLE entity-occurrence has the following attributes that pertain to constraints:

• CNS-DOMAIN=Y
• CONSTRAINT=Y

CONSTRAINT constraint-name

Sets the CONSTRAINT entity-occurrence SQLNAME attribute to the name specified in the statement.

FOREIGN KEY (column-list)

Creates a key consisting of the identified column or columns and a constraint. The following are the significant KEY entity-occurrence attributes defined:

• AGR-SQLNAME= The SQL name of the table.
• AUTHID= The schema (authorization ID) of the table.
• AGR-SQLNAME= The SQL name of the table.
• AUTHID= The schema (authorization ID) of the table.
• DATACOM-NAME= blanks
• DATACOM-ID=000
• ENTITY-NAME= CA Datacom Datadictionary uses the constraint's ENTITY-NAME value.
• FOREIGN=Y
• INCLUDE-NIL-KEY=Y
• MASTER-KEY=N
• NATIVE-KEY=N
• MAX-KEY-LENGTH=00
• SQLNAME= CA Datacom Datadictionary uses the constraint's SQLNAME value.
• UNIQUE=N

The following are the significant CONSTRAINT entity-occurrence attributes defined:

• AUTHID= The schema (authorization ID) associated with the table.
• ENTITY-NAME= An internally generated unique name consisting of the schema (the AUTHID used when creating the table) followed by a hyphen and the SQLNAME.
• SQLNAME= The name given to the constraint in the statement or an internally created name in the form CONSTRAINT_nnnn where the nnnn are four digits selected for uniqueness within the database.

The TABLE entity-occurrence has the following attributes:

• CNS-SAME-BASE=Y (if all tables to which the foreign key refers are in the same base).
• CNS-REFERS=Y
• CONSTRAINT=Y

Sets the referenced TABLE entity-occurrence CNS-REFERENCED= and CONSTRAINT= attributes to Y.