SQL Statements › ALTER TABLE › Description

Description

table-name

Specify the name of the table you want to alter. The table-name must identify a table described in the CA Datacom Datadictionary, but not a CA Datacom Datadictionary table.

ADD

Allows you to add column definitions or table constraint definitions.

Important! We recommend that you do not add constraints to an open table. If a constraint (CHECK, FOREIGN KEY, or PRIMARY KEY) is added to an open table that previously had no constraints, the added constraint is not enforced in programs that opened the table before the execution of the ALTER TABLE statement that added the constraint. To activate constraint enforcement for a constraint added to an open table, the program must close and reopen the table..(This caution only applies to record-at-a-time programs referencing tables to which the constraint was added.)

When you use the ALTER TABLE statement to add domain or foreign key constraint(s) to a table, they are added to the Schema Information Tables (SIT) (see Schema Information Tables (SIT)) in check pending status. The CONFIRM function of DBUTLTY is then called to validate existing data against the added constraint(s). Previous existing foreign key(s) are not revalidated. If a violation is found, CA Datacom/DB issues an SQL warning message and changes the table's Directory (CXX) status to prevent access to the table's rows.

Note: You must CONFIRM tables in the following order, from referenced-to-referencing, or parent-to-child, that is to say, the referenced table cannot be in check pending status. For example, if there is a foreign key from a REMARKS table to a LINE_ITEM table on PO_Nbr and Line_Item_Nbr, and the LINE_ITEM table has a foreign key that references the PO table on PO_Nbr, the PO table must be loaded first, then the LINE_ITEM table can be confirmed, then the REMARKS table can be confirmed. Therefore, the confirm order would be: from PO table to LINE_ITEM table to REMARKS table.

If violations are found, with the table in check pending status you can do one of two things:

1. You can execute the CONFIRM function of DBUTLTY to delete the row(s) in error and optionally write them to an exception table for further action. To identify the violated constraint(s), after writing the row(s) in error to an exception table you can write a program to read those row(s) and attempt to insert them back into the original table so that the violated constraint(s) are reported.
2. Or you can use the ALTER TABLE statement to drop the violated constraint(s), causing the table to be reconfirmed and taken out of check pending status. If all the added constraints are dropped, the CONFIRM function of DBUTLTY notes that there are no constraints in check pending status in the SIT and resets the table's Directory (CXX) status to allow access to the table's rows.

If you try to add a new constraint other than domain or foreign key constraint(s) previously described and there is data already in the table that violates the constraint, CA Datacom/DB issues an error message. You can correct the error by either changing the constraint definition or removing the data from the table.

column definition

When a new column is added (through a column definition), rows that already exist in the table receive NULL values in the added column unless NOT NULL WITH DEFAULT is specified in the column constraint definition. If NOT NULL WITH DEFAULT is specified, they receive zero (0) for NUMERIC data types, blanks for fixed-length strings, 1/1/1 for dates, 0:00:00 for times, and 1/1/1-0:00:00.000000 for timestamps.

The new column is added at the end of the row.

When a table is created through SQL, an element with the name SQLEL is added spanning the full row. If the SQLEL element exists, the length of this new column is added. If the SQLEL element does not exist, it is added.

See Column Definition for information about the column definition and Column Constraint Definition for information about the column constraint definition.

Note: When you add a column definition, you have the option of specifying UNSIGNED following the data type specification.

table constraint definition

See Table Constraint Definition.

DROP

Allows you to drop CONSTRAINTs, columns, PRIMARY KEYs, or FOREIGN KEYs. If you try to drop a constraint by name during an ALTER TABLE statement and the specified constraint name is not found, CA Datacom/DB issues an error message. You can query the SYSCONSTRSRC and SYSCONSTRDEP tables (see Schema Information Tables (SIT)) to determine the correct name of the constraint.

You cannot drop a primary or unique constraint if there is one or more foreign references to it. In such cases, CA Datacom/DB issues an error message. You can query the SYSCONSTRSRC and SYSCONSTRDEP tables (see Schema Information Tables (SIT)) to locate the foreign reference. You can then remove all foreign references before dropping the primary or unique constraint.

