
  REFERENCES tbl_name (index_col_name,...)
   [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
   [ON DELETE reference_option]
   [ON UPDATE reference_option]

reference_option:
  RESTRICT | CASCADE | SET NULL | NO ACTION

INDEX and KEY are synonyms.

Index names are optional, if not specified an automatic name will be assigned.
Index name are needed to drop indexes and appear in error messages when a
constraint is violated.

Index Categories
----------------

Plain Indexes
-------------

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.

PRIMARY KEY
-----------

For PRIMARY KEY indexes, you can specify a name for the index, but it is
ignored, and the name of the index is always PRIMARY. From MariaDB 10.3.18 and
MariaDB 10.4.8, a warning is explicitly issued if a name is specified. Before
then, the name was silently ignored.

See Getting Started with Indexes: Primary Key for more information.

UNIQUE
------

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.

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).
* 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.

FULLTEXT
--------

Use the FULLTEXT keyword to create full-text indexes.

See Full-Text Indexes for more information.

SPATIAL
-------

Use the SPATIAL keyword to create geometric indexes.

See SPATIAL INDEX for more information.

Index Options
-------------

KEY_BLOCK_SIZE Index Option
---------------------------

The KEY_BLOCK_SIZE index option is similar to the KEY_BLOCK_SIZE table option.

With the InnoDB storage engine, if you specify a non-zero value for the
KEY_BLOCK_SIZE table option for the whole table, then the table will
implicitly be created with the ROW_FORMAT table option set to COMPRESSED.
However, this does not happen if you just set the KEY_BLOCK_SIZE index option
for one or more indexes in the table. The InnoDB storage engine ignores the
KEY_BLOCK_SIZE index option. However, the SHOW CREATE TABLE statement may
still report it for the index.

For information about the KEY_BLOCK_SIZE index option, see the KEY_BLOCK_SIZE
table option below.

Index Types
-----------

Each storage engine supports some or all index types. See Storage Engine Index
Types for details on permitted index types for each storage engine.

Different index types are optimized for different kind of operations:

* BTREE is the default type, and normally is the best choice. It is supported
by all storage engines. It can be used to compare a column's value with a
value using the =, >, >=, <, <=, BETWEEN, and LIKE operators. BTREE can also
be used to find NULL values. Searches against an index prefix are possible.
* HASH is only supported by the MEMORY storage engine. HASH indexes can only
be used for =, <=, and >= comparisons. It can not be used for the ORDER BY
clause. Searches against an index prefix are not possible.
* RTREE is the default for SPATIAL indexes, but if the storage engine does not
support it BTREE can be used.

Index columns names are listed between parenthesis. After each column, a
prefix length can be specified. If no length is specified, the whole column
will be indexed. ASC and DESC can be specified for compatibility with are
DBMS's, but have no meaning in MariaDB.

WITH PARSER Index Option
------------------------

The WITH PARSER index option only applies to FULLTEXT indexes and contains the