ALTER TABLE

Modify the structure of an existing table.

Syntax

ALTER [ONLINE] TABLE tbl_name
  [alter_specification [, alter_specification] ...]

alter_specification:
    ADD [COLUMN] col_name { column_definition | AS computed_column_definition } [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition, ...)
  | ADD [UNIQUE] { INDEX | KEY } [index_name] [index_type] (index_col_name, ...) [index_option] ...
  | DROP [COLUMN] col_name
  | DROP { INDEX | KEY } index_name
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | CHANGE old_col_name new_col_name
  | RENAME [TO | AS] new_tbl_name
  | MODIFY { INDEX | KEY } index_name SET ([index_kv_option], ...)
  | { ENABLE | DISABLE } AUTOSTATS

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [COMMENT 'string']

computed_column_definition:
    computed_column_expression PERSISTED data_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING { BTREE | HASH }

index_option:
    KEY_BLOCK_SIZE [=] value
  | BUCKET_COUNT [=] value
  | index_type
  | WITH ([index_kv_option], ...)
  | UNENFORCED [RELY | NORELY]

index_kv_option:
    RESOLUTION = value
  | COLUMNSTORE_SEGMENT_ROWS = value
  | COLUMNSTORE_FLUSH_BYTES = value

Remarks

Info

Successful completion of a new ALTER TABLE will invalidate the plancache for all queries that operate on the affected table, requiring their recompilation.

Examples

The following example creates a table with three columns, then alters the table to add a fourth column called middle_initial.

memsql> CREATE TABLE customer(
    ->   first_name VARCHAR(30),
    ->   last_name VARCHAR(30),
    ->   birth_date DATE
    -> );


memsql> INSERT INTO customer VALUES
    ->   ("Joe", "Williams", "1960/1/2"),
    ->   ("Esther", "Johnson", "1962/7/19");


memsql> SELECT * FROM customer;
+------------+-----------+------------+
| first_name | last_name | birth_date |
+------------+-----------+------------+
| Esther     | Johnson   | 1962-07-19 |
| Joe        | Williams  | 1960-01-02 |
+------------+-----------+------------+


memsql> ALTER TABLE customer ADD column middle_initial CHAR(1);


memsql> SELECT * FROM customer;
+------------+-----------+------------+----------------+
| first_name | last_name | birth_date | middle_initial |
+------------+-----------+------------+----------------+
| Esther     | Johnson   | 1962-07-19 | NULL           |
| Joe        | Williams  | 1960-01-02 | NULL           |
+------------+-----------+------------+----------------+


memsql> UPDATE customer SET middle_initial = "T" WHERE first_name = "Esther";


memsql> SELECT * FROM customer;
+------------+-----------+------------+----------------+
| first_name | last_name | birth_date | middle_initial |
+------------+-----------+------------+----------------+
| Esther     | Johnson   | 1962-07-19 | T              |
| Joe        | Williams  | 1960-01-02 | NULL           |
+------------+-----------+------------+----------------+

The following example changes the middle_initial column to a middle_name column.

memsql> ALTER TABLE customer change middle_initial middle_name;


memsql> ALTER TABLE customer modify middle_name VARCHAR(30);


memsql> SELECT * FROM customer;
+------------+-----------+------------+-------------+
| first_name | last_name | birth_date | middle_name |
+------------+-----------+------------+-------------+
| Esther     | Johnson   | 1962-07-19 | T           |
| Joe        | Williams  | 1960-01-02 | NULL        |
+------------+-----------+------------+-------------+


memsql> UPDATE customer SET middle_name = "Wallace" WHERE last_name = "Williams";


memsql> SELECT * FROM customer;
+------------+-----------+------------+-------------+
| first_name | last_name | birth_date | middle_name |
+------------+-----------+------------+-------------+
| Esther     | Johnson   | 1962-07-19 | T           |
| Joe        | Williams  | 1960-01-02 | Wallace     |
+------------+-----------+------------+-------------+

Was this article useful?