Modify the structure of an existing table.

Syntax

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

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
  | AUTO_INCREMENT [=] new_value

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

• MemSQL supports online ALTER TABLE, which means that you can read and write while the table is being altered. ALTER TABLE on a sharded table is always executed online. Note that online ALTER TABLE will not begin modifying the table, but it will wait until all DML queries that were already running on the table finish. This allows any in-progress queries to complete execution before modifying the structure of the table, and ensures consistency of results from queries on the table since the time of execution of ALTER TABLE. As soon as the in-progress reads and writes complete and the ALTER TABLE command begins modifying the table, new reads and writes will proceed as normal. This blocking period usually lasts on the order of milliseconds.
• If you are running frequent ALTER TABLE statements on a table and have a lot of long-running queries on that table, then your normal workload may experience some periods of delay since it blocks other queries from starting while it waits for completion of long-running queries.
• Online ALTER TABLE in MemSQL cannot be rolled back, so operations that may result in errors are disallowed. For example, you cannot add unique indexes with online ALTER TABLE because there may be duplicate keys in the indexed column.
• ALTER OFFLINE TABLE is supported on REFERENCE tables only. ALTER OFFLINE TABLE operations will cause write queries against the table to fail until the ALTER TABLE completes. Read queries can still be executed against the table as if it were in the state immediately before the ALTER TABLE operation began executing. If the user does not specify whether to do the ALTER TABLE operation online or offline, MemSQL will do it online if possible. Note that offline ALTER TABLE completes more quickly than online, but offline blocks writes for as long as the command is executing.
• ALTER TABLE must be run on the master aggregator node (see Node Requirements for MemSQL Commands).
• Columns used to construct a view cannot be dropped. See CREATE VIEW for more information.
• computed_column_expression defines the value of a computed column using other columns in the table, constants, built-in functions, operators, and combinations thereof. For more information see Persistent Computed Columns.
• On columnstore tables, adding columns, dropping columns, and changing column names are supported, but modifying column types is unsupported.
• Adding or dropping indexes on columnstore tables is unsupported.
• ALTER TABLE tbl_name MODIFY KEY SET (...) modifies configuration variables associated with the index. It is currently only supported on columnstore indexes. See Advanced Columnstore Configuration Options for more information.
• ALTER TABLE tbl_name { ENABLE | DISABLE } AUTOSTATS enables or disables automatic statistics collection on the table. See Automatic Statistics for more information.
• ALTER TABLE tbl_name AUTO_INCREMENT [=] new_value sets the next auto_increment value that will be generated. It can only be used with reference tables and to increase the internal auto_increment counter. If new_value is smaller than the current value, a warning is shown, displaying the current value.
• Altering PRIMARY, SHARD, or UNIQUE keys is unsupported.
• TIMEOUT, a value specified in milliseconds, determines the length of time to wait for the ALTER TABLE command to commit across the cluster. If not specified, the default_distributed_ddl_timeout global variable value is used.

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 Helpful?

Thanks For Your Feedback!