Summary: in this tutorial, we will show you how to create a new table in a database by using the MySQL CREATE TABLE statement.
MySQL CREATE TABLE syntax
To create a new table within a database, you use the MySQL
CREATE TABLE statement. The
CREATE TABLE statement is one of the most complex statements in MySQL.
The following illustrates the syntax of the simplified version of the
CREATE TABLE statement:
CREATE TABLE [IF NOT EXISTS] table_name(
Let’s examine the syntax in greater detail.
First, you specify the name of the table that you want to create after the
CREATE TABLE clause. The table name must be unique within a database. The
IF NOT EXISTS is an optional clause that allows you to check if the table that you are creating already exists in the database. If this is the case, MySQL will ignore the whole statement and will not create any new table. It is highly recommended that you use
IF NOT EXISTS in every
CREATE TABLE statement to avoid an error of creating a new table that already exists.
Second, you specify a list of columns for the table in the
column_list section, columns are separated by commas.
Third, you can optionally specify the storage engine for the table in the
ENGINE clause. You can use any storage engine such as InnoDB and MyISAM. If you don’t explicitly declare the storage engine, MySQL will use InnoDB by default.
InnoDB became the default storage engine since MySQL version 5.5. The InnoDB storage engine brings many benefits of a relational database management system such as ACID transaction, referential integrity, and crash recovery. In the previous versions, MySQL used MyISAM as the default storage engine.
To define a column for the table in the
CREATE TABLE statement, you use the following syntax:
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
The most important components of the syntax above are:
column_namespecifies the name of the column. Each column has a specific data type and maximum length e.g.,
NOT NULLindicates that the column does not allow
DEFAULT valueis used to specify the default value of the column.
AUTO_INCREMENTindicates that the value of the column is generated by one automatically whenever a new row is inserted into the table. Each table has one and only one
If you want to set a column or a set of columns as the primary key, you use the following syntax:
PRIMARY KEY (col1,col2,...)
MySQL CREATE TABLE statement example
The following statement creates a new table named
CREATE TABLE IF NOT EXISTS tasks (
task_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
status TINYINT NOT NULL,
priority TINYINT NOT NULL,
PRIMARY KEY (task_id)
The tasks table has the following columns:
task_idis an auto-increment column. If you use the
INSERTstatement to add a new row to the table without specifying a value for the task_id column, the task_id column will take an auto-generated integer beginning with one. The
task_idis the primary key column.
titlecolumn is a variable character string column whose maximum length is 255. It means that you cannot insert a string whose length is greater than 255 into this column. The
NOT NULLindicates that the column must have a value. In other words, you have to provide a value when you insert or update this column.
due_dateare date columns which accept NULL.
TINYINTcolumns which do not allow NULL.
descriptioncolumn is a
TEXTcolumn that accepts NULL.
In this tutorial, you have learned how to use MySQL CREATE TABLE statement to create a new table in a database.