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.