ADD Definition (add_definition) 

You can define additional table properties by specifying an ADD definition (add_definition) in the ALTER TABLE statement.

Syntax

<add_definition> ::= ADD <column_definition>,...
| ADD (<column_definition>,...)
| ADD <constraint_definition>
| ADD <key_definition>
| ADD <referential_constraint_definition>

Examples

SQL Tutorial, Tables, Primary Key, Constraints, Foreign Key Dependencies Between Tables

Adding a Column Definition: ADD <column_definition>

By specifying column definitions (column_definition), you can extend the table specified in the ALTER TABLE statement to include these columns. These specifications must not exceed the maximum number of columns allowed and the maximum length of a row.

The memory requirements for each column are increased by one character (for normal memory requirements, see Memory requirements of a column value as a function of the data type), if the length described is less than 31 characters and the column does not have the data type VARCHAR.

All the rows of the newly defined columns contain the NULL value if a default value has not been defined by a DEFAULT specification for these columns. If the NULL value impairs a CONSTRAINT definition of the table, the ALTER TABLE statement fails.

If you insert LONG columns, you cannot specify a DEFAULT specification. If you want to define a default value for a LONG column, you can define the LONG column using ADD definition and then use the MODIFY definition to define a default value.

In every other respect, specifying a column definition has the same effect as specifying a column definition in a CREATE TABLE statement.

If view tables are defined for the specified table, and if alias names are defined for one of these view tables, and if the view tables reference the columns in the table with *, the ALTER TABLE statement will fail.

●     If view tables are defined for the specified table, and if no alias names are defined, and if the view tables reference the columns in the table with *, this view table contains the columns added to the base table by the ADD definition.

Adding a CONSTANT Definition: ADD <constant definition>

All the rows in the table must fulfill the condition defined by the search condition of the CONSTRAINT definition.

Adding a Key Definition: ADD <key definition>

A key is defined for the table specified in the ALTER TABLE statement. At execution time, the table must only contain the key column SYSKEY generated by the database system. The columns specified in the key definition must be columns in the table and fulfill the key properties (none of the columns may contain NULL values; no two rows may contain the same values in all of the columns defined in the key definition). The new key is stored in the table’s metadata. The key column SYSKEY is omitted. This is an extremely lengthy procedure for tables with a large number of rows, since extensive copy operations are carried out.

If a primary key is defined for the table specified in the ALTER TABLE statement and this key has exactly the same structure as an existing index, this index (the existing index) is deleted.

Inserting a Referential CONSTRAINT Definition: ADD <referential_constraint_definition>

An integrity condition is defined for the table specified in the ALTER TABLE statement. The columns specified in the referential CONSTRAINT definition must be columns in the table. All of the rows in the table must satisfy the integrity condition defined by the referential CONSTRAINT definition.