totn MariaDB

MariaDB: CREATE TABLE Statement

This MariaDB tutorial explains how to use the MariaDB CREATE TABLE statement with syntax and examples.

Description

The MariaDB CREATE TABLE statement allows you to create and define a table.

Syntax

In its simplest form, the syntax for the CREATE TABLE statement in MariaDB is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

However, the full syntax for the MariaDB CREATE TABLE statement is:

CREATE [ OR REPLACE ] [ TEMPORARY ] TABLE [IF NOT EXISTS] table_name
( 
  column1 datatype [ NULL | NOT NULL]
                   [ DEFAULT default_value ]
                   [ AUTO_INCREMENT ]
                   [ UNIQUE KEY | PRIMARY KEY ]
                   [ COMMENT 'string' ],

  column2 datatype [ NULL | NOT NULL]
                   [ DEFAULT default_value ]
                   [ AUTO_INCREMENT ]
                   [ UNIQUE KEY | PRIMARY KEY ]
                   [ COMMENT 'string' ],
  ...

  | [CONSTRAINT [constraint_name]] PRIMARY KEY [ USING BTREE | HASH ] (index_col_name, ...)

  | [INDEX | KEY] index_name [ USING BTREE | HASH ] (index_col_name, ...)

  | [CONSTRAINT [constraint_name]] UNIQUE [ INDEX | KEY ] 
        [ index_name ] [ USING BTREE | HASH ] (index_col_name, ...)

  | {FULLTEXT | SPATIAL} [ INDEX | KEY] index_name (index_col_name, ...)

  | [CONSTRAINT [constraint_name]] 
        FOREIGN KEY index_name (index_col_name, ...)
        REFERENCES another_table_name (index_col_name, ...)
        [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
        [ ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
        [ ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]

  | CHECK (expression)

    {ENGINE | TYPE} = engine_name
  | AUTO_INCREMENT = value
  | AVG_ROW_LENGTH = value
  | [DEFAULT] CHARACTER SET = charset_name
  | CHECKSUM = {0 | 1}
  | [DEFAULT] COLLATE = collation_name
  | COMMENT = 'string'
  | DATA DIRECTORY = 'absolute path'
  | DELAY_KEY_WRITE = { 0 | 1 }
  | INDEX DIRECTORY = 'absolute path'
  | INSERT_METHOD = { NO | FIRST | LAST }
  | MAX_ROWS = value
  | MIN_ROWS = value
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | RAID_TYPE = { 1 | STRIPED | RAIDO }
       RAID_CHUNKS = value
       RAID_CHUNKSIZE = value
  | ROW_FORMAT = {DEFAULT | DYNAMIC | FIXED | COMPRESSED}
  | UNION = (table1, ... )
);

Parameters or Arguments

OR REPLACE
Optional. If the table already exists, the table definition will be replaced with this new CREATE TABLE statement.
TEMPORARY
Optional. It specifies that the table is a temporary table.
IF NOT EXISTS
Optional. If specified, the CREATE TABLE statement will not raise an error if the table already exists.
table_name
The name of the table that you wish to create.
column1, column2
The columns that you wish to create in the table.
datatype

The data type for the column and can be one of the following:

Value
CHAR [ (length) ] [ CHARACTER SET charset_name ] [ COLLATE collation_name ]
VARCHAR [ (length) ] [ CHARACTER SET charset_name ] [ COLLATE collation_name ]
BINARY [ (length) ]
VARBINARY (length)
DATE
TIME
TIMESTAMP
DATETIME
YEAR
TINYINT [ (length) ] [ UNSIGNED ] [ ZEROFILL ]
SMALLINT [ (length) ] [ UNSIGNED ] [ ZEROFILL ]
MEDIUMINT [ (length) ] [ UNSIGNED ] [ ZEROFILL ]
INT [ (length) ] [ UNSIGNED ] [ ZEROFILL ]
INTEGER [ (length) ] [ UNSIGNED ] [ ZEROFILL ]
BIGINT [ (length) ] [ UNSIGNED ] [ ZEROFILL ]
REAL [ (length, decimals) ] [ UNSIGNED ] [ ZEROFILL ]
DOUBLE [ (length, decimals) ] [ UNSIGNED ] [ ZEROFILL ]
FLOAT [ (length, decimals) ] [ UNSIGNED ] [ ZEROFILL ]
DECIMAL [ (length, [ decimals ]) ] [ UNSIGNED ] [ ZEROFILL ]
NUMERIC [ (length, [ decimals ]) ] [ UNSIGNED ] [ ZEROFILL ]
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT [ BINARY ] [ CHARACTER SET charset_name ] [ COLLATE collation_name ]
TEXT [ BINARY ] [ CHARACTER SET charset_name ] [ COLLATE collation_name ]
MEDIUMTEXT [ BINARY ] [ CHARACTER SET charset_name ] [ COLLATE collation_name ]
LONGTEXT [ BINARY ] [ CHARACTER SET charset_name ] [ COLLATE collation_name ]
ENUM(value1, value2, ...) [ CHARACTER SET charset_name ] [ COLLATE collation_name ]
NULL or NOT NULL
Each column should be defined as NULL or NOT NULL. If this parameter is omitted, the database assumes NULL as the default.
DEFAULT default_value
Optional. It is the value to assign to the column if left blank or NULL.
AUTO_INCREMENT
Optional. It sets the column to be an autonumber field.
constraint_name
The name of the constraint if you define a primary key, unique constraint or foreign key.
index_col_name

It is the following syntax:

column_name [ (length) ] [ ASC | DESC ]

Note

  • There can only be one column in a table that is set as AUTO_INCREMENT and this column must be the primary key.

Example

Let's look at a MariaDB CREATE TABLE example.

CREATE TABLE websites
( website_id INT(11) NOT NULL AUTO_INCREMENT,
  website_name VARCHAR(25) NOT NULL,
  server_name VARCHAR(20),
  creation_date DATE,
  CONSTRAINT websites_pk PRIMARY KEY (website_id)
);

This MariaDB CREATE TABLE example creates a table called websites which has 4 columns and one primary key:

  • The first column is called website_id which is created as an INT datatype (maximum 11 digits in length) and can not contain NULL values. It is set as an AUTO_INCREMENT field which means that it is an autonumber field (starting at 1, and incrementing by 1, unless otherwise specified.)
  • The second column is called website_name which is a VARCHAR datatype (maximum 25 characters in length) and can not contain NULL values.
  • The third column is called server_name which is a VARCHAR datatype (maximum 20 characters in length) and can contain NULL values.
  • The fourth column is called creation_date which is a DATE datatype and can contain NULL values.
  • The primary key is called websites_pk and is set to the website_id column.

Next, let's create a table in MariaDB that has a DEFAULT VALUE.

CREATE TABLE pages
( page_id INT(11) NOT NULL AUTO_INCREMENT,
  website_id INT(11) NOT NULL,
  host_name VARCHAR(45) NOT NULL DEFAULT 'TBD',
  CONSTRAINT pages_pk PRIMARY KEY (page_id)
);

This MariaDB CREATE TABLE example creates a table called pages which has 3 columns and one primary key:

  • The first column is called page_id which is created as an INT datatype (maximum 11 digits in length) and can not contain NULL values. It is set as an AUTO_INCREMENT field.
  • The second column is called website_id which is an INT datatype (maximum 11 characters in length) and can not contain NULL values.
  • The third column is called host_name which is a VARCHAR datatype (maximum 45 characters in length) and can not contain NULL values. If no value is provided for this column, the DEFAULT VALUE will be 'TBD'.
  • The primary key is called pages_pk and is set to the page_id column.