ALTER TABLE

Syntax

ALTER [ONLINE] [IGNORE] TABLE tbl_name
    [WAIT n | NOWAIT]
    alter_specification [, alter_specification] ...
alter_specification:
    table_option ...
  | ADD [COLUMN] [IF NOT EXISTS] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] [IF NOT EXISTS] (col_name column_definition,...)
  | ADD {INDEX|KEY} [IF NOT EXISTS] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
        reference_definition
  | ADD PERIOD FOR SYSTEM_TIME (start_column_name, end_column_name)
  | ALTER [COLUMN] col_name SET DEFAULT literal | (expression)
  | ALTER [COLUMN] col_name DROP DEFAULT
  | CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] [IF EXISTS] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} [IF EXISTS] index_name
  | DROP FOREIGN KEY [IF EXISTS] fk_symbol
  | DROP CONSTRAINT [IF EXISTS] constraint_name
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | ALGORITHM [=] {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | FORCE
  | partition_options
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
  | REMOVE PARTITIONING
  | ADD SYSTEM VERSIONING
  | DROP SYSTEM VERSIONING
index_col_name:
    col_name [(length)] [ASC | DESC]
index_type:
    USING {BTREE | HASH | RTREE}
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | CLUSTERING={YES| NO}
table_options:
    table_option [[,] table_option] ...

ADD COLUMN       [IF NOT EXISTS]
ADD INDEX        [IF NOT EXISTS]
ADD FOREIGN KEY  [IF NOT EXISTS]
ADD PARTITION    [IF NOT EXISTS]
CREATE INDEX     [IF NOT EXISTS]
DROP COLUMN      [IF EXISTS]
DROP INDEX       [IF EXISTS]
DROP FOREIGN KEY [IF EXISTS]
DROP PARTITION   [IF EXISTS]
CHANGE COLUMN    [IF EXISTS]
MODIFY COLUMN    [IF EXISTS]
DROP INDEX       [IF EXISTS]

Description

ALTER TABLE enables you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and the storage engine of the table.

If another connection is using the table, a metadata lock is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.

When adding a UNIQUE index on a column (or a set of columns) which have duplicated values, an error will be produced and the statement will be stopped. To suppress the error and force the creation of UNIQUE indexes, discarding duplicates, the IGNORE option can be specified. This can be useful if a column (or a set of columns) should be UNIQUE but it contains duplicate values; however, this technique provides no control on which rows are preserved and which are deleted. Also, note that IGNORE is accepted but ignored in ALTER TABLE ... EXCHANGE PARTITION statements.

This statement can also be used to rename a table. For details see RENAME TABLE.

When an index is created, the storage engine may use a configurable buffer in the process. Incrementing the buffer speeds up the index creation. Aria and MyISAM allocate a buffer whose size is defined by aria_sort_buffer_size or myisam_sort_buffer_size, also used for REPAIR TABLE. InnoDB/XtraDB allocates three buffers whose size is defined by innodb_sort_buffer_size.

Privileges

Executing the ALTER TABLE statement generally requires at least the ALTER privilege for the table or the database..

If you are renaming a table, then it also requires the DROP, CREATE and INSERT privileges for the table or the database as well.

Online DDL

In MariaDB 10.0 and later, online DDL is supported with the ALGORITHM and LOCK clauses.

See InnoDB Online DDL Overview for more information on online DDL with InnoDB.

ALTER ONLINE TABLE

Online ALTER TABLE is available by executing the following:

ALTER ONLINE TABLE ...;

This statement has the following semantics:

ALTER TABLE ... LOCK=NONE;

ALTER TABLE ... ALGORITHM=INPLACE;

Column Definitions

See CREATE TABLE: Column Definitions for information about column definitions.

Index Definitions

See CREATE TABLE: Index Definitions for information about index definitions.

The CREATE INDEX and DROP INDEX statements can also be used to add or remove an index.

Character Sets and Collations

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name

See Setting Character Sets and Collations for details on setting the character sets and collations.

Alter Specifications

Table Options

See CREATE TABLE: Table Options for information about table options.

WAIT/NOWAIT

ADD COLUMN

... ADD COLUMN [IF NOT EXISTS]  (col_name column_definition,...)

Adds a column to the table. The syntax is the same as in CREATE TABLE. If you are using IF NOT_EXISTS the column will not be added if it was not there already. This is very useful when doing scripts to modify tables.

The FIRST and AFTER clauses affect the physical order of columns in the datafile. Use FIRST to add a column in the first (leftmost) position, or AFTER followed by a column name to add the new column in any other position. Note that, nowadays, the physical position of a column is usually irrelevant.

See also Instant ADD COLUMN for InnoDB.

DROP COLUMN

... DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]

