Show TOC

Background documentationADD Definition (add_definition)

 

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

Structure

Syntax Syntax

  1. <add_definition> ::=
  ADD <column_definition>,...
| ADD (<column_definition>,...)
| ADD <key_definition>
| ADD <constraint_definition>
| ADD <referential_constraint_definition>
End of the code.
Examples

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

Adding a Column Definition: ADD <column_definition>

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

Note Note

The memory requirements for each column are increased by one character (for normal memory requirements, see Memory Requirements of a Column Value According to Data Type), if the length described is less than 31 characters and the column does not have the data type VARCHAR.

End of the note.

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 LOB columns, you cannot specify a DEFAULT specification. If you want to define a default value for a LOB column, you can define the LOB 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, 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, 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 Key Definition: ADD <key definition>

A key is defined for the table specified in the ALTER TABLE statement. At the time of execution, 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 and 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 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, then this existing index is deleted.

Adding a CONSTRAINT Definition: ADD <constraint definition>

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

Inserting a Referential CONSTRAINT Definition: ADD <referential_constraint_definition>

A constraint (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.