This tutorial explains how to use the MySQL CREATE TABLE statement with syntax and examples.
The MySQL CREATE TABLE statement allows you to create and define a table.
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, ... )
);
Optional. It specifies that the table is a temporary table.
Optional. If specified, the CREATE TABLE statement will not raise an error if the tables already exists.
The name of the table that you wish to create.
The columns that you wish to create in the table.
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 ] |
Each column should be defined as NULL or NOT NULL. If this parameter is omitted, the database assumes NULL as the default.
Optional. It is the value to assign to the column if left blank or NULL.
Optional. It sets the column to be an autonumber field.
Optional. The name of the constraint if you define a primary key, unique constraint or foreign key.
Optional. It is the following syntax:
column_name [ (length) ] [ ASC | DESC ]
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:
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: