ALTER TABLE
On this page
Modify the structure of an existing table.
Syntax
ALTER [ONLINE] TABLE <table_name>[<alter_specification> [, <alter_specification>] ...] [TIMEOUT <timeout_value>]<alter_specification>:ADD [COLUMN] col_name { column_definition | AS computed_column_definition } [FIRST | AFTER <column_name>]| ADD [COLUMN] (<column_name> <column_definition>, ...)| ADD COLUMN GROUP [column_group_name](*)| ADD [UNIQUE] { INDEX | KEY } [<index_name>] [<index_type>] (<index_column_name>, ...) [<index_option>] ...| ADD FULLTEXT [USING VERSION 1] [<index_name>] (<index_column_name>, ...)| ADD FULLTEXT USING VERSION 2 [<index_name>] (<index_column_name>, ...)| DROP [COLUMN] <column_name>| DROP { INDEX | KEY } <index_name>| DROP COLUMN GROUP <column_name_group>| MODIFY [COLUMN] <column_name> <column_definition>[FIRST | AFTER <column_name>]| MODIFY COLUMN <column_name> <column_definition> COLLATE <collation_name>| CHANGE <old_column_name> <new_column_name>| RENAME [TO | AS] <new_table_name>| MODIFY { INDEX | KEY } <index_name> SET ([<index_kv_option>], ...)| AUTOSTATS_ENABLED = {ON|OFF}| AUTOSTATS_CARDINALITY_MODE = {INCREMENTAL|PERIODIC|OFF}| AUTOSTATS_HISTOGRAM_MODE = {CREATE|UPDATE|OFF}| AUTOSTATS_SAMPLING = {ON|OFF}| AUTO_INCREMENT [=] <new_value>| COMPRESSION [=] { NONE | SPARSE }| BACKGROUND_MERGER = {ON|OFF}<column_definition>:<data_type> [NOT NULL | NULL] [DEFAULT <default_value>][COMMENT '<string>'] [SERIES TIMESTAMP] [SPARSE]<computed_column_definition>:<computed_column_expression> PERSISTED <data_type><index_column_name>:<column_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
-
SingleStore 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 approximately for 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.You can monitor the progress of the
ALTER TABLE
statement by running the following statement:SELECT * FROM INFORMATION_SCHEMA.MV_PROCESSLISTWHERE INFO LIKE '%ALTER%' ORDER BY NODE_ID;This query contains information about currently running
ALTER
commands for all nodes on a cluster.Since DDL commands like ALTER
run on the master aggregator only, the order byNODE_
clause ensures the master aggregator is the top result.ID Note the STATE
column as it displays the state of the process.A common state for DDL commands like
ALTER
is: waiting on the leaves.This is a normal state in most cases since altering the table happens on the leaves. If the execution of the DDL command is waiting on a metadata lock on the leaves and an error message is generated, there could be a long-running query preventing the lock from being taken. A metadata lock is taken when DDL commands are executed to ensure a consistent state on all nodes. Refer to the Query Errors topic for resolving query timeout errors due to long running queries in a workload.
-
Online
ALTER TABLE
in SingleStore cannot be rolled back, so operations that may result in errors are disallowed.For example, you cannot add indexes with online ALTER TABLE
because there may be duplicate keys in the indexed column -
Once the
ALTER TABLE
command is invoked, it cannot be stopped or killed if it is in the commit phase and must be completed.Killing the query will have no effect; the query will continue to perform its work in the background even if the cluster is restarted. You must wait and allow the ALTER
to complete.If the ALTER
is killed during the commit phase, restart the cluster and executeOPTIMIZE TABLE <table_
to complete the failedname> FIX_ ALTER ALTER
. -
ALTER OFFLINE TABLE
is supported onREFERENCE
tables only.ALTER OFFLINE TABLE
operations will cause write queries against the table to fail until theALTER 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, SingleStore 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. -
Multiple
ALTER TABLE
commands can run in parallel on the same database (one per table though). -
Use
ALTER TABLE CHANGE
to rename a column. -
Use
ALTER TABLE RENAME TO
orALTER TABLE RENAME AS
to rename the table.TO
orAS
can be used interchangeably as they are synonyms.If a read on a table is occurring during the alter operation, the read will continue. -
This command can be run on the master aggregator node, or a child aggregator node (see Cluster Management Commands ).
-
Columns used to construct a view cannot be dropped.
See CREATE VIEW for more information. -
<computed_
defines the value of a computed column using other columns in the table, constants, built-in functions, operators, and combinations thereof.column_ expression> For more information see Using Persistent Computed Columns. -
On columnstore tables, adding columns, dropping columns, and changing column names are supported, but using
ALTER TABLE MODIFY
to change the data type for a column is not supported.To modify column data types in Columnstore tables, see the examples below. On rowstore tables, adding columns, dropping columns, and changing column names are supported.
Using ALTER TABLE MODIFY
to change the data type from a string to an integer will default any values in those columns to 0. -
Only hash indexes can be added or dropped on columnstore tables.
Multi-column hash indexes are supported. Please note, the maximum number of columns an index can contain is 32. Multi-column index syntax:
ALTER TABLE <table_name> ADD KEY (col_name_1, col_name_2,... col_name_32); -
ALTER TABLE <table_
modifies configuration variables associated with the index.name> MODIFY KEY SET (. . . ) It is currently only supported on columnstore indexes. See Configuring the Columnstore to Work Effectively for more information. -
ALTER TABLE <table_
modifies the table by assigning the key designation to a chosen field or multiple fields.name> ADD KEY <field_ name> ADD KEY
is the same as CREATE INDEX. -
ALTER TABLE <table_
modifies the table by assigning the key designation to a chosen field.name> ADD KEY <field_ name> ADD KEY
is the same as CREATE INDEX. -
ALTER TABLE <table_
enables or disables automatic statistics collection on the table.name> { ENABLE | DISABLE } AUTOSTATS There are three categories of autostats - AUTOSTATS_
,CARDINALITY_ MODE AUTOSTATS_
, andHISTOGRAM_ MODE AUTOSTATS_
.SAMPLING SingleStore lets you independently control how each category of statistics is automatically gathered. Multiple autostats settings can be combined in a single ALTER TABLE
statement.See Automatic Statistics for more information. -
ALTER TABLE <table_
sets the next auto_name> AUTO_ INCREMENT [=] <new_ value> increment value that will be generated. It can only be used with reference tables and to increase the internal auto_ increment counter. If <new_
is smaller than the current value, a warning is shown, displaying the current value.value> -
Altering
PRIMARY
orSHARD
keys is unsupported.Altering UNIQUE
keys is only supported on rowstore reference tables:CREATE ROWSTORE REFERENCE TABLE table_
name -
This command causes implicit commits.
Refer to COMMIT for more information. -
TIMEOUT
, a value specified in milliseconds, determines the length of time to wait for theALTER TABLE
command to commit across the cluster.If not specified, the default_
global variable value is used.distributed_ ddl_ timeout -
The
SERIES TIMESTAMP
clause specifies a table column as the default timestamp column.This setting can be specified only for a single table column. The column can be either of the following data types: DATE
,TIME
,DATETIME
, orTIMESTAMP
.SingleStore recommends to use either DATETIME
orDATETIME(6)
data type instead ofTIMESTAMP
because the automatic update behavior ofTIMESTAMP
is subject to change.See Timestamp Behavior for details. -
The
SERIES TIMESTAMP
clause does not affect the data type of a table column, rather it specifies the behavior of the column in the time series specific functions likeFIRST()
,LAST()
, andTIME_
.BUCKET -
For an example of how to modify a rowstore table’s columns to use sparse compression, see Example 3.
Also, see COMPRESSION = SPARSE and SPARSE behavior for a general discussion on sparse rowstore compression. -
Adding an index on a table having the
COMPRESSION = SPARSE
table option will store all key columns in their non-sparse format -
Adding an index on a column having the
SPARSE
column option is not allowed. -
Dropping an index on a table having the
COMPRESSION = SPARSE
option will, for each column, store the column as sparse if possible. -
If a table was created with the
SPARSE
option on individual columns, you will not be able to modify theCOMPRESSION = SPARSE
table option, and vice-versa.For example, if a table is created using the statement CREATE TABLE <table_
, you would not be able to runname> (<column_ 1> INT, <column_ 2> VARCHAR(50) SPARSE, <column_ 3> DATETIME); ALTER TABLE <table_
successfully.name> COMPRESSION = SPARSE; -
The
BACKGROUND_
option allows the background merger to be enabled or disabled on existing tables.MERGER This is helpful when making shallow copies of tables. By default, the background merger will be disabled on any table that is created through a shallow copy operation. Here is an example of how to enable the background merger on an existing table:
ALTER TABLE col_table BACKGROUND_MERGER=ON; -
In rowstores, you can use the
ALTER TABLE
statement to modify the collation of existing columns.For more information, see Modifying the Collation of Columns.
-
In columnstores, you can use the
ALTER TABLE <table_
statement to add a column group to an existing table.name> ADD COLUMN GROUP See How the Columnstore Works for more information about column groups. Here is an example of how to alter a columnstore table to add a column group to all columns:
ALTER TABLE col_table ADD COLUMN GROUP col_group_ind (*)); -
When creating a table, using an optional COLUMN GROUP clause creates a materialized copy of each row as a separate index.
A column group can be dropped from a table by using an ALTER TABLE .
statement.. . DROP COLUMN GROUP . . . For example: ALTER TABLE col_table DROP COLUMN GROUP col_group_ind; -
Refer to the Permission Matrix for the required permission.
Note
Successful completion of a new ALTER TABLE
will invalidate the plancache for all queries that operate on the affected table, requiring their recompilation.
Examples
ALTER TABLE to Add Columns
The following example creates a table with three columns, then alters the table to add a fourth column called middle_
.
CREATE ROWSTORE TABLE customer(first_name VARCHAR(30),last_name VARCHAR(30),birth_date DATE);
INSERT INTO customer VALUES("Joe", "Williams", "1960/1/2"),("Esther", "Johnson", "1962/7/19");
SELECT * FROM customer;
+------------+-----------+------------+
| first_name | last_name | birth_date |
+------------+-----------+------------+
| Esther | Johnson | 1962-07-19 |
| Joe | Williams | 1960-01-02 |
+------------+-----------+------------+
ALTER TABLE customer ADD column middle_initial CHAR(1);
See the result:
SELECT * FROM customer;
+------------+-----------+------------+----------------+
| first_name | last_name | birth_date | middle_initial |
+------------+-----------+------------+----------------+
| Esther | Johnson | 1962-07-19 | NULL |
| Joe | Williams | 1960-01-02 | NULL |
+------------+-----------+------------+----------------+
UPDATE customer SET middle_initial = "T" WHERE first_name = "Esther";
See the result:
SELECT * FROM customer;
+------------+-----------+------------+----------------+
| first_name | last_name | birth_date | middle_initial |
+------------+-----------+------------+----------------+
| Esther | Johnson | 1962-07-19 | T |
| Joe | Williams | 1960-01-02 | NULL |
+------------+-----------+------------+----------------+
ALTER TABLE to Rename Columns
The following example uses the customer
table from Example 1.middle_
column to a middle_
column.
ALTER TABLE customer CHANGE middle_initial middle_name;ALTER TABLE customer MODIFY middle_name VARCHAR(30);
See the result:
SELECT * FROM customer;
+------------+-----------+------------+-------------+
| first_name | last_name | birth_date | middle_name |
+------------+-----------+------------+-------------+
| Esther | Johnson | 1962-07-19 | T |
| Joe | Williams | 1960-01-02 | NULL |
+------------+-----------+------------+-------------+
UPDATE customer SET middle_name = "Wallace" WHERE last_name = "Williams";
See the result:
SELECT * FROM customer;
+------------+-----------+------------+-------------+
| first_name | last_name | birth_date | middle_name |
+------------+-----------+------------+-------------+
| Esther | Johnson | 1962-07-19 | T |
| Joe | Williams | 1960-01-02 | Wallace |
+------------+-----------+------------+-------------+
ALTER TABLE to Modify Individual SPARSE Compression Columns
The following example demonstrates how to modify existing columns in a table to use or not to use sparse compression.
Create a table with three columns.b
is a sparse column.
CREATE ROWSTORE TABLE sparse_demo (a INT, b VARCHAR(50) SPARSE, c DATETIME);
Modify column a
to use sparse compression.b
to use non-sparse compression.
ALTER TABLE sparse_demo MODIFY COLUMN a INT SPARSE;ALTER TABLE sparse_demo MODIFY COLUMN b VARCHAR(50);
See the results:
SELECT column_name, is_sparse FROM information_schema.columnsWHERE table_name = 'sparse_demo';
+-------------+-----------+
| column_name | is_sparse |
+-------------+-----------+
| a | YES |
| b | NO |
| c | NO |
+-------------+-----------+
ALTER TABLE to Modify All Columns to Use SPARSE Compression
The following example demonstrates how to modify all of the columns in the table to use sparse compression.
Create a table with three non-sparse columns.
CREATE ROWSTORE TABLE sparse_demo2 (a INT, b VARCHAR(50), c DATETIME);
ALTER TABLE sparse_demo2 COMPRESSION = SPARSE;
See the results:
SELECT column_name, is_sparse FROM information_schema.columnsWHERE table_name = 'sparse_demo2';
+-------------+-----------+
| column_name | is_sparse |
+-------------+-----------+
| a | YES |
| b | YES |
| c | YES |
+-------------+-----------+
ALTER TABLE to Modify Column Data Types, Sizes, and Collation
Note
On rowstore tables, adding columns, dropping columns, and changing column names are supported using the MODIFY COLUMN
argument.ALTER TABLE MODIFY COLUMN
to change the data type from a string to integer will default any values in those columns to 0.
The following example demonstrates how to modify the data type of a column in a rowstore table.col1
to a FLOAT
data type.
ALTER TABLE alter_rowstore_table MODIFY COLUMN col1 FLOAT;
The following example demonstrates how to modify the data type of a column in a columnstore table.
CREATE TABLE towns (id INT UNSIGNED NOT NULL PRIMARY KEY,town_name VARCHAR(64) COLLATE utf8_general_ci NOT NULL,pop INT NOT NULL);
INSERT INTO townsVALUES(1, 'Hamilton Bay', 12346),(2, 'Probability Bay', 263951),(3, 'Decentralized Park', 29265),(4, 'Axiom Township', 845696),(5, 'Elegant Island', 987654);
DESC towns;
+-----------+------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------+------------------+------+-----+---------+
| id | int(10) unsigned | NO | PRI | NULL |
| town_name | varchar(64) | NO | | NULL |
| pop | int(11) | YES | | NULL |
+-----------+------------------+------+-----+---------+
First, create the new column, pop2
with the desired data type:
ALTER TABLE towns ADD COLUMN pop2 DECIMAL(20);
Then, copy the data into the new column (pop2
), rename the original column to a different name (pop_
) to retain the data in the original column, rename the new column to the original column name, confirm the data type for the new column is correct, and drop the original column with the old data type:
-- Copy data from the original column to the new columnUPDATE towns SET pop2 = pop;-- Rename the old column into a different name to retain dataALTER TABLE towns CHANGE pop pop_to_delete;-- Rename the new column to the original column nameALTER TABLE towns CHANGE pop2 pop;-- Confirm the data type is correctDESC towns;
+---------------+------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+---------------+------------------+------+-----+---------+
| id | int(10) unsigned | NO | PRI | NULL |
| town_name | varchar(64) | NO | | NULL |
| pop_to_delete | int(10) unsigned | NO | | NULL |
| pop | decimal(20,0) | YES | | NULL |
+---------------+------------------+------+-----+---------+
-- Drop the column created to retain the data
ALTER TABLE towns DROP COLUMN pop_to_delete;
If the new data type is not correct after checking, revert the columns as follows and repeat the process from the previous code block.
ALTER TABLE towns CHANGE pop pop2;ALTER TABLE towns CHANGE pop_to_delete pop;
ALTER TABLE to Add Keys
The following examples demonstrate how a table will look before and after ADD KEY
with the ALTER TABLE
command is used.
CREATE TABLE employees(emp_id INT(7),emp_lastname VARCHAR(25),emp_firstname VARCHAR(25),emp_dept VARCHAR(25));
INSERT employeesVALUES(114, "Williams", "Trent", "sales"),("010", "Stevens", "Carol", "R&D"),("208", "Lowell", "Martin", "maint"),("160", "Anderson", "Jane", "sales");
DESC employees;
+----------------+------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+----------------+------------------+------+-----+---------+
| emp_id | int(7) | YES | | NULL |
| emp_lastname | varchar(25) | YES | | NULL |
| emp_firstname | varchar(25) | YES | | NULL |
| emp_dept | varchar(25) | YES | | NULL |
+----------------+------------------+------+-----+---------+
Below is the result if ADD KEY
had been applied to a single field in the table.
ALTER TABLE employees ADD KEY (emp_id);
See the result:
SHOW CREATE TABLE employees;
+-----------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+----------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` |
| | (`emp_id` int(7) DEFAULT NULL, ` |
| | emp_lastname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, |
| | `emp_firstname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, |
| | `emp_dept` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, |
| | SORT KEY `__UNORDERED` (), |
| | KEY `emp_id` (`emp_id`) USING HASH, |
| | SHARD KEY () ) |
| | AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES' |
+-----------+----------------------------------------------------------------------------------------------------------------------------+
Below is the result if ADD KEY
had been applied to more than one field in the table.
ALTER TABLE employees ADD KEY id_lastname (emp_id, emp_lastname);
See the result:
SHOW CREATE TABLE employees;
+-----------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+----------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` |
| | (`emp_id` int(7) DEFAULT NULL, ` |
| | emp_lastname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, |
| | `emp_firstname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, |
| | `emp_dept` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, |
| | SORT KEY `__UNORDERED` (), |
| | KEY `emp_id` (`emp_id`,`emp_lastname`) USING HASH |
| | SHARD KEY () ) |
| | AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES' |
+-----------+----------------------------------------------------------------------------------------------------------------------------+
ALTER TABLE to Change Column Positions
This example shows how to move a column and add a column at a specific position in a table.
CREATE TABLE at_chgord (a int, b int, c int);/* add a column at the beginning */ALTER TABLE at_chgord ADD COLUMN id bigint FIRST;/* change the type of b to bigint but leave it in the same position */ALTER TABLE at_chgord ADD COLUMN b_big bigint;UPDATE at_chgord SET b_big = b; /* for a huge table, consider doing this in batches */
Now the table looks like this:
DESC at_chgord;
+-------+------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+------+---------+-------+
| id | bigint(20) | YES | | NULL | |
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
| b_big | bigint(20) | YES | | NULL | |
+-------+------------+------+------+---------+-------+
Next, drop column b:
ALTER TABLE at_chgord DROP COLUMN b;
Now move b_AFTER
:
ALTER TABLE at_chgord MODIFY b_big bigint AFTER a;
Then rename it:
ALTER TABLE at_chgord CHANGE b_big b;
And here's the final result:
DESC at_chgord;
+-------+------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+------+---------+-------+
| id | bigint(20) | YES | | NULL | |
| a | int(11) | YES | | NULL | |
| b | bigint(20) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
+-------+------------+------+------+---------+-------+
ALTER TABLE to Change Full-Text Search Versions
The full-text search version (from legacy to version 2) can be changed on an existing table.DROP INDEX
command:
Note
If an index name was not designated when creating the table, the full-text index key_
must be used when dropping the index.key_
is displayed when the SHOW INDEXES FROM <table_
command is executed.
DROP INDEX <fts_index_name> | <index_key_name> ON <table_name>;
Then, ALTER TABLE
command is used with the FULLTEXT USING VERSION 2
argument :
ALTER TABLE <table_name> ADD FULLTEXT USING VERSION 2 fts_index_name (<fts_col>) | (<fts_col1>,..., <fts_coln>);
Refer to Working with Full-Text Search for more information about full-text search versions.
Last modified: December 12, 2024