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] ...
In MariaDB 10.0.2 and later, IF EXISTS and IF NOT EXISTS
clauses have been added for the following:
 
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]
When IF EXISTS and IF NOT EXISTS are used in clauses,
queries will not
report errors when the condition is triggered for that
clause. A warning with
the same message text will be issued and the ALTER will move
on to the next
clause in the statement (or end if finished).
 
This was done in MDEV-318.
 
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
 
ALTER ONLINE TABLE has also worked for partitioned tables
since MariaDB 10.0.11.
 
Online ALTER TABLE is available by executing the following:
 
ALTER ONLINE TABLE ...;
 
This statement has the following semantics:
 
In MariaDB 10.0.12 and later, this statement is equivalent
to the following:
 
ALTER TABLE ... LOCK=NONE;
 
See the LOCK alter specification for more information.
 
In MariaDB 10.0.11, this statement is equivalent to the
following:
 
ALTER TABLE ... ALGORITHM=INPLACE;
 
See the ALGORITHM alter specification for more information.
 
MariaDB until 10.0.10
 
In MariaDB 10.0.10 and before, this statement ensures that
the ALTER TABLE statement does not make a copy of the table.
 
WAIT/NOWAIT
 
Set the lock wait timeout. See WAIT and NOWAIT.
 
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.
 
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.
 
Dropping a column that is part of a multi-column UNIQUE
constraint is not permitted. For example:
 
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
 
The reason is that dropping column a would result in the new
constraint that all values in column b be unique. In order
to drop the column, an explicit DROP PRIMARY KEY and ADD
PRIMARY KEY would be required. Up until MariaDB 10.2.7, the
column was dropped and the additional constraint applied,
resulting in the following structure:
 
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 | |
+-------+---------+------+-----+---------+-------+
 
MariaDB 10.4.0 supports instant DROP COLUMN. DROP COLUMN of
an indexed column would imply DROP INDEX (and in the case of
a non-UNIQUE multi-column index, possibly ADD INDEX). These
will not be allowed with ALGORITHM=INSTANT, but unlike
before, they can be allowed with ALGORITHM=NOCOPY
 
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';
 
ADD PRIMARY KEY
 
Add a primary key.
 
For PRIMARY KEY indexes, you can specify a name for the
index, but it is silently ignored, and the name of the index
is always PRIMARY.
 
See Getting Started with Indexes: Primary Key for more
information.
 
DROP PRIMARY KEY
 
Drop a primary key.
 
For PRIMARY KEY indexes, you can specify a name for the
index, but it is silently ignored, and the name of the index
is always PRIMARY.
 
See Getting Started with Indexes: Primary Key for more
information.
 
ADD FOREIGN KEY
 
Add a foreign key.
 
For FOREIGN KEY indexes, a reference definition must be
provided.
 
For FOREIGN KEY indexes, you can specify a name for the
constraint, using the CONSTRAINT keyword. That name will be
used in error messages.
 
First, you have to specify the name of the target (parent)
table and a column or a column list which must be indexed
and whose values must match to the foreign key's values.
The MATCH clause is accepted to improve the compatibility
with other DBMS's, but has no meaning in MariaDB. The ON
DELETE and ON UPDATE clauses specify what must be done when
a DELETE (or a REPLACE) statements attempts to delete a
referenced row from the parent table, and when an UPDATE
statement attempts to modify the referenced foreign key
columns in a parent table row, respectively. The following
options are allowed:
RESTRICT: The delete/update operation is not performed. The
statement terminates with a 1451 error (SQLSTATE '2300').
NO ACTION: Synonym for RESTRICT.
CASCADE: The delete/update operation is performed in both
tables.
SET NULL: The update or delete goes ahead in the parent
table, and the corresponding foreign key fields in the child
table are set to NULL. (They must not be defined as NOT NULL
for this to succeed).
 
MariaDB until 5.3
SET DEFAULT: This option is currently implemented only for
the PBXT storage engine, which is disabled by default and no
longer maintained. It sets the child table's foreign key
fields to their DEFAULT values when the referenced parent
table key entries are updated or deleted.
 
If either clause is omitted, the default behavior for the
omitted clause is RESTRICT.
 
See Foreign Keys for more information.
 
DROP FOREIGN KEY
 
Drop a foreign key.
 
See Foreign Keys for more information.
 
ADD INDEX
 
Add a plain index.
 
Plain indexes are regular indexes that are not unique, and
are not acting as a primary key or a foreign key. They are
also not the "specialized" FULLTEXT or SPATIAL indexes.
 
See Getting Started with Indexes: Plain Indexes for more
information.
 
DROP INDEX
 
Drop a plain index.
 
Plain indexes are regular indexes that are not unique, and
are not acting as a primary key or a foreign key. They are
also not the "specialized" FULLTEXT or SPATIAL indexes.
 
See Getting Started with Indexes: Plain Indexes for more
information.
 
ADD UNIQUE INDEX
 
Add a unique index.
 
The UNIQUE keyword means that the index will not accept
duplicated values, except for NULLs. An error will raise if
you try to insert duplicate values in a UNIQUE index.
 
For UNIQUE indexes, you can specify a name for the
constraint, using the CONSTRAINT keyword. That name will be
used in error messages.
 
See Getting Started with Indexes: Unique Index for more
information.
 
DROP UNIQUE INDEX
 
Drop a unique index.
 
The UNIQUE keyword means that the index will not accept
duplicated values, except for NULLs. An error will raise if
you try to insert duplicate values in a UNIQUE index.
 
For UNIQUE indexes, you can specify a name for the
constraint, using the CONSTRAINT keyword. That name will be
used in error messages.
 
See Getting Started with Indexes: Unique Index for more
information.
 
ADD FULLTEXT INDEX
 
Add a FULLTEXT index.
 
See Full-Text Indexes for more information.
 
DROP FULLTEXT INDEX
 
Drop a FULLTEXT index.
 
See Full-Text Indexes for more information.
 
ADD SPATIAL INDEX
 
Add a SPATIAL index.
 
See SPATIAL INDEX for more information.
 
DROP SPATIAL INDEX
 
Drop a SPATIAL index.
 
See SPATIAL INDEX for more information.
 
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.
 
RENAME TO
 
Renames the table. See also RENAME TABLE.
 
ADD CONSTRAINT
 
Modifies the table adding a constraint on a particular
column or columns.
 
MariaDB 10.2.1 introduced new ways to define a constraint.
 
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
 
DROP CONSTRAINT for UNIQUE and FOREIGN KEY constraints was
introduced in MariaDB 10.2.22 and MariaDB 10.3.13.
 
DROP CONSTRAINT for CHECK constraints was introduced in
MariaDB 10.2.1
 
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;
 
ADD SYSTEM VERSIONING
 
System-versioned tables was added in MariaDB 10.3.4.
 
Add system versioning.
 
DROP SYSTEM VERSIONING
 
System-versioned tables was added in MariaDB 10.3.4.
 
Drop system versioning.
 
ADD PERIOD FOR SYSTEM_TIME
 
System-versioned tables was added in MariaDB 10.3.4.
 
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
 
ALTER TABLE ... EXCHANGE PARTITION was introduced in MariaDB
10.0.4
 
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 I