
-----------------

A generated column is a column in a table that cannot explicitly be set to a
specific value in a DML query. Instead, its value is automatically generated
based on an expression. This expression might generate the value based on the
values of other columns in the table, or it might generate the value by
calling built-in functions or user-defined functions (UDFs).

There are two types of generated columns:

* PERSISTENT or STORED: This type's value is actually stored in the table.
* VIRTUAL: This type's value is not stored at all. Instead, the value is
generated dynamically when the table is queried. This type is the default.

Generated columns are also sometimes called computed columns or virtual
columns.

For a complete description about generated columns and their limitations, see
Generated (Virtual and Persistent/Stored) Columns.

COMPRESSED
----------

Certain columns may be compressed. See Storage-Engine Independent Column
Compression.

INVISIBLE
---------

Columns may be made invisible, and hidden in certain contexts. See Invisible
Columns.

WITH SYSTEM VERSIONING Column Option
------------------------------------

Columns may be explicitly marked as included from system versioning. See
System-versioned tables for details.

WITHOUT SYSTEM VERSIONING Column Option
---------------------------------------

Columns may be explicitly marked as excluded from system versioning. See
System-versioned tables for details.

Index Definitions
-----------------

index_definition:
  {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option]
...
 {{{|}}} {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
 {{{|}}} [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
 {{{|}}} [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type]
(index_col_name,...) [index_option] ...
 {{{|}}} [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
reference_definition

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} ]
 [ IGNORED | NOT IGNORED ]

reference_definition:
  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.

MariaDB starting with 10.5
--------------------------
Unique, if index type is not specified, is normally a BTREE index that can
also be used by the optimizer to find rows. If the key is longer than the max
key length for the used storage engine, a HASH key will be created. This
enables MariaDB to enforce uniqueness for any type or number of columns.

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. Prior to MariaDB 10.8, this
was only for compatibility with other DBMSs, but had no meaning in MariaDB.
From MariaDB 10.8, individual columns in the index can now be explicitly
sorted in ascending or descending order. This can be useful for optimizing
certain ORDER BY cases (MDEV-13756, MDEV-26938, MDEV-26939, MDEV-26996). From
MariaDB 11.4.0, not only ascending, but also descending, indexes can now be
used to optimize MIN() and MAX() (MDEV-27576).

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

The WITH PARSER index option only applies to FULLTEXT indexes and contains the
fulltext parser name. The fulltext parser must be an installed plugin.

COMMENT Index Option
--------------------

A comment of up to 1024 characters is permitted with the COMMENT index option.

The COMMENT index option allows you to specify a comment with user-readable
text describing what the index is for. This information is not used by the
server itself.

CLUSTERING Index Option
-----------------------

The CLUSTERING index option is only valid for tables using the TokuDB storage
engine.

IGNORED / NOT IGNORED
---------------------

MariaDB starting with 10.6.0
----------------------------
From MariaDB 10.6.0, indexes can be specified to be ignored by the optimizer.
See Ignored Indexes.

Periods
-------

period_definition:
  PERIOD FOR SYSTEM_TIME (start_column_name, end_column_name)
MariaDB supports a subset of the standard syntax for periods. At the moment
it's only used for creating System-versioned tables. Both columns must be
created, must be either of a TIMESTAMP(6) or BIGINT UNSIGNED type, and be
generated as ROW START and ROW END accordingly. See System-versioned tables
for details.

The table must also have the WITH SYSTEM VERSIONING clause.

Constraint Expressions
----------------------

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

MariaDB 10.2.1 introduced two ways to define a constraint:

* CHECK(expression) given as part of a column definition.
* CONSTRAINT [constraint_name] CHECK (expression)

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

create table t1 (a int check(a>0) ,b int check (b> 0), constraint abc check
(a>b));

If you use the second format and you don't give a name to the constraint, then
the constraint will get a auto generated name. This is done so that you can
later delete the constraint with ALTER TABLE DROP constraint_name.

One can disable all constraint expression checks by setting the variable
check_constraint_checks to OFF. This is useful for example when loading a
table that violates some constraints that you want to later find and fix in
SQL.

See CONSTRAINT for more information.

Table Options
-------------

For each individual table you create (or alter), you can set some table
options. The general syntax for setting options is:

<OPTION_NAME> = <option_value>, [<OPTION_NAME> = <option_value> ...]

The equal sign is optional.

Some options are supported by the server and can be used for all tables, no
matter what storage engine they use; other options can be specified for all
storage engines, but have a meaning only for some engines. Also, engines can
extend CREATE TABLE with new options.

If the IGNORE_BAD_TABLE_OPTIONS SQL_MODE is enabled, wrong table options
generate a warning; otherwise, they generate an error.

table_option:    
  [STORAGE] ENGINE [=] engine_name
 | AUTO_INCREMENT [=] value
 | AVG_ROW_LENGTH [=] value
 | [DEFAULT] CHARACTER SET [=] charset_name
 | CHECKSUM [=] {0 | 1}
 | [DEFAULT] COLLATE [=] collation_name
 | COMMENT [=] 'string'
 | CONNECTION [=] 'connect_string'
 | DATA DIRECTORY [=] 'absolute path to directory'
 | DELAY_KEY_WRITE [=] {0 | 1}
 | ENCRYPTED [=] {YES | NO}
 | ENCRYPTION_KEY_ID [=] value
 | IETF_QUOTES [=] {YES | NO}
 | INDEX DIRECTORY [=] 'absolute path to directory'
 | INSERT_METHOD [=] { NO | FIRST | LAST }
 | KEY_BLOCK_SIZE [=] value
 | MAX_ROWS [=] value
 | MIN_ROWS [=] value
 | PACK_KEYS [=] {0 | 1 | DEFAULT}
 | PAGE_CHECKSUM [=] {0 | 1}
 | PAGE_COMPRESSED [=] {0 | 1}
 | PAGE_COMPRESSION_LEVEL [=] {0 .. 9}
 | PASSWORD [=] 'string'
 | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT|PAGE}
 | SEQUENCE [=] {0|1}
 | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
 | STATS_PERSISTENT [=] {DEFAULT|0|1}
 | STATS_SAMPLE_PAGES [=] {DEFAULT|value}
 | TABLESPACE tablespace_name
 | TRANSACTIONAL [=]  {0 | 1}
 | UNION [=] (tbl_name[,tbl_name]...)
 | WITH SYSTEM VERSIONING