Drops the column from the table. If you are using IF EXISTS you will not get an error if the column didn't exist. If the column is part of any index, the column will be dropped from them, except if you add a new column with identical name at the same time. The index will be dropped if all columns from the index were dropped. If the column was used in a view or trigger, you will get an error next time the view or trigger is accessed.

CREATE TABLE a (
  a int,
  b int,
  primary key (a,b)
);

ALTER TABLE x DROP COLUMN a;
[42000][1072] Key column 'A' doesn't exist in table

ALTER TABLE x DROP COLUMN a;
Query OK, 0 rows affected (0.46 sec)

DESC x;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

RESTRICT and CASCADE are allowed to make porting from other database systems easier. In MariaDB, they do nothing.

MODIFY COLUMN

Allows you to modify the type of a column. The column will be at the same place as the original column and all indexes on the column will be kept. Note that when modifying column, you should specify all attributes for the new column.

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY((a));
ALTER TABLE t1 MODIFY a BIGINT UNSIGNED AUTO_INCREMENT;

CHANGE COLUMN

Works like MODIFY COLUMN except that you can also change the name of the column. The column will be at the same place as the original column and all index on the column will be kept.

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a));
ALTER TABLE t1 CHANGE a b BIGINT UNSIGNED AUTO_INCREMENT;

ALTER COLUMN

This lets you change column options.

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, b varchar(50), PRIMARY KEY(a));
ALTER TABLE t1 ALTER b SET DEFAULT 'hello';

ENABLE/ DISABLE KEYS

DISABLE KEYS will disable all non unique keys for the table for storage engines that support this (at least MyISAM and Aria). This can be used to speed up inserts into empty tables.

ENABLE KEYS will enable all disabled keys.

ADD CONSTRAINT

Modifies the table adding a constraint on a particular column or columns.

Note: Before MariaDB 10.2.1, constraint expressions were accepted in syntax, but ignored.

ALTER TABLE table_name 
ADD CONSTRAINT [constraint_name] CHECK(expression);

Before a row is inserted or updated, all constraints are evaluated in the order they are defined. If any constraint fails, then the row will not be updated. One can use most deterministic functions in a constraint, including UDF's.

CREATE TABLE account_ledger (
	id INT PRIMARY KEY AUTO_INCREMENT,
	transaction_name VARCHAR(100),
	credit_account VARCHAR(100),
	credit_amount INT,
	debit_account VARCHAR(100),
	debit_amount INT);

ALTER TABLE account_ledger 
ADD CONSTRAINT is_balanced 
    CHECK((debit_amount + credit_amount) = 0);

The constraint_name is optional. If you don't provide one in the ALTER TABLE statement, MariaDB auto-generates a name for you. This is done so that you can remove it later using DROP CONSTRAINT clause.

You can disable all constraint expression checks by setting the variable check_constraint_checks to OFF. You may find this useful when loading a table that violates some constraints that you want to later find and fix in SQL.

To view constraints on a table, query information_schema.TABLE_CONSTRAINTS:

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE 
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'account_ledger';

+-----------------+----------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME     | CONSTRAINT_TYPE |
+-----------------+----------------+-----------------+
| is_balanced     | account_ledger | CHECK           |
+-----------------+----------------+-----------------+

