Syntax\n------\n\nALTER [ONLINE] [IGNORE] TABLE [IF EXISTS] tbl_name\n    [WAIT n | NOWAIT]\n    alter_specification [, alter_specification] ...\n\nalter_specification:\n    table_option ...\n  | ADD [COLUMN] [IF NOT EXISTS] col_name column_definition\n        [FIRST | AFTER col_name ]\n  | ADD [COLUMN] [IF NOT EXISTS] (col_name column_definition, ...)\n  | ADD {INDEX|KEY} [IF NOT EXISTS] [index_name]\n        [index_type] (index_col_name,...) [index_option] ...\n  | ADD [CONSTRAINT [symbol]] PRIMARY KEY [IF NOT EXISTS]\n        [index_type] (index_col_name,...) [index_option] ...\n  | ADD [CONSTRAINT [symbol]]\n        UNIQUE [INDEX|KEY] [IF NOT EXISTS] [index_name]\n        [index_type] (index_col_name,...) [index_option] ...\n  | ADD FULLTEXT [INDEX|KEY] [IF NOT EXISTS [index_name]\n        (index_col_name,...) [index_option] ...\n  | ADD SPATIAL [INDEX|KEY] [IF NOT EXISTS [index_name]\n        (index_col_name,...) [index_option] ...\n  | ADD VECTOR [INDEX|KEY] [IF NOT EXISTS [index_name]\n        (index_col_name,...) [index_option] ...\n  | ADD [CONSTRAINT [symbol]]\n        FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)\n        reference_definition\n  | ADD PERIOD FOR [time_period_name|SYSTEM_TIME] (start_column_name, end_column_name)\n  | ALTER [COLUMN] col_name SET DEFAULT literal | (expression)\n  | ALTER [COLUMN] col_name DROP DEFAULT\n  | ALTER {INDEX|KEY} [IF EXISTS] index_name [NOT] IGNORED\n  | CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name column_definition\n        [FIRST|AFTER col_name]\n  | MODIFY [COLUMN] [IF EXISTS] col_name column_definition\n        [FIRST | AFTER col_name]\n  | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]\n  | DROP [CONSTRAINT] PRIMARY KEY\n  | DROP {INDEX|KEY} [IF EXISTS] index_name\n  | DROP FOREIGN KEY [IF EXISTS] fk_symbol\n  | DROP CONSTRAINT [IF EXISTS] constraint_name\n  | DISABLE KEYS\n  | ENABLE KEYS\n  | RENAME [TO] new_tbl_name\n  | ORDER BY col_name [, col_name] ...\n  | RENAME COLUMN [IF EXISTS] old_col_name TO new_col_name\n  | RENAME {INDEX|KEY} old_index_name TO new_index_name\n  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]\n  | [DEFAULT] CHARACTER SET [=] charset_name\n  | [DEFAULT] COLLATE [=] collation_name\n  | DISCARD TABLESPACE\n  | IMPORT TABLESPACE\n  | ALGORITHM [=] {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}\n  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}\n  | FORCE\n  | partition_options\n  | CONVERT TABLE normal_table TO partition_definition [{WITH | WITHOUT} VALIDATION]\n  | CONVERT PARTITION partition_name TO TABLE tbl_name\n  | ADD PARTITION [IF NOT EXISTS] (partition_definition)\n  | DROP PARTITION [IF EXISTS] partition_names\n  | TRUNCATE PARTITION partition_names\n  | COALESCE PARTITION number\n  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]\n  | ANALYZE PARTITION partition_names\n  | CHECK PARTITION partition_names\n  | OPTIMIZE PARTITION partition_names\n  | REBUILD PARTITION partition_names\n  | REPAIR PARTITION partition_names\n  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]\n  | REMOVE PARTITIONING\n  | ADD SYSTEM VERSIONING\n  | DROP SYSTEM VERSIONING\n\nindex_col_name:\n    col_name [(length)] [ASC | DESC]\n\nindex_type:\n    USING {BTREE | HASH | RTREE}\n\nindex_option:\n    KEY_BLOCK_SIZE [=] value\n  | index_type\n  | WITH PARSER parser_name\n  | VISIBLE\n  | COMMENT 'string'\n  | CLUSTERING={YES | NO}\n  | { IGNORED | NOT IGNORED }\n  | DISTANCE={EUCLIDEAN | COSINE}\n  | M=number\n\n  table_option [[,] table_option] ...\n\nDescription\n-----------\n\nALTER 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.\n\nIf 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.\n\nWhen 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.\n\nThis statement can also be used to rename a table. For details see RENAME TABLE.\n\nWhen 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 allocates three buffers whose size is defined by innodb_sort_buffer_size.\n\nPrivileges\n\nExecuting the ALTER TABLE statement generally requires at least the ALTER privilege for the table or the database. If you are renaming a table, this also requires the DROP, CREATE and INSERT privileges for the table or the database.\n\nOnline DDL\n\nOnline DDL is supported with the ALGORITHM and LOCK clauses.\n\nSee InnoDB Online DDL Overview for more information on online DDL with InnoDB.\n\nALTER ONLINE TABLE\n\nALTER ONLINE TABLE also works for partitioned tables.\n\nALTER ONLINE TABLE ... is equivalent to the ALTER TABLE ... LOCK = NONE.\n\nSee the LOCK alter specification for more information.\n\nWAIT/NOWAIT\n\nSet the lock wait timeout. See WAIT and NOWAIT.\n\nIF EXISTS\n\nThe IF EXISTS and IF NOT EXISTS clauses are available for the following clauses:\n\n``sql\nADD COLUMN       [IF NOT EXISTS]\nADD INDEX        [IF NOT EXISTS]\nADD FOREIGN KEY  [IF NOT EXISTS]\nADD PARTITION    [IF NOT EXISTS]\nCREATE INDEX     [IF NOT EXISTS]\nDROP COLUMN      [IF EXISTS]\nDROP INDEX       [IF EXISTS]\nDROP FOREIGN KEY [IF EXISTS]\nDROP PARTITION   [IF EXISTS]\nCHANGE COLUMN    [IF EXISTS]\nMODIFY COLUMN    [IF EXISTS]\nDROP INDEX       [IF EXISTS]\n`\n\nWhen 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).\n\nIf this is directive is used after ALTER ... TABLE, you don't get an error if the table doesn't exist. Example:\n\n`sql\nCREATE TABLE t (i INT);\nALTER TABLE IF EXISTS t RENAME COLUMN IF EXISTS i TO k;\n`\n\nColumn Definitions\n\nSee CREATE TABLE: Column Definitions for information about column definitions.\n\nIndex Definitions\n\nSee CREATE TABLE: Index Definitions for information about index definitions.\n\nThe CREATE INDEX and DROP INDEX statements can also be used to add or remove an index.\n\nCharacter Sets and Collations\n\n`sql\nCONVERT TO CHARACTER SET charset_name [COLLATE collation_name]\n[DEFAULT] CHARACTER SET [=] charset_name\n[DEFAULT] COLLATE [=] collation_name\n`\n\nSee Setting Character Sets and Collations for details on setting the character sets and collations.\n\nAlter Specifications\n\nTable Options\n\nSee CREATE TABLE: Table Options for information about table options.\n\nADD COLUMN\n\n`sql\n... ADD COLUMN [IF NOT EXISTS]  (col_name column_definition,...)\n`\n\nAdds 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.\n\nThe 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.\n\nSee also Instant ADD COLUMN for InnoDB.\n\nDROP COLUMN\n\n`sql\n... DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]\n`\n\nDrops 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:\n\n`sql\nCREATE TABLE a (\n  a int,\n  b int,\n  primary key (a,b)\n);\n\nALTER TABLE x DROP COLUMN a;\n[42000][1072] Key column 'A' doesn't exist in table\n`\n\nThe 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.\n\nMariaDB 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 prior versions, they can be allowed with ALGORITHM=NOCOPY\n\nRESTRICT and CASCADE are allowed to make porting from other database systems easier. In MariaDB, they do nothing.\n\nMODIFY COLUMN\n\nAllows 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.\n\n`sql\nCREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY((a));\nALTER TABLE t1 MODIFY a BIGINT UNSIGNED AUTO_INCREMENT;\n`\n\nCHANGE COLUMN\n\nWorks 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.\n\n`sql\nCREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a));\nALTER TABLE t1 CHANGE a b BIGINT UNSIGNED AUTO_INCREMENT;\n`\n\nALTER COLUMN\n\nThis lets you change column options.\n\n`sql\nCREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, b varchar(50), PRIMARY KEY(a));\nALTER TABLE t1 ALTER b SET DEFAULT 'hello';\n`\n\nRENAME INDEX/KEY\n\nYou can rename an index using the RENAME INDEX (or RENAME KEY) syntax:\n\n`sql\nALTER TABLE t1 RENAME INDEX i_old TO i_new;\n`\n\nRENAME INDEX/KEY is not available.\n\nRENAME COLUMN\n\nYou can rename a column using the RENAME COLUMN syntax:\n\n`sql\nALTER TABLE t1 RENAME COLUMN c_old TO c_new;\n`\n\nRENAME COLUMN is not available.\n\nADD PRIMARY KEY\n\nAdds 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.\n\nDROP PRIMARY KEY\n\nDrops 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.\n\nADD FOREIGN KEY\n\nAdds 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.\n\nFirst, 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:\n\n RESTRICT: The delete/update operation is not performed. The statement terminates with a 1451 error (SQLSTATE '2300').\n NO ACTION: Synonym for RESTRICT.\n CASCADE: The delete/update operation is performed in both tables.\n 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).\n SET DEFAULT: This option is implemented only for the legacy 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.\n\nIf either clause is omitted, the default behavior for the omitted clause is RESTRICT.\n\nSee Foreign Keys for more information.\n\nDROP FOREIGN KEY\n\nDrops a foreign key. See Foreign Keys for more information.\n\nADD INDEX\n\nAdds 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. For limits on InnoDB indexes, see InnoDB Limitations. See Getting Started with Indexes: Plain Indexes for more information.\n\nDROP INDEX\n\nDrops 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.\n\nADD UNIQUE INDEX\n\nAdds 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.\n\nDROP UNIQUE INDEX\n\nDrops 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.\n\nADD FULLTEXT INDEX\n\nAdds a FULLTEXT index. See Full-Text Indexes for more information.\n\nDROP FULLTEXT INDEX\n\nDrops a FULLTEXT index. See Full-Text Indexes for more information.\n\nADD SPATIAL INDEX\n\nAdds a SPATIAL index. See SPATIAL INDEX for more information.\n\nDROP SPATIAL INDEX\n\nDrops a SPATIAL index. See SPATIAL INDEX for more information.\n\nENABLE/ DISABLE KEYS\n\nDISABLE KEYS disables 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 enables all disabled keys.\n\nRENAME TO\n\nRenames a table. See also RENAME TABLE.\n\nADD CONSTRAINT\n\nModifies the table adding a constraint on a particular column or columns.\n\n`sql\nALTER TABLE table_name \nADD CONSTRAINT [constraint_name] CHECK(\n\n[Truncated — full content at mariadb.com/docs]