MySQL CREATE TABLE

Summary: in this tutorial, you will learn how to use the MySQL CREATE TABLE statement to create a new table in the database.

Introduction to MySQL CREATE TABLE statement

The CREATE TABLE statement allows you to create a new table in a database.

The following illustrates the basic syntax of the CREATE TABLE  statement:

CREATE TABLE [IF NOT EXISTS] table_name(
   column1 datatype constraints,
   column2 datatype constraints,
   ...
) ENGINE=storage_engine;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• table_name: This is the name of the table that you want to create.
• column1, column2, etc.: The names of the columns in the table.
• datatype: the data of each column such as INT, VARCHAR, DATE, etc.
• constraints: These are optional constraints such as NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY.

If you create a table with a name that already exists in the database, you’ll get an error. To avoid the error, you can use the IF NOT EXISTS option.

In MySQL, each table has a storage engine such as InnoDB or MyISAM. The ENGINE clause allows you to specify the storage engine of the table.

If you don’t explicitly specify a storage engine, MySQL will use the default storage engine which is InnoDB.

InnoDB became the default storage engine starting with MySQL version 5.5. The InnoDB storage engine offers several advantages of a relational database management system, including ACID transaction support, referential integrity, and crash recovery. In earlier versions, MySQL used MyISAM as the default storage engine.

MySQL CREATE TABLE statement examples

Let’s take some examples of creating new tables.

1) Basic CREATE TABLE statement example

The following example uses the CREATE TABLE statement to create a new table called tasks:

CREATE TABLE tasks (
    id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    start_date DATE,
    due_date DATE
);Code language: SQL (Structured Query Language) (sql)

The tasks table has four columns:

• The id is an INT column and serves as the primary key column.
• The title is a VARCHAR column and cannot be NULL.
• The start_date and end_date are the DATE column and can be NULL.

To execute the CREATE TABLE statement:

First, log in to the MySQL server using the mysql command from your terminal with an account that has CREATE privilege:

mysql -u root -p

It’ll prompt you for the password:

Enter password: ********

Next, create a new database called test:

CREATE DATABASE test;

If the database already exists, you can drop it first before executing the above statement:

DROP DATABASE test;

Then, select the test database to work with:

USE test;Code language: PHP (php)

After that, execute the CREATE TABLE statement:

CREATE TABLE tasks (
    id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    start_date DATE,
    due_date DATE
);Code language: PHP (php)

Finally, list all tables in the test database:

SHOW TABLES;

Output:

+----------------+
| Tables_in_test |
+----------------+
| tasks          |
+----------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

It shows the table tasks that we have created.

2) Creating a table with a foreign key example

Suppose each task has a checklist. To store the checklists of tasks, you can create a new table called checklists as follows:

CREATE TABLE checklists(
  id INT, 
  task_id INT, 
  title VARCHAR(255) NOT NULL, 
  is_completed BOOLEAN NOT NULL DEFAULT FALSE, 
  PRIMARY KEY (id, task_id), 
  FOREIGN KEY (task_id) 
      REFERENCES tasks (id) 
      ON UPDATE RESTRICT 
      ON DELETE CASCADE
);Code language: SQL (Structured Query Language) (sql)

The table checklists has a primary key that consists of two columns. Therefore, we need to use a table constraint to define the primary key:

PRIMARY KEY (id, task_id)Code language: SQL (Structured Query Language) (sql)

In addition, the task_id is the foreign key column that references the id column of the tasks table, therefore, we use a foreign key constraint to establish this relationship:

FOREIGN KEY (task_id) 
    REFERENCES tasks (id) 
    ON UPDATE RESTRICT 
    ON DELETE CASCADECode language: SQL (Structured Query Language) (sql)

Note that you will learn more about the foreign key constraint here.

Summary

• Use CREATE TABLE statement to create a new table.