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 don't 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 [ COLUMNS ] 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 | 
        UNIQUE "(" column_name [ "," ... ] ")"
 
    column_spec:
        data_type [ column_constraint ]
 
    column_constraint:
        PRIMARY KEY |
        UNIQUE |
        USING [ HASH | RTREE | TRIGRAM | INCLUSIVE | BTREE ] INDEX
        [ FOREIGN KEY ] references
        [ NOT ] NULL
 
    references:
        REFERENCES table [ "(" columns ")" ]
         

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 is rather small (~ 50 bytes remain in the "dictionary" memory area (controlled by the ddl_dict_size parameter). If it is anticipated that the alter table or drop table operations are going to be frequent, or if an application dynamically creates a lot of 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 the 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 preserve the consistency of the database – normally. If the 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 topic Changing the Database Schema for further details.)