DROP CONSTRAINT

Modifies the table, removing the given constraint.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

When you add a constraint to a table, whether through a CREATE TABLE or ALTER TABLE...ADD CONSTRAINT statement, you can either set a constraint_name yourself, or allow MariaDB to auto-generate one for you. To view constraints on a table, query information_schema.TABLE_CONSTRAINTS. For instance,

CREATE TABLE t (
   a INT,
   b INT,
   c INT,
   CONSTRAINT CHECK(a > b),
   CONSTRAINT check_equals CHECK(a = c)); 

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE 
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 't';

+-----------------+----------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME     | CONSTRAINT_TYPE |
+-----------------+----------------+-----------------+
| check_equals    | t              | CHECK           |
| CONSTRAINT_1    | t              | CHECK           |
+-----------------+----------------+-----------------+

To remove a constraint from the table, issue an ALTER TABLE...DROP CONSTRAINT statement. For example,

ALTER TABLE t DROP CONSTRAINT is_unique;

SYSTEM VERSIONING

FORCE

ALTER TABLE ... FORCE can force MariaDB to re-build the table.

In MariaDB 5.5 and before, this could only be done by setting the ENGINE table option to its old value. For example, for an InnoDB table, one could execute the following:

ALTER TABLE tab_name ENGINE = InnoDB;

In MariaDB 10.0 and later, the FORCE option can be used instead. For example, :

ALTER TABLE tab_name FORCE;

With InnoDB, the table rebuild will only reclaim unused space (i.e. the space previously used for deleted rows) if the innodb_file_per_table system variable is set to ON. If the system variable is OFF, then the space will not be reclaimed, but it will be-re-used for new data that's later added.

EXCHANGE PARTITION

This is used to exchange the tablespace files between a partition and another table.

See copying InnoDB's transportable tablespaces for more information.

DISCARD TABLESPACE

This is used to discard an InnoDB table's tablespace.

See copying InnoDB's transportable tablespaces for more information.

IMPORT TABLESPACE

This is used to import an InnoDB table's tablespace. The tablespace should have been copied from its original server after executing FLUSH TABLES FOR EXPORT.

See copying InnoDB's transportable tablespaces for more information.

ALTER TABLE ... IMPORT only applies to InnoDB tables. Most other popular storage engines, such as Aria and MyISAM, will recognize their data files as soon as they've been placed in the proper directory under the datadir, and no special DDL is required to import them.

ALGORITHM

In MariaDB 5.5 and before, ALTER TABLE operations required making a temporary copy of the table, which can be slow for large tables.

In MariaDB 10.0, the ALGORITHM clause was added. This clause can be used to exert some control over how MariaDB performs the ALTER TABLE operation. The supported values are:

• ALGORITHM=DEFAULT - This implies the default behavior for the specific statement, such as if no ALGORITHM clause is specified.
• ALGORITHM=COPY
• ALGORITHM=INPLACE
• ALGORITHM=NOCOPY - This was added in MariaDB 10.3.7.
• ALGORITHM=INSTANT - This was added in MariaDB 10.3.7.

See InnoDB Online DDL Overview: Online DDL ALGORITHM Options for information on how the ALGORITHM clause affects InnoDB.

ALGORITHM=DEFAULT

The default behavior, which occurs if ALGORITHM=DEFAULT is specified, or if ALGORITHM is not specified at all, usually only makes a copy if the operation doesn't support being done in-place at all. In this case, the most efficient available algorithm will usually be used.

However, in MariaDB 10.3.6 and before, if the value of the old_alter_table system variable is set to ON, then the default behavior is to perform ALTER TABLE operations by making a copy of the table using the old algorithm.

In MariaDB 10.3.7 and later, the old_alter_table system variable is deprecated. Instead, the alter_algorithm system variable defines the default algorithm for ALTER TABLE operations.

ALGORITHM=COPY

