Syntax
------ 
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS]
tbl_name
 (create_definition,...) [table_options ]...
[partition_options]
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS]
tbl_name
 [(create_definition,...)] [table_options ]...
[partition_options]
 select_statement
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS]
tbl_name
 { LIKE old_table_name | (LIKE old_table_name) }
 
select_statement:
 [IGNORE | REPLACE] [AS] SELECT ... (Some legal select
statement)
 
Description
----------- 
Use the CREATE TABLE statement to create a table with the
given name.
 
In its most basic form, the CREATE TABLE statement provides
a table name
followed by a list of columns, indexes, and constraints. By
default, the table
is created in the default database. Specify a database with
db_name.tbl_name.
If you quote the table name, you must quote the database
name and table name
separately as `db_name`.`tbl_name`. This is particularly
useful for CREATE TABLE ... SELECT, because it allows to
create a table into a database, which contains data from
other databases. See Identifier Qualifiers.
 
If a table with the same name exists, error 1050 results.
Use IF NOT EXISTS
to suppress this error and issue a note instead. Use SHOW
WARNINGS
to see notes.
 
The CREATE TABLE statement automatically commits the current
transaction,
except when using the TEMPORARY keyword.
 
For valid identifiers to use as table names, see Identifier
Names.
 
Note: if the default_storage_engine is set to ColumnStore
then it needs setting on all UMs. Otherwise when the tables
using the default engine are replicated across UMs they will
use the wrong engine. You should therefore not use this
option as a session variable with ColumnStore.
 
Microsecond precision can be between 0-6. If no precision is
specified it is assumed to be 0, for backward compatibility
reasons.
 
Privileges
 
Executing the CREATE TABLE statement requires the CREATE
privilege for the table or the database.
 
CREATE OR REPLACE TABLE ...
 
The OR REPLACE clause was added in MariaDB 10.0.8.
 
If the OR REPLACE clause is used and if the table already
exists, then instead of returning an error, the server will
drop the existing table and replace it with the newly
defined table.
 
This syntax was originally added to make replication more
robust if it has to rollback and repeat statements such as
CREATE ... SELECT on slaves.
 
CREATE OR REPLACE TABLE table_name (a int);
 
is basically the same as:
 
CREATE TABLE table_name (a int);
 
with the following exceptions:
If table_name was locked with LOCK TABLES it will continue
to be locked after the statement.
Temporary tables are only dropped if the TEMPORARY keyword
was used. (With DROP TABLE, temporary tables are preferred
to be dropped before normal tables).
 
Things to be Aware of With CREATE OR REPLACE
 
The table is dropped first (if it existed), after that the
CREATE is done. Because of this, if the CREATE fails, then
the table will not exist anymore after the statement. If the
table was used with LOCK TABLES it will be unlocked.
One can't use OR REPLACE together with IF EXISTS.
Slaves in replication will by default use CREATE OR REPLACE
when replicating CREATE statements that don''t use IF
EXISTS. This can be changed by setting the variable
slave-ddl-exec-mode to STRICT.
 
CREATE TABLE IF NOT EXISTS ...
 
If the IF NOT EXISTS clause is used, then the index will
only be created if an index with the same name does not
already exist. If the index already exists, then a warning
will be triggered by default.
 
CREATE TEMPORARY TABLE ...
 
Use the TEMPORARY keyword to create a temporary table that
is only available to your current session. Temporary tables
are dropped when the your session ends. Temporary table
names are specific to your session. They will not conflict
with other temporary tables from other session even if they
share the same name. They will shadow names of non-temporary
tables or views, if they are identical. A temporary table
can have the same name as a non-temporary table which is
located in the same database. In that case, their name will
reference the temporary table when used in SQL statements.
You must have the CREATE TEMPORARY TABLES privilege on the
database to create temporary tables. If no storage engine is
specified, the default_tmp_storage_engine setting will
determine the engine.
 
CREATE TABLE ... LIKE
 
