Mysql Create Table Statement

MySQL: CREATE TABLE Statement

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

Description

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

Syntax

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

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

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

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

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

Optional. The name of the constraint if you define a primary key, unique constraint or foreign key.

index_col_name

Optional. 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 MySQL CREATE TABLE example.

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

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

  • The first column is called contact_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 last_name which is a VARCHAR datatype (maximum 30 characters in length) and can not contain NULL values.
  • The third column is called first_name which is a VARCHAR datatype (maximum 25 characters in length) and can contain NULL values.
  • The fourth column is called birthday which is a DATE datatype and can contain NULL values.
  • The primary key is called contacts_pk and is set to the contact_id column.

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

CREATE TABLE suppliers
( supplier_id INT(11) NOT NULL AUTO_INCREMENT,
  supplier_name VARCHAR(50) NOT NULL,
  account_rep VARCHAR(30) NOT NULL DEFAULT 'TBD',
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

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

  • The first column is called supplier_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 supplier_name which is a VARCHAR datatype (maximum 50 characters in length) and can not contain NULL values.
  • The third column is called account_rep which is a VARCHAR datatype (maximum 30 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 suppliers_pk and is set to the supplier_id column.

Copyright 2020. All Rights Reserved