SQL ALTER TABLE

The ALTER TABLE statement alters the definition of a table by modifying, adding, or dropping columns and constraints. The ALTER TABLE statements can be of two types:

• Those that do not change the table layout format but simply use the RENAME operation.
• Those that change the table format. These operations normally take a relatively long time, as the database runtime creates a new table and moves all data from the original table into the newly created one.

The syntax is defined as follows:

    ALTER TABLE name action { , action }

    action:
        RENAME TO new_table_name
        | RENAME [ COLUMN ] old_column_name TO new_column_name
        | ADD [ COLUMN ] column_name column_spec
        | ADD CONSTRAINT constraint_name table_constraint
        | DROP [ COLUMN ] column_name
        | DROP CONSTRAINT constraint_name
        | ALTER [ COLUMN ] column_name TYPE column_spec
        | SET [ NOT ] TEMPORARY

    table_constraint:
        PRIMARY KEY ( column_name [ , ... ] )
        | FOREIGN KEY ( column_name [ , ... ] ) REFERENCES table_name
        | UNIQUE ( column_name [ , ... ] )

    column_spec:
        data_type [ column_constraint ]

    column_constraint:
        PRIMARY KEY
        | UNIQUE
        | USING [ HASH | RTREE | TRIGRAM | INCLUSIVE | BTREE | HNSW ] INDEX
        | [ FOREIGN KEY ] REFERENCES table_name
        | [ NOT ] NULL

    references:
        REFERENCES table_name [ ( column_name [ , ... ] ) ]

Note that the REFERENCES clause notifies the SQL engine of a FOREIGN KEY constraint. The referential integrity is not enforced by the SQL engine.

Also, note that all SQL keywords are case insensitive in eXtremeSQL - i.e., ALTER TABLE and alter table are equivalent.

Adjusting Dictionary Memory Space

Note that when tables are dropped or altered, the database engine does not completely remove all traces of the modified tables from the dictionary. The remaining memory footprint is rather small (~50 bytes remain in the "dictionary" memory area, controlled by the ddl_dict_size parameter). If it is anticipated that ALTER TABLE or DROP TABLE operations will be frequent, or if an application dynamically creates many non-BTree indexes (e.g., hash indexes), it is advisable to reserve extra space for the dictionary. For example, when using xSQL, adjust the configuration file option db_params:additional_heap_size.

Warning for Non-SQL Applications

For applications built with a static schema that has been changed either through ALTER TABLE or otherwise, any attempt to open a transaction with a persistent database through any API other than SQL must be avoided. If the "pre-modified" schema is used, the database runtime will normally preserve the consistency of the database. If static schema modifications are detected, the transaction returns the MCO_E_SCHEMA_CHANGED error code. However, it is possible that the application could read incorrect or stale data. In order to use the new schema from non-SQL applications, the new schema must be saved and reprocessed or reloaded, depending on the API used by the non-SQL application. (See the topic Changing the Database Schema for further details.)