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, or if you specify DEFAULT for that column. You cannot usually 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.

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.

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. 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 primary or unique key creates an index on that column. Specifying a key in the column definition is equivalent to specifying a single-column key separately. See Indexes below.

You can provide a comment for each column using the COMMENT clause. Use the SHOW FULL COLUMNS statement to see column comments.

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.