MySQL CREATE TABLE Guide with Examples

CREATE TABLE adds a new, empty table to a MySQL database, with a name and a data type for each column. For example, you can create a customers table with a column for each customer's email address.

Every CREATE TABLE statement has the same shape:

CREATE TABLE table_name (
    column_name data_type constraints,
    ...
    column_name data_type constraints
);

• table_name names the new table.
• column_name names one column.
• data_type is the kind of value the column holds, such as INT, VARCHAR(100) or DATE.
• constraints are optional rules for the column's values, such as NOT NULL.
• A comma goes between two columns. A comma after the last column fails with ERROR 1064, a syntax error near the closing parenthesis.

The examples on this page use this customers table:

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(100) NOT NULL UNIQUE,
    full_name   VARCHAR(100) NOT NULL,
    status      VARCHAR(10) NOT NULL DEFAULT 'active'
);

Every statement here ran on MySQL 9.1.0 and 8.0.29 with the same output. The manual links go to MySQL 8.4.

Create the table from the mysql client

Log in with an account that has the CREATE privilege, such as root:

mysql -u root -p

To give the privilege to another account, see MySQL CREATE USER and GRANT. Then select the database that will hold the table. To create one first, see MySQL CREATE DATABASE.

USE shop;

Run the CREATE TABLE customers statement from the top of the page. Then list the tables and the new table's columns:

SHOW TABLES;
DESCRIBE customers;

The heading Tables_in_shop names the database. In DESCRIBE, PRI marks the primary key and UNI the unique key.

SHOW CREATE TABLE customers prints the statement as MySQL stored it, with the defaults that MySQL chose at the end: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci.

Without USE, CREATE TABLE fails with ERROR 1046 (3D000): No database selected.

PRIMARY KEY and AUTO_INCREMENT

PRIMARY KEY marks the column that identifies each row, so its values are unique and never NULL. When an insert leaves out an AUTO_INCREMENT column, MySQL fills it with the next number.

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Ana Lima'), ('[email protected]', 'Ben Cole');

SELECT customer_id, email, full_name FROM customers;

The insert named no customer_id, so MySQL numbered the rows: Ana got 1, and Ben got 2.

NOT NULL

NOT NULL makes a column refuse NULL, the marker for a missing value. For example, customers refuses a customer without a full name:

INSERT INTO customers (email) VALUES ('[email protected]');

ERROR 1364 (HY000): Field 'full_name' doesn't have a default value

The insert gave no full_name, and full_name has no DEFAULT to fill in. A column without NOT NULL accepts NULL.

MySQL refuses the row only in strict SQL mode, on by default in MySQL 8.4. With strict mode off, the insert passes with a warning, and full_name holds an empty string.

DEFAULT

DEFAULT sets the value that a column takes when an insert leaves it out. For example, every new customer starts with the status 'active'.

INSERT INTO customers (email, full_name, status)
VALUES ('[email protected]', 'Cy Park', 'closed');

SELECT * FROM customers;

The earlier insert of Ana and Ben left status out, so both rows got 'active'. Cy's insert gave 'closed' instead. For a value that MySQL computes as each row goes in, see MySQL CREATE TRIGGER.

UNIQUE

UNIQUE makes a column refuse a value that another row already has. For example, two customers can't share an email address:

INSERT INTO customers (email, full_name) VALUES ('[email protected]', 'Ana Silva');

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'customers.email'

Ana Lima already has [email protected], so MySQL refused the second row.

FOREIGN KEY

FOREIGN KEY makes a column accept only values that exist in a column of another table. For example, every order in an orders table must belong to an existing customer. Create orders, then insert two orders:

CREATE TABLE orders (
    order_id    INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on  DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

INSERT INTO orders (customer_id, ordered_on) VALUES (1, '2026-11-12');
INSERT INTO orders (customer_id, ordered_on) VALUES (99, '2026-11-12');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

MySQL refused the order for customer 99, because no customer has that id. The order for customer 1 went in:

SELECT * FROM orders;

For what happens to the orders of a deleted customer, see What is a foreign key. To remove a constraint, see MySQL DROP CONSTRAINT.

IF NOT EXISTS

IF NOT EXISTS makes CREATE TABLE do nothing when a table of that name already exists. For example, a setup script can run twice without an error.

CREATE TABLE IF NOT EXISTS customers (customer_id INT);
SHOW WARNINGS;

MySQL created nothing and reported a note. Without IF NOT EXISTS, the statement fails with ERROR 1050 (42S01): Table 'customers' already exists.

IF NOT EXISTS compares only the name. The statement above asks for one column, yet customers keeps its four.

CREATE TABLE LIKE and AS SELECT

CREATE TABLE ... LIKE creates an empty table with the columns and keys of another table. CREATE TABLE ... AS SELECT creates a table from a query's rows. For example, you can copy customers and orders before you change them:

CREATE TABLE customers_empty LIKE customers;
CREATE TABLE customers_copy AS SELECT * FROM customers;
CREATE TABLE orders_empty LIKE orders;
CREATE TABLE orders_copy AS SELECT * FROM orders;

SHOW CREATE TABLE on each copy shows what the copy kept:

customers_empty has no rows, and keeps the primary key and the unique key on email. customers_copy has the three customers, without any key.

Neither copy of orders has the foreign key.

What goes wrong

AUTO_INCREMENT without a key

An AUTO_INCREMENT column needs an index, and a table has at most one such column. The column doesn't have to be the primary key:

CREATE TABLE bad_counter (n INT AUTO_INCREMENT, label VARCHAR(20));

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Add PRIMARY KEY or UNIQUE to the column.

The referenced table doesn't exist yet

If customers doesn't exist, the orders statement fails:

ERROR 1824 (HY000): Failed to open the referenced table 'customers'

Create customers before orders.

Create the same table in DbSchema

DbSchema, a MySQL client and visual designer, creates the table from dialogs. Connect DbSchema to your MySQL database, click an empty spot on the diagram, and choose Table → Create Table in the toolbar. Type the name in Table Name, then press Add in the Columns tab for each column, as Tables, Columns and Indexes shows.

The column dialog sets the constraints with checkboxes and fields: Primary Key, Mandatory ( Not Null ), Default, and Identity. For MySQL, DbSchema writes Identity as AUTO_INCREMENT.

While DbSchema is connected, pressing OK creates the table in MySQL at once. Offline, the table stays in DbSchema's model, its copy of the schema, until you synchronize the two.

Download DbSchema from https://dbschema.com/download.html, connect it to your MySQL database, and add your next table from the diagram. The free Community Edition covers creating tables in a connected database. Saving the model to a file and synchronizing it with the database need DbSchema Pro.