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

If the OR REPLACE clause is used and 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 replicas.

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 table will only be created if a
table with the same name does not already exist. If the table 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 the current session. Temporary tables are dropped when the session ends.
Temporary table names are specific to the session. They will not conflict with
other temporary tables from other sessions 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.

ROCKSDB temporary tables cannot be created by setting the
default_tmp_storage_engine system variable, or using CREATE TEMPORARY TABLE
LIKE. Before MariaDB 10.7, they could be specified, but would silently fail,
and a MyISAM table would be created instead. From MariaDB 10.7 an error is
returned. Explicitly creating a temporary table with ENGINE=ROCKSDB has never
been permitted.

CREATE TABLE ... LIKE
---------------------

Use the LIKE clause instead of a full table definition to create an empty
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.

LIKE does not preserve the TEMPORARY status of the original table. To make the
new table TEMPORARY as well, use CREATE TEMPORARY TABLE ... LIKE.

LIKE does not work with views, only base tables. Attempting to use it on a
view will result in an error:

CREATE VIEW v (mycol) AS SELECT 'abc';

CREATE TABLE v2 LIKE v;
ERROR 1347 (HY000): 'test.v' is not of type 'BASE TABLE'

The same version of the table storage format as found in the original table is
used for the new table.

CREATE TABLE ... LIKE performs the same checks as CREATE TABLE. So a statement
may fail if a change in the SQL_MODE renders it invalid. For example:

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


CREATE OR REPLACE TABLE y LIKE x;
ERROR 1067 (42000): Invalid default value for 'd'

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)]
  [ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]
  [AUTO_INCREMENT] [ZEROFILL] [UNIQUE [KEY] | [PRIMARY] KEY]
  [INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING]
  [COMMENT 'string'] [REF_SYSTEM_ID = value]
  [reference_definition]
 | data_type [GENERATED ALWAYS]
 AS [ ROW {START|END} [NOT NULL ENABLE] [[PRIMARY] KEY]
    | (expression) [VIRTUAL | PERSISTENT | STORED] ]
   [INVISIBLE] [UNIQUE [KEY]] [COMMENT 'string']
constraint_definition:
 CONSTRAINT [constraint_name] CHECK (expression)
Note: Until MariaDB 10.4, MariaDB accepts the shortcut format with a
REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that
syntax does nothing. For example:

CREATE TABLE b(for_key INT REFERENCES a(not_key));

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. However, only the syntax described below creates foreign
keys.

From MariaDB 10.5, MariaDB will attempt to apply the constraint. See Foreign
Keys examples.

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

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

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 (excluding AUTO_INCREMENT columns):

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.

It is possible to assign BLOB or TEXT columns a DEFAULT value. In versions
prior to MariaDB 10.2.1, assigning a default to these columns was not possible.

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 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. Use the SHOW FULL COLUMNS statement to see
column comments.

REF_SYSTEM_ID
-------------

REF_SYSTEM_ID can be used to specify Spatial Reference System IDs for spatial
data type columns. For example:

CREATE TABLE t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101);

Generated Columns