MySQL CREATE TABLE with Examples

How to Create Tables in MySQL Using the CREATE TABLE Statement

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

Learn more:

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;

CREATE TABLE [IF NOT EXISTS]: If "IF NOT EXISTS" is specified, MySQL will not create a table if it already exists.

To define a column for a table in a CREATE TABLE statement, use the following syntax:

column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]

Remarks

Column_name: The name of the column to be created. Each column has a specific data type and a maximum length.
NOT NULL: A column that does not allow NULL when specified. Each record must contain the value of the column.
DEFAULT value. If not specified, the default value for the column to add.
AUTO_INCREMENT. the value of the column is automatically generated by the system when MySQL adds a new row to the table, and there is 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 that reference another table

To create a table with a column that references 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: Create a table without a primary key column

Suppose you want to create a table named "customers" that consists of two columns, customer_id and cusotmer_name, and does not have a primary key column.

The "customers" table does not have a primary key column.
The customer_id column is an "INT" data type and cannot contain null values.
The customer_name column is of the VARCHAR data type and can store up to 50 characters and NULL values.

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

MySQL allows you to create tables without a primary key.

Example 2: Create a table with a primary key

In this example, let's create a table consisting of primary key columns.

Let's 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 it is an AUTO_INCREMENT column. If not specified, the value is automatically created when a new row is added to the table.
The customer_name column is a VARCHAR data type that can store up to 50 characters and NULL values.
The customer_group column is a VARCHAR data type and can store up to 40 characters. If not specified, it defaults to "Retail Customers."
The active column is of the TINYINT data type and can contain NULL values. If not specified, it defaults to 1.

The SQL statement to create the "customers" table 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: Create tables with primary and foreign keys

In this example, we will create two tables named customers and groups.

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

The "groups" table consists of two columns: group_id and group_name. The group_id column is the primary key.

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

Creating the "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 the "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;

After you execute the above SQL code, you will see that the "customers" table consists of both primary key and foreign key.

Example 4: Create tables with composite primary keys

This example shows 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 references the customer_id of the "customers" table.
The "order_items" table 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 product_id and order_id are 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;

After you execute the above SQL code, you will see that the order_items table consists of two primary keys: order_id and product_id.

Summary

In this tutorial, you have learned how to create a table in MySQL using the CREATE TABLE statement.

You also learned to create a table without a primary key column, with a primary key column, and with primary key and foreign key columns.

Note that the database must exist before you can create the table.