Use the LIKE clause instead of a full table definition to
create a table with the same definition as another table,
including columns, indexes, and table options. Foreign key
definitions, as well as any DATA DIRECTORY or INDEX
DIRECTORY table options specified on the original table,
will not be created.
 
CREATE TABLE ... SELECT
 
You can create a table containing data from other tables
using the CREATE ... SELECT statement. Columns will be
created in the table for each field returned by the SELECT
query.
 
You can also define some columns normally and add other
columns from a SELECT. You can also create columns in the
normal way and assign them some values using the query, this
is done to force a certain type or other field
characteristics. The columns that are not named in the query
will be placed before the others. For example:
 
CREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM
 SELECT 5 AS b, c, d FROM another_table;
 
Remember that the query just returns data. If you want to
use the same indexes, or the same columns attributes ([NOT]
NULL, DEFAULT, AUTO_INCREMENT) in the new table, you need to
specify them manually. Types and sizes are not automatically
preserved if no data returned by the SELECT requires the
full size, and VARCHAR could be converted into CHAR. The
CAST() function can be used to forcee the new table to use
certain types.
 
Aliases (AS) are taken into account, and they should always
be used when you SELECT an expression (function,
arithmetical operation, etc).
 
If an error occurs during the query, the table will not be
created at all.
 
If the new table has a primary key or UNIQUE indexes, you
can use the IGNORE or REPLACE keywords to handle duplicate
key errors during the query. IGNORE means that the newer
values must not be inserted an identical value exists in the
index. REPLACE means that older values must be overwritten.
 
If the columns in the new table are more than the rows
returned by the query, the columns populated by the query
will be placed after other columns. Note that if the strict
SQL_MODE is on, and the columns that are not names in the
query do not have a DEFAULT value, an error will raise and
no rows will be copied.
 
Concurrent inserts are not used during the execution of a
CREATE ... SELECT.
 
If the table already exists, an error similar to the
following will be returned:
 
ERROR 1050 (42S01): Table 't' already exists
 
If the IF NOT EXISTS clause is used and the table exists, a
note will be produced instead of an error.
 
To insert rows from a query into an existing table, INSERT
... SELECT can be used.
 
Column Definitions
 
create_definition:
 { col_name column_definition | index_definition |
period_definition | CHECK (expr) }
 
