# 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.)