Results of Using ALTER TABLE

The following table describes how using the SQL ALTER TABLE statement impacts the CA Datacom Datadictionary definition.

ALTER TABLE table-name

Updates the definition of the specified table.

column-name

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

AFTER= $LAST or the SQL name of the column it follows.
AGR-SQLNAME= The SQL name of the table.
AUTHID= The schema (AUTHID) of the table.
CLASS=S
ENTITY-NAME = The column-name specified in the statement (the same name as used for SQLNAME).
LENGTH= The length of the column.
PARENT=START
SQLNAME= The column-name specified in the statement (the same name as used for ENTITY-NAME).

Additional attributes are defined based on the column definition syntax.

The column must be added to the end of the table.

datatype

Defines the column's data type through the following FIELD entity-occurrence attributes:

SEMANTIC-TYPE= If the column's data type is DATE, TIME, or TIMESTAMP, this attribute reflects 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

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 SYSTEM USER

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

DEFAULT NULL

For the FIELD entity-occurrence, leaves the NULL-INDICATOR attribute unchanged. Removes a previously specified numeric or literal default value, but does not alter the nullability (NULL-INDICATOR attribute) of a column.

NOT NULL

For the FIELD entity-occurrence, sets the NULL-INDICATOR attribute to N. NOT NULL can be used when adding a column, but not when modifying a column.

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 (AUTHID) 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=N if not already Y
MAX-KEY-LENGTH=0
NATIVE-KEY=N if not already 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 (AUTHID) 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

You cannot copy over or delete a table or any part of a table while it has a constraint.

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 (AUTHID) 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=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 (AUTHID) 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

WITH DEFAULT

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

REFERENCES table-name

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.

CHECK (search condition)

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

AUTHID= The schema (AUTHID) 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 supplied in this parameter.

ADD table constraint

Modifies the table to add one of the constraints that follows to the table.

FOREIGN KEY (column-list)

Defines a key consisting of this column or columns and a constraint. The KEY entity-occurrence has the following attributes:

AGR-SQLNAME= The SQL name of the table.
AUTHID= The schema (AUTHID) 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
MAX-KEY-LENGTH=00
NATIVE-KEY=N
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 (AUTHID) 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:

If all tables referred to by the foreign key are in the same base, CNS-SAME-BASE=Y; otherwise, CNS-SAME-BASE=N.
CNS-REFERS=Y
CONSTRAINT=Y

Sets the referenced TABLE entity-occurrence CNS-REFERENCED= and CONSTRAINT= attributes to 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.

CHECK (search condition)

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

AUTHID= The schema (AUTHID) 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.

CONSTRAINT constraint-name

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

DROP CONSTRAINT constraint-name

Obsoletes the CONSTRAINT entity-occurrence specified. The KEY entity-occurrence remains. Views that reference the constraint's table are marked invalid.

You can drop the foreign key only if there are no primary keys that reference it. Views based on this table are marked invalid.

DROP column-name

Obsoletes the column. All plans, views, and synonyms that reference the column are marked invalid.

DROP PRIMARY KEY

Obsoletes the CONSTRAINT entity-occurrence. The KEY entity-occurrence remains. The DROP PRIMARY KEY statement is successful only if there are no foreign keys that reference this primary key. Views based on the table associated with the constraint are marked as invalid.

DROP FOREIGN KEY identifier

Obsoletes the KEY entity-occurrence created as the foreign key and the CONSTRAINT entity-occurrence.

Views based on the table associated with the obsoleted key are marked as invalid.

MODIFY column-name DEFAULT literal

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

MODIFY column-name DEFAULT USER

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

MODIFY column-name DEFAULT SYSTEM USER

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

MODIFY column-name DEFAULT NULL

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

MODIFY column-name data type

Modifies the column's data type changing the following FIELD entity-occurrence attributes:

SEMANTIC-TYPE= If the column's data type is changed from DATE, TIME, or TIMESTAMP to another data type, this attribute reflects blanks.
SIGN= N if character, Y if numeric.
TYPE= becomes the new data type you specify.
TYPE-NUMERIC= conventional.

All plans and views that reference the column are marked invalid.

RENAME column-name TO column-name

For the FIELD entity-occurrence, changes the SQLNAME to the name specified in the RENAME statement. All plans, views, and synonyms that reference the column are marked invalid. The ENTITY-NAME attribute is not changed from its current value in CA Datacom Datadictionary.