[STORAGE] ENGINE
----------------

[STORAGE] ENGINE specifies a storage engine for the table. If this option is
not used, the default storage engine is used instead. That is, the
default_storage_engine session option value if it is set, or the value
specified for the --default-storage-engine mariadbd startup option, or the
default storage engine, InnoDB. If the specified storage engine is not
installed and active, the default value will be used, unless the
NO_ENGINE_SUBSTITUTION SQL MODE is set (default). This is only true for CREATE
TABLE, not for ALTER TABLE. For a list of storage engines that are present in
your server, issue a SHOW ENGINES.

AUTO_INCREMENT
--------------

AUTO_INCREMENT specifies the initial value for the AUTO_INCREMENT primary key.
This works for MyISAM, Aria, InnoDB, MEMORY, and ARCHIVE tables. You can
change this option with ALTER TABLE, but in that case the new value must be
higher than the highest value which is present in the AUTO_INCREMENT column.
If the storage engine does not support this option, you can insert (and then
delete) a row having the wanted value - 1 in the AUTO_INCREMENT column.

AVG_ROW_LENGTH
--------------

AVG_ROW_LENGTH is the average rows size. It only applies to tables using
MyISAM and Aria storage engines that have the ROW_FORMAT table option set to
FIXED format.

MyISAM uses MAX_ROWS and AVG_ROW_LENGTH to decide the maximum size of a table
(default: 256TB, or the maximum file size allowed by the system).

[DEFAULT] CHARACTER SET/CHARSET
-------------------------------

[DEFAULT] CHARACTER SET (or [DEFAULT] CHARSET) is used to set a default
character set for the table. This is the character set used for all columns