Note: When a PRIMARY KEY or UNIQUE constraint is dropped, the corresponding index (key) remains but is made nonunique, even if the key was originally created using CA Datacom Datadictionary.

You cannot drop a WITH CHECK OPTION constraint from a view definition by using the ALTER TABLE statement. To drop a WITH CHECK OPTION constraint from a view definition, use the DROP statement to drop the view definition, then use the CREATE VIEW statement to re-create the view definition without the WITH CHECK OPTION constraint.

Note: Dropping the view and re-creating it requires the rebinding of all dependent statements and views. You also have to re-create any views which were dependent on the dropped view.

A column may not be dropped if it is:

• The only column in any element,
• The only column of a key, or
• Involved in any constraint, that is to say, a FOREIGN KEY, PRIMARY KEY, UNIQUE, or CHECK constraint. If you attempt to drop a column involved in any constraint, you receive an SQL -242 return code (CONFLICT ALTERING COLUMN).

DROP does not process and you receive a -118 return code when the CA Datacom Datadictionary entity-occurrence definition of the table or view you specify is protected with a password or a Lock Level 1 or 2. The error message also includes a Datadictionary Service Facility (DSF) return code. The DSF return codes are:

• IPW (for password protected)
• IOR (for Lock Level 1 protected)
• NTF (for Lock Level 2 protected)

See Deleting SQL Objects for more information, and see the CA Datacom Datadictionary documentation for information on passwords and lock levels.

CONSTRAINT constraint-name

Specifies the CONSTRAINT to be dropped. See Column Constraint Definition for information about the CONSTRAINT constraint-name.

column-name

Specifies the name of a column. The column must belong to the table you have specified with the table-name.

PRIMARY KEY

Specifies that the PRIMARY KEY constraint for a table name is to be dropped. See Column Constraint Definition for information about the PRIMARY KEY.

FOREIGN KEY constraint-name

Specifies the FOREIGN KEY to be dropped. See Referential Constraint Definition for information about the FOREIGN KEY.

MODIFY

Allows you to modify a column's DEFAULT or data type.

You cannot modify a column that is involved in any constraint, that is to say, a FOREIGN KEY, PRIMARY KEY, UNIQUE, or CHECK constraint. If you attempt to modify a column that is involved in any constraint, you receive an SQL -242 return code (CONFLICT ALTERING COLUMN).

DEFAULT

Allows you to specify a default.

literal

Specifies a literal as the default. The literal you specify must be consistent with the data type of the specified column.

A user-supplied DEFAULT literal can be up to 20 bytes long, or the length of the column involved, whichever is shorter. A default value may be specified for a character column where the column is greater than 20 bytes long, but the default literal itself is limited to 20 bytes, with the remaining bytes padded with blanks by the system.

NULL

Specifies a NULL value as the default.

USER

Specifies the current authorization ID as the default.

SYSTEM USER

This CA Datacom/DB extension specifies the accessor ID of the currently signed-on user as the default.

datatype

A column's data type may be changed with the following rules:

• DATE, TIME, TIMESTAMP: these data types may not be modified.
• Changing the length of a character field results in padding with blanks or truncation. You receive an error message if truncation would delete non-blank data.

  Important! All data types that are known to CA Datacom Datadictionary as special data types but are unknown to SQL are treated by SQL as character. Therefore, if you alter one of these columns, the ALTER uses the character rules.

• Any of the numeric data types may be converted to any other numeric data type (FLOAT, REAL, DOUBLE PRECISION, DECIMAL, SMALLINT, INTEGER, NUMERIC). Conversion of values from the old to the new data type is treated with the same truncation rules as assignment. (See Basic Operations (Assignment and Comparison).)

See Data Types for more information about data types.

RENAME column-name TO column-name

Allows you to rename a column. This changes the SQL name of the column throughout the CA Datacom Datadictionary and SIT tables. It does not correct any external source statements.

You cannot rename a column that is involved in any constraint, that is to say, a FOREIGN KEY, PRIMARY KEY, UNIQUE, or CHECK constraint. If you attempt to rename a column that is involved in any constraint, you receive an SQL -242 return code (CONFLICT ALTERING COLUMN).