column_definition:
 data_type
 [NOT NULL | NULL] [DEFAULT default_value | (expression)]
 [AUTO_INCREMENT] [ZEROFILL] [UNIQUE [KEY] | [PRIMARY] KEY]
 [INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING]
 [COMMENT 'string']
 [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
 [reference_definition]
 | data_type [GENERATED ALWAYS] 
 AS { { ROW {START|END} } | { (expression) [VIRTUAL |
PERSISTENT | STORED] } }
 [UNIQUE [KEY]] [COMMENT 'string']
 
constraint_definition:
 CONSTRAINT [constraint_name] CHECK (expression)
Note: MariaDB accepts the REFERENCES clause in ALTER TABLE
and CREATE TABLE column definitions, but that syntax does
nothing. MariaDB simply parses it without returning any
error or warning, for compatibility with other DBMS's.
Before MariaDB 10.2.1 this was also true for CHECK
constraints. Only the syntax for indexes described below
creates foreign keys.
 
Each definition either creates a column in the table or
specifies and index or
constraint on one or more columns. See Indexes below for
details
on creating indexes.
 
Create a column by specifying a column name and a data type,
optionally
followed by column options. See Data Types for a full list
of data types allowed in MariaDB.
 
NULL and NOT NULL
 
Use the NULL or NOT NULL options to specify that values in
the column
may or may not be NULL, respectively. By default, values may
be NULL. See also NULL Values in MariaDB.
 
DEFAULT Column Option
 
The DEFAULT clause was enhanced in MariaDB 10.2.1. Some
enhancements include
BLOB and TEXT columns now support DEFAULT.
The DEFAULT clause can now be used with an expression or
function.
 
Specify a default value using the DEFAULT clause. If you
don't specify DEFAULT then the following rules apply:
If the column is not defined with NOT NULL, AUTO_INCREMENT
or TIMESTAMP, an explicit DEFAULT NULL will be added.
Note that in MySQL and in MariaDB before 10.1.6, you may get
an explicit DEFAULT for primary key parts, if not specified
with NOT NULL.
 
The default value will be used if you INSERT a row without
specifying a value for that column, or if you specify
DEFAULT for that column.
Before MariaDB 10.2.1 you couldn't usually provide an
expression or function to evaluate at
insertion time. You had to provide a constant default value
instead. The one
exception is that you may use CURRENT_TIMESTAMP as
the default value for a TIMESTAMP column to use the current
timestamp at insertion time.
 
CURRENT_TIMESTAMP may also be used as
the default value for a DATETIME
 
From MariaDB 10.2.1 you can use most functions in DEFAULT.
Expressions should have parentheses around them. If you use
a non deterministic function in DEFAULT then all inserts to
the table will be replicated in row mode. You can even refer
to earlier columns in the DEFAULT expression:
 
CREATE TABLE t1 (a int DEFAULT (1+1), b int DEFAULT (a+1));
CREATE TABLE t2 (a bigint primary key DEFAULT UUID_SHORT());
 
The DEFAULT clause cannot contain any stored functions or
subqueries, and a column used in the clause must already
have been defined earlier in the statement.
 
Since MariaDB 10.2.1, it is possible to assign BLOB or TEXT
columns a DEFAULT value. In earlier versions, assigning a
default to these columns was not possible.
 
Starting from 10.3.3 you can also use DEFAULT (NEXT VALUE
FOR sequence)
 
AUTO_INCREMENT Column Option
 
Use AUTO_INCREMENT to create a column whose value can
can be set automatically from a simple counter. You can only
use AUTO_INCREMENT
on a column with an integer type. The column must be a key,
and there can only be
one AUTO_INCREMENT column in a table. If you insert a row
without specifying
a value for that column (or if you specify 0, NULL, or
DEFAULT
as the value), the actual value will be taken from the
counter, with each insertion
incrementing the counter by one. You can still insert a
value explicitly. If you
insert a value that is greater than the current counter
value, the counter is
set based on the new value. An AUTO_INCREMENT column is
implicitly NOT NULL.
Use LAST_INSERT_ID to get the AUTO_INCREMENT value
most recently used by an INSERT statement.
 
ZEROFILL Column Option
 
If the ZEROFILL column option is specified for a column
using a numeric data type, then the column will be set to
UNSIGNED and the spaces used by default to pad the field are
replaced with zeros. ZEROFILL is ignored in expressions or
as part of a UNION. ZEROFILL is a non-standard MySQL and
MariaDB enhancement.
 
PRIMARY KEY Column Option
 
Use PRIMARY KEY (or just KEY) to make a column a primary
key. A primary key is a special type of a unique key. There
can be at most one primary key per table, and it is
implicitly NOT NULL.
 
Specifying a column as a unique key creates a unique index
on that column. See the Index Definitions section below for
more information.
 
UNIQUE KEY Column Option
 
Use UNIQUE KEY (or just UNIQUE) to specify that all values
in the column
must be distinct from each other. Unless the column is NOT
NULL, there may be
multiple rows with NULL in the column. 
 
Specifying a column as a unique key creates a unique index
on that column. See the Index Definitions section below for
more information.
 
COMMENT Column Option
 
You can provide a comment for each column using the COMMENT
clause. The maximum length is 1024 characters (it was 255
characters before MariaDB 5.5). Use
the SHOW FULL COLUMNS statement to see column comments.
 
Generated Columns
 
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.
 
COLUMN_FORMAT
 
COLUMN_FORMAT is only used by MySQL Cluster, and is silently
ignored in MariaDB.
 
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}
 
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 silently ignored, and the name of the index
is always PRIMARY.
 
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