If ALGORITHM=COPY is specified, then the copy algorithm will be used even if it is not necessary. This can result in a lengthy table copy. If multiple ALTER TABLE operations are required that each require the table to be rebuilt, then it is best to specify all operations in a single ALTER TABLE statement, so that the table is only rebuilt once.

ALGORITHM=INPLACE

In MariaDB 10.0 and later, many ALTER TABLE operations can be performed using in-place operations, without making a temporary copy of the table.

Over time, more operations have been made possible without a full copy. Here is a list of the actions that can be performed without having to copy the table:

If ALGORITHM=INPLACE is specified, then the storage engine performs the operation in-place. Concurrent modification to the table may be possible. If the operation cannot be done in-place by the specific storage engine, then an error is raised instead.

ALGORITHM=NOCOPY

The ALGORITHM=NOCOPY option was introduced in MariaDB 10.3.7.

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has to rebuild the clustered index.

If ALGORITHM=NOCOPY is specified, then an error will be raised in cases where the clustered index would be rebuilt, since that would cause the whole table to be rebuilt. Raising an error is sometimes preferable if the alternative is for the statement to perform unexpectedly slowly.

ALGORITHM=INSTANT

The ALGORITHM=INSTANT option was introduced in MariaDB 10.3.7.

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has to modify data files.

If ALGORITHM=INSTANT is specified, then an error will be raised in cases where data files would be modified. Raising an error is sometimes preferable if the alternative is for the statement to perform unexpectedly slowly.

LOCK

Depending on the required operation and the used storage engine, different lock strategies can be used for ALTER TABLE. In some cases no locks are required, other times only read locks are required, while sometimes write locks are required. The LOCK clause can be used to require a certain lock strategy. It will be used if possible (even if you specify a strategy which is more restrictive than necessary), but if the required operation on the storage engine needs a more restrictive strategy, an error is issued. Allowed values for the LOCK clause are:

• DEFAULT: The maximum allowed concurrency for the specific operation is permitted.
• NONE: No lock is acquired. All concurrent DML is permitted. If this level of locking is not permitted for an operation, then an error is raised.
• SHARED: A read lock is acquired. Read-only concurrent DML is permitted. If this level of locking is not permitted for an operation, then an error is raised.
• EXCLUSIVE: A write lock is acquired. No concurrent DML is permitted.

A missing LOCK clause is equivalent to LOCK=DEFAULT.

ALTER ONLINE TABLE is equivalent to LOCK=NONE. Therefore, you can also use that statement to ensure that your ALTER TABLE allows concurrent DML.

Progress Reporting

MariaDB provides progress reporting for ALTER TABLE statement for clients that support the new progress reporting protocol. For example, if you were using the mysql client, then the progress report might look like this::

ALTER TABLE test ENGINE=Aria;
Stage: 1 of 2 'copy to tmp table'    46% of stage

The progress report is also shown in the output of the SHOW PROCESSLIST statement and in the contents of the information_schema.PROCESSLIST table.

See Progress Reporting for more information.

Aborting ALTER TABLE Operations

If an ALTER TABLE operation is being performed and the connection is killed, the changes will be rolled back in a controlled manner. The rollback can be a slow operation as the time it takes is relative to how far the operation has progressed.

Examples

Adding a new column:

ALTER TABLE t1 ADD x INT;

Dropping a column:

ALTER TABLE t1 DROP x;

Modifying the type of a column:

ALTER TABLE t1 MODIFY x bigint unsigned;

Changing the name and type of a column:

ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;

Combining multiple clauses in a single ALTER TABLE statement, separated by commas:

ALTER TABLE t1 DROP x, ADD x2 INT,  CHANGE y y2 INT;

Changing the storage engine:

ALTER TABLE t1 ENGINE = InnoDB;

Rebuilding the table (the previous example will also rebuild the table if it was already InnoDB):

ALTER TABLE t1 FORCE;

See Also

• CREATE TABLE
• DROP TABLE
• Character Sets and Collations
• SHOW CREATE TABLE
• Instant ADD COLUMN for InnoDB