Summary: in this tutorial, we will show you how to create new tables in a database using MySQL CREATE TABLE statement.
MySQL CREATE TABLE syntax
In order 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 statement in MySQL.
The following illustrates the syntax of the
CREATE TABLE statement in the simple form:
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 TABLEclause. The table name must be unique within a database. The
IF NOT EXISTSis an optional part of the statement 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 EXISTSin every
CREATE TABLEstatement for preventing from an error of creating a new table that already exists.
- Second, you specify a list of columns for the table in the
column_listsection. Columns are separated by a comma (,). We will show you how to define columns in more detail in the next section.
- Third, you need to specify the storage engine for the table in the
engineclause. You can use any storage engine such as InnoDB, MyISAM, HEAP, EXAMPLE, CSV, ARCHIVE, MERGE FEDERATED or NDBCLUSTER. If you don’t declare the storage engine explicitly, MySQL will use InnoDB by default.
InnoDB became the default storage engine since MySQL version 5.5. The InnoDB table type brings many benefits of relational database management system such as ACID transaction, referential integrity, and crash recovery. In 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[size] [NOT NULL|NULL] [DEFAULT value]
The most important components of the syntax above are:
column_namespecifies the name of the column. Each column has a specific data type and the size e.g.,
NULLindicates that the column accepts
NULLvalue or not.
DEFAULTvalue is used to specify the default value of the column.
AUTO_INCREMENTindicates that the value of the column is increased automatically whenever a new row is inserted into the table. Each table has one and only one
If you want to set particular columns of the table as the primary key, you use the following syntax:
PRIMARY KEY (col1,col2,...)
Example of MySQL CREATE TABLE statement
Let’s practice with an example of creating a new table named
tasks in our sample database as follows:
You can use the
CREATE TABLE statement to create the
tasks table as follows:
CREATE TABLE IF NOT EXISTS tasks (
task_id INT(11) NOT NULL AUTO_INCREMENT,
subject VARCHAR(45) DEFAULT NULL,
start_date DATE DEFAULT NULL,
end_date DATE DEFAULT NULL,
description VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (task_id)
In this tutorial, you have learned how to use MySQL CREATE TABLE statement to create a new table in a database.