
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.

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

Add system versioning. See System-versioned tables.

DROP SYSTEM VERSIONING
----------------------

Drop system versioning. See System-versioned tables.

ADD PERIOD FOR SYSTEM_TIME
--------------------------

See System-versioned tables.

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;

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 (the default). 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.

The rebuild may fail if conditions are violated due to a change in the
sql_mode. For example:

CREATE OR REPLACE TABLE x (d DATE DEFAULT '0000-00-00');


ALTER TABLE x FORCE;
ERROR 1067 (42000): Invalid default value for 'd'

Partitions
----------

ADD PARTITION
-------------

See Partitioning Overview: Adding Partitions for details.

ANALYZE PARTITION
-----------------

See Partitioning Overview: Analyzing Partitions for details.

CHECK PARTITION
---------------

See Partitioning Overview: Checking Partitions for details.

COALESCE PARTITION
------------------

Reduces the number of HASH or KEY partitions in a table. See Partitioning
Overview: Coalescing Partitions.

CONVERT PARTITION / TABLE
-------------------------

CONVERT PARTITION and CONVERT TABLE were introduced in MariaDB 10.7.1.

CONVERT PARTITION can be used to remove a partition from a table and make this
an ordinary table. For example:

ALTER TABLE partitioned_table CONVERT PARTITION part1 TO TABLE normal_table;

CONVERT PARTITION will take an existing table and move this to another table
as its own partition with a specified partition definition. For example the
following moves normal_table to a partition of partitioned_table with a
definition that its values, based on the PARTITION BY of the
partitioned_table, are less than 12345.

ALTER TABLE partitioned_table CONVERT TABLE normal_table 
 TO PARTITION part1 VALUES LESS THAN (12345);

From MariaDB 11.4, the optional [{WITH | WITHOUT} VALIDATION] is permitted.

See Partitioning Overview: Converting Partitions to/from Tables for more
details.

See also 10.7 preview feature: CONVERT PARTITION (mariadb.org blog post)

DROP PARTITION
--------------

Used to drop specific partitions (and discard all data within the specified
partitions) for RANGE and LIST partitions. See Partitioning Overview: Dropping
Partitions.

EXCHANGE PARTITION
------------------

This is used to exchange the contents of a partition with another table. This
is performed by swapping the tablespaces of the partition with the other table.

From MariaDB 11.4, the optional [{WITH | WITHOUT} VALIDATION] is permitted.

See Partitioning Overview: Exchanging Partitions for more details.

See also copying InnoDB's transportable tablespaces.

OPTIMIZE PARTITION
------------------

See Partitioning Overview: Optimizing Partitions for details.

REMOVE PARTITIONING
-------------------

See Partitioning Overview: Removing Partitioning.

REORGANIZE PARTITION
--------------------

See Partitioning Overview: Reorganizing Partitions.

TRUNCATE PARTITION
------------------

See Partitioning Overview: Truncating Partitions.

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

The ALTER TABLE statement supports the ALGORITHM clause. This clause is one of
the clauses that is used to implement online DDL. ALTER TABLE supports several
different algorithms. An algorithm can be explicitly chosen for an ALTER TABLE
operation by setting the ALGORITHM clause. 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: ALGORITHM 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
--------------

ALGORITHM=COPY is the name for the original ALTER TABLE algorithm from early
MariaDB versions.

When ALGORITHM=COPY is set, MariaDB essentially does the following operations:

-- Create a temporary table with the new definition
CREATE TEMPORARY TABLE tmp_tab (
...
);

-- Copy the data from the original table
INSERT INTO tmp_tab
 SELECT * FROM original_tab;

-- Drop the original table
DROP TABLE original_tab;

-- Rename the temporary table, so that it replaces the original one
RENAME TABLE tmp_tab TO original_tab;

This algorithm is very inefficient, but it is generic, so it works for all
storage engines.

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.

From MariaDB 11.2, ALTER TABLE can now do most operations with ALGORITHM=COPY,
LOCK=NONE. See LOCK=NONE.

ALGORITHM=INPLACE
-----------------

ALGORITHM=COPY can be incredibly slow, because the whole table has to be
copied and rebuilt. ALGORITHM=INPLACE was introduced as a way to avoid this by
performing operations in-place and avoiding the table copy and rebuild, when
possible.

When ALGORITHM=INPLACE is set, the underlying storage engine uses
optimizations to perform the operation while avoiding the table copy and
rebuild. However, INPLACE is a bit of a misnomer, since some operations may
still require the table to be rebuilt for some storage engines. Regardless,
several operations can be performed without a full copy of the table for some
storage engines.

A more accurate name would have been ALGORITHM=ENGINE, where ENGINE refers to
an "engine-specific" algorithm.

If an ALTER TABLE operation supports ALGORITHM=INPLACE, then it can be
performed using optimizations by the underlying storage engine, but it may
rebuilt.

See InnoDB Online DDL Operations with ALGORITHM=INPLACE for more.

ALGORITHM=NOCOPY
----------------

ALGORITHM=NOCOPY was introduced in MariaDB 10.3.7.

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has
to rebuild the clustered index, because when the clustered index has to be
rebuilt, the whole table has to be rebuilt. ALGORITHM=NOCOPY was introduced as
a way to avoid this.

If an ALTER TABLE operation supports ALGORITHM=NOCOPY, then it can be
performed without rebuilding the clustered index.

If ALGORITHM=NOCOPY is specified for an ALTER TABLE operation that does not
support ALGORITHM=NOCOPY, then an error will be raised. In this case, raising
an error is preferable, if the alternative is for the operation to rebuild the
clustered index, and perform unexpectedly slowly.

See InnoDB Online DDL Operations with ALGORITHM=NOCOPY for more.

ALGORITHM=INSTANT
-----------------

ALGORITHM=INSTANT was introduced in MariaDB 10.3.7.

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has
to modify data files. ALGORITHM=INSTANT was introduced as a way to avoid this.

If an ALTER TABLE operation supports ALGORITHM=INSTANT, then it can be
performed without modifying any data files.

If ALGORITHM=INSTANT is specified for an ALTER TABLE operation that does not
support ALGORITHM=INSTANT, then an error will be raised. In this case, raising
an error is preferable, if the alternative is for the operation to modify data
files, and perform unexpectedly slowly.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT for more.

LOCK
----

The ALTER TABLE statement supports the LOCK clause. This clause is one of the
clauses that is used to implement online DDL. ALTER TABLE supports several
different locking strategies. A locking strategy can be explicitly chosen for