MySQL CREATE TABLE with Examples

How to Create Tables in MySQL Using the CREATE TABLE Statement

In this tutorial, you'll learn how to create a table in MySQL using the MySQL CREATE statement.

CREATE TABLE Syntax

The syntax of the MySQL CREATE TABLE statement is as follows:

CREATE TABLE [IF NOT EXISTS] table_name(
column_name(s)
) ENGINE=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]

Remarks

Column_name: The name of the column that you want to create. Each column has a specific data type and maximum length.
NOT NULL: The column doesn't allow NULL if specified. Each record must contain a value for that column.
DEFAULT value: The default value of the column to add if not specified.
AUTO_INCREMENT: The column value is generated automatically by the system at the time MySQL adds a new record to a table. There can be only one AUTO_INCREMENT column per table.

Creating a table with primary key columns

To create a table with primary key columns, use the following syntax:

CREATE TABLE [IF NOT EXISTS] table_name(
column_name(s),
PRIMARY KEY (column_name1, column_name2, ...)
) ENGINE=storage_engine;

Creating a table with a column referenced to another table

To create a table with a column referenced to another table, use the following syntax:

CREATE TABLE [IF NOT EXISTS] table_name(
    column_name(s),
    PRIMARY KEY (column_name1, column_name2, ...),
    FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name)
) ENGINE=storage_engine;

Examples

Example 1: Creating a table without a primary key column

Let's say you want to create a table named customers, which consists of two columns, customer_id and cusotmer_name, without a primary key column.

The customers table doesn't have a primary key column.
The customer_id column is an INT data type and can't contain NULL values.
The customer_name column is a VARCHAR data type and can store up to 50 characters, and can also contain NULL values.

CREATE TABLE IF NOT EXISTS customers (
customer_id INT NOT NULL,
customer_name VARCHAR(50) NULL
) ENGINE = innodb;

MySQL allows creating a table without a primary key.

Example 2: Creating a table with a primary key

In this example, let's create a table that consists of a primary key column.

We're going to create a new table named customers that consists of three columns, customer_id, customer_name, and customer_group.

The customer table consists of one primary key column.
The customer_id column is the primary key and set as the AUTO_INCREMENT column of the table. The value is created automatically at the time a new record added to the table, if not specified.
The customer_name column is a VARCHAR data type and can store up to 50 characters, and can also contain NULL values.
The customer_group column is a VARCHAR data type and can store up to 40 characters, and can also contain NULL values. It defaults to Retail Customers, if not specified.
The active column is a TINYINT data type and can contain NULL values. It defaults to 1, if not specified.

The SQL statement is as follows:

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(50) NULL,
    customer_group VARCHAR(40) NULL DEFAULT "Retail Customers",
    active TINYINT NULL DEFAULT 1,
    PRIMARY KEY (customer_id)
) ENGINE = innodb;

Example 3: Creating tables with primary and foreign keys

In this example, let's create two tables, named customers and groups.

The customers table consists of three columns, customer_id, customer_name, and group_id. The customer_id is a primary key.

The groups table consists of two columns group_id and group_name. The group_id column is a primary key.

The group_id column of the customers table referenced the group_id of the groups table.

Creating a groups table:

CREATE TABLE IF NOT EXISTS groups (
group_id SMALLINT NOT NULL AUTO_INCREMENT,
group_name VARCHAR(150) NULL,
PRIMARY KEY (group_id)
) ENGINE = innodb;

Creating a customers table:

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(50) NULL,
    group_id SMALLINT NULL,
    PRIMARY KEY (customer_id),
    FOREIGN KEY (group_id) REFERENCES groups (group_id)
) ENGINE = innodb;

As you can see, the customer table consists of both primary key and foreign key.

Example 4: Creating tables with composite primary keys

In this example, I show you how to create composite primary keys by creating four tables, named customers, products, orders, and order_items.

The products table consists of three columns, product_id, product_name, and price.
The customers table consists of two columns customer_id and customer_name.
The orders table consists of three columns, order_id, customer_id, and order_number. The customer_id column referenced to the customer_id of the customers table.
The order_item consists of three columns, order_id, product_id, and unit_price. The product_id column referenced the product_id of the products table, and the order_id column referenced the order_id of the orders table. And, the product_id and order_id are the primary keys.

Creating the products table:

CREATE TABLE IF NOT EXISTS products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(50) NULL,
    price DECIMAL(2) NOT NULL,
    PRIMARY KEY (product_id)
) ENGINE = innodb;

Creating the customers table:

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(50) NULL,
   PRIMARY KEY (customer_id),
) ENGINE = innodb;

Creating the orders table:

CREATE TABLE IF NOT EXISTS orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_number VARCHAR(20) NOT NULL,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
) ENGINE = innodb;

Creating the order_items table:

CREATE TABLE IF NOT EXISTS order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    unit_price VARCHAR(255) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products (product_id),
    FOREIGN KEY (order_id) REFERENCES orders (order_id),
) ENGINE = innodb;

As you can see, the order_items table consists of two primary keys (order_id and product_id).

In this tutorial, you've learned how to create tables in MySQL using the CREATE TABLE statement. Also, you learned to create tables with primary key and foreign key columns.

Note that a database must exist before you can create tables.