CREATE TABLE

You are viewing an old version of this article. View the current version here.

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_option]... [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_option]... [partition_options] select_statement CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_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. You must have the CREATE privilege for the table or on the database to create a table.

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

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.

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 may conflict with other temporary tables from other sessions or shadow names of non-temporary tables. You must have the CREATE TEMPORARY TABLES privilege on the database to create temporary tables.

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.

The CREATE TABLE statement automatically commits the current transaction, except when using the TEMPORARY keyword.

New in MariaDB 5.3

Microsecond precision can be between 0-6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.

Table Definitions

create_definition: { col_name column_definition | index_definition | CHECK (expr) }

column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] [reference_definition] | data_type [GENERATED ALWAYS] AS (expression) {VIRTUAL | PERSISTENT} [UNIQUE [KEY]] [COMMENT 'string']

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.

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.

Specify a default value using the DEFAULT clause. The default value will be used if you INSERT a row without specifying a value for that column. You cannot generally provide an expression or function to evaluate at insertion time. You must 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.

Indexes

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

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

Table Options

table_option: 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} | 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} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] | UNION [=] (tbl_name[,tbl_name]...)

Partitions

partition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(column_list) | RANGE(expr) | LIST(expr) } [PARTITIONS num] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(column_list) } [SUBPARTITIONS num] ] [(partition_definition [, partition_definition] ...)]

partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [NODEGROUP [=] node_group_id] [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition: SUBPARTITION logical_name [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [NODEGROUP [=] node_group_id]

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.