How to Create a Table in MySQL: Syntax, Examples, and Best Practices

Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.

Use CREATE TABLE to define a MySQL table’s columns, data types, keys, and rules. First select a database, then create the table and inspect its definition. The examples below target MySQL 8.4; syntax and behavior may differ in older MySQL releases, MariaDB, and other compatible servers.

USE shop;

CREATE TABLE customers (
    customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (customer_id),
    UNIQUE KEY uq_customers_email (email)
) ENGINE = InnoDB;

Before you create a table

You need a running MySQL server, a client or database tool such as the mysql command-line client or MySQL Workbench, and an account with the CREATE privilege for the database. Check which server you are connected to with:

#	Preview	Product	Price
1		MySQL Crash Course: A Hands-on Introduction to Database Development	$34.52	Buy on Amazon
2		Learning MySQL: Get a Handle on Your Data	$36.38	Buy on Amazon
3		MySQL Cookbook: Solutions for Database Developers and Administrators	$56.73	Buy on Amazon
4		Database Programming Using Python and MySQL	$8.00	Buy on Amazon
5		PHP & MySQL: Server-side Web Development	$27.19	Buy on Amazon

SELECT VERSION();

This article uses the MySQL 8.4 Reference Manual as its syntax baseline. Do not assume every example behaves identically on MySQL 5.7, MariaDB, or another MySQL-compatible service. In MySQL 8.4, InnoDB is the default storage engine unless server configuration or the statement specifies otherwise. MySQL 8.4: CREATE TABLE

1. Create or select a database

A table belongs to a database. In a fresh setup, create one and select it:

#1 Best Overall

Sale

MySQL Crash Course: A Hands-on Introduction to Database Development

$34.52

Buy on Amazon

CREATE DATABASE IF NOT EXISTS inventory
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

USE inventory;

IF NOT EXISTS avoids an error if the database already exists, but it does not verify or alter that database’s settings. utf8mb4 is a general-purpose Unicode character set; the collation determines text comparison and sorting. Choose a collation compatible with your server version and language requirements rather than assuming one is right for every deployment. CREATE SCHEMA is a synonym for CREATE DATABASE in MySQL. MySQL 8.4: CREATE DATABASE

For an existing database, you can inspect visible databases and select one:

SHOW DATABASES;
USE inventory;

The databases shown depend on your privileges. USE changes the default database for subsequent statements in the current session. Alternatively, qualify a table name with its database, such as inventory.customers, so a script does not depend on which database is selected. MySQL 8.4: USE

2. Understand the CREATE TABLE statement

The general shape is:

CREATE TABLE [IF NOT EXISTS] table_name (
    column_definition,
    table_constraint,
    index_definition
) table_options;

A table stores related records as rows. Columns describe each record’s attributes, and data types constrain the kinds of values they can hold. Constraints enforce rules such as required values or uniqueness; indexes help MySQL find rows and enforce certain constraints.

For example, a customer table might contain an ID, email, name, and creation time. The primary key identifies each row; a separate unique key prevents two rows from sharing an email address.

Definitions inside the parentheses are comma-separated. Do not leave a comma after the final definition. The semicolon terminates the statement in most SQL clients.

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

This minimal table is valid, but it leaves important design choices unresolved: the columns are nullable by default unless another rule applies, there is no primary key, and no business uniqueness or validation rules are defined.

3. Choose columns and data types deliberately

A column definition typically gives a name and type, then may add attributes such as nullability, a default, or auto-increment behavior:

Rank #2

Sale

Learning MySQL: Get a Handle on Your Data

$36.38

Buy on Amazon

column_name data_type [NULL | NOT NULL] [DEFAULT value] [AUTO_INCREMENT]

Common starting points:

MySQL supports additional numeric, string, binary, date/time, JSON, spatial, ENUM, and SET types. MySQL 8.4 numeric types · MySQL 8.4 features and data types

Choose signed versus UNSIGNED intentionally. Use UNSIGNED when negative values are not meaningful and the corresponding wider nonnegative range is useful. Do not use FLOAT or DOUBLE for exact currency calculations.

4. Define keys, constraints, and defaults

Primary key and AUTO_INCREMENT

A primary key uniquely identifies rows and cannot contain NULL. A table has one primary key, which can consist of one or more columns. A common integer identifier is declared like this:

customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (customer_id)

MySQL supplies a generated value when an insert omits the auto-increment column. The values are not a gapless sequence: deletes, failed or rolled-back inserts, and concurrency can leave gaps. An auto-increment ID also does not establish business ordering or prevent duplicate business values.

A surrogate key such as an integer ID is stable and convenient for joins, but still add a separate uniqueness rule for a business identifier such as an email, SKU, or order number. A natural key can be appropriate when it is stable, compact, and genuinely identifies the record; changing or wide natural keys can make foreign keys and indexes more costly. In InnoDB, secondary indexes include the primary-key columns, so an unnecessarily wide primary key has a storage cost.

NOT NULL, DEFAULT, and checks

Unless another rule applies, omitting both NULL and NOT NULL generally leaves a column nullable. Use NOT NULL when absence is invalid. A default is used when an insert omits the column; it does not reject other values supplied by the caller.

status VARCHAR(20) NOT NULL DEFAULT 'pending',
quantity INT UNSIGNED NOT NULL DEFAULT 1,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

For simple row-level rules, MySQL 8.4 supports CHECK constraints:

CREATE TABLE line_items (
    line_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (line_item_id),
    CHECK (quantity > 0),
    CHECK (unit_price >= 0)
) ENGINE = InnoDB;

Defaults can be sensitive to data type, SQL mode, and server version; expression defaults also have syntax requirements. Consult the version-specific manual for nontrivial defaults. MySQL 8.4: Data type default values

Rank #3

Sale

MySQL Cookbook: Solutions for Database Developers and Administrators

$56.73

Buy on Amazon

Unique keys

A unique key prevents duplicate non-NULL values for its indexed key. It differs from a primary key: a table can have multiple unique keys, while only one key is designated the primary key. Do not assume a unique key means exactly one row can contain NULL; decide whether the column itself should also be NOT NULL.

CREATE TABLE accounts (
    account_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (account_id),
    CONSTRAINT uq_accounts_email UNIQUE (email)
);

Foreign keys and relationships

A foreign key links a child row to a referenced parent key. For example, each order below must refer to a customer:

CREATE TABLE customers (
    customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    PRIMARY KEY (customer_id)
) ENGINE = InnoDB;

CREATE TABLE orders (
    order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id BIGINT UNSIGNED NOT NULL,
    ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id),
    KEY idx_orders_customer_id (customer_id),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE = InnoDB;

Declare foreign keys as table-level definitions. The child and referenced columns must have compatible types and attributes, and the referenced columns should normally be a primary or unique, non-null key. Foreign-key columns need an index; MySQL can create one when necessary. In MySQL, enforcement depends on the storage engine: InnoDB and NDB enforce foreign keys, while other engines may parse and ignore the syntax. MySQL 8.4: Foreign key constraints

Choose referential actions to match the data model. ON DELETE CASCADE deletes dependent child rows when a parent is deleted; that can be useful, but may remove more data than intended. RESTRICT or NO ACTION prevents deleting a referenced parent while dependents remain. SET NULL requires a nullable child column. Do not copy an action without considering what deletion or update should mean in your application.

Ordinary indexes

Primary and unique keys create indexes. Add ordinary indexes for real lookup, join, filtering, or sorting patterns—not automatically to every column.

CREATE TABLE articles (
    article_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug VARCHAR(200) NOT NULL,
    author_id BIGINT UNSIGNED NOT NULL,
    published_at DATETIME NULL,
    PRIMARY KEY (article_id),
    UNIQUE KEY uq_articles_slug (slug),
    KEY idx_articles_author_id (author_id),
    KEY idx_articles_published_at (published_at)
);

Indexes can speed reads but consume storage and make inserts and updates more expensive. For a composite index, column order matters because it affects which query prefixes can use it. Indexing long TEXT or BLOB values may require a prefix length. MySQL 8.4 does not directly index a JSON column; a generated column can expose a scalar JSON value for indexing.

5. Create the table, then verify it

Here is a complete beginner workflow in the MySQL client. Start the client from your shell (this is a shell command, not SQL):

mysql -u your_username -p

Enter your password when prompted. To connect to a specific host and database, you can instead use:

Rank #4

Database Programming Using Python and MySQL

$8.00

Buy on Amazon

mysql -h hostname -u your_username -p database_name

Then run the SQL in the client:

SELECT VERSION();
SHOW DATABASES;
USE shop;

CREATE TABLE customers (
    customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (customer_id),
    UNIQUE KEY uq_customers_email (email)
) ENGINE = InnoDB;

SHOW TABLES;
DESCRIBE customers;
SHOW CREATE TABLE customersG

DESCRIBE gives a convenient column summary. SHOW CREATE TABLE reveals MySQL’s actual normalized definition, including indexes, constraints, defaults, and table options. Use it to check the schema you created rather than relying on memory or intent. MySQL 8.4: SHOW CREATE TABLE

Test both an ordinary insert and a rule the table is meant to enforce:

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Alex Smith');

SELECT * FROM customers;

-- This should be rejected because the email is already present.
INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Another Person');

A duplicate-value error on the second insert is expected: the unique key is working. It is different from an error saying the table name already exists.

6. Useful CREATE TABLE variations

Use IF NOT EXISTS

CREATE TABLE IF NOT EXISTS customers (
    customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (customer_id),
    UNIQUE KEY uq_customers_email (email)
);

This suppresses the error when a table with that name already exists. It does not compare definitions, add missing columns, or repair indexes. Inspect the existing object before choosing whether to retain or change it.

Specify the database in the table name

CREATE TABLE shop.customers (
    customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (customer_id)
);

This avoids relying on the session’s currently selected database.

Copy a table definition without its rows

CREATE TABLE customers_backup LIKE customers;

CREATE TABLE ... LIKE makes an empty table based on the original definition and copies its columns and indexes. It is useful for a structural copy, not a data backup. MySQL 8.4: CREATE TABLE

Create a table from query results

CREATE TABLE recent_orders AS
SELECT order_id, customer_id, ordered_at
FROM orders
WHERE ordered_at >= '2026-01-01';

This derives columns from the query output, so it is convenient for a result set or snapshot but is not a complete schema-copy method. It may not preserve attributes such as AUTO_INCREMENT, indexes, foreign keys, or intended precision and length. Add required constraints and indexes explicitly; the table options belong before AS SELECT, not after the query. For a deliberate schema copy, use explicit DDL or LIKE, then copy data separately as appropriate. MySQL 8.4: CREATE TABLE … SELECT

Create a temporary table

CREATE TEMPORARY TABLE session_totals (
    customer_id BIGINT UNSIGNED NOT NULL,
    total DECIMAL(12,2) NOT NULL
);

A temporary table is session-scoped and intended for intermediate work, not permanent application data.

Best Value

Sale

PHP & MySQL: Server-side Web Development

$27.19

Buy on Amazon

7. Change a table after creation

CREATE TABLE defines an initial schema. Use ALTER TABLE for later changes:

ALTER TABLE customers
ADD COLUMN phone VARCHAR(30) NULL;

ALTER TABLE customers
ADD INDEX idx_customers_phone (phone);

For a foreign key, first ensure the child and parent columns are compatible and the referenced table and key exist:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

Review schema changes, test them against representative data, and apply them through a migration process for deployed applications rather than making untracked production edits. MySQL 8.4: Data definition statements

8. Troubleshoot common errors

“No database selected”

The session has not selected a default database and the table name is unqualified. Run USE shop; first, or create the fully qualified name shop.customers.

“Table already exists”

Inspect it before changing anything:

SHOW TABLES;
SHOW CREATE TABLE customersG;

Decide whether to keep it, alter it, or use a different name. Do not use DROP TABLE as a casual fix: it removes the table and its data.

Syntax error near the end of a definition

A common cause is an extra comma after the final column:

-- Incorrect
CREATE TABLE users (
    id INT,
    name VARCHAR(100),
);

-- Correct
CREATE TABLE users (
    id INT,
    name VARCHAR(100)
);

Also check for a missing comma between definitions, a type unsupported by your server version, a foreign key in the wrong position, or table options placed after a SELECT in a CREATE TABLE ... SELECT statement.

Foreign-key creation fails

Inspect both definitions with SHOW CREATE TABLE. Confirm that the tables use an engine that enforces foreign keys, the referenced table and key exist, the referenced columns are indexed, and child and parent types and attributes are compatible. Check column order and names, too. If using ON DELETE SET NULL, the child column must allow NULL. MySQL documents engine and key requirements in its foreign-key reference.

Unexpected NULL values or defaults

If a column should be required, verify that it was created with NOT NULL. Use SHOW CREATE TABLE table_nameG to inspect the real definition. For a default-value error, check the value’s type, date validity, server version, SQL mode, and any expression-default syntax.

Identifier or reserved-word errors

Avoid names such as order, group, key, and condition. Prefer descriptive names like order_id or customer_group. If an awkward identifier cannot be changed, quote it with backticks:

CREATE TABLE `order` (
    `key` INT NOT NULL
);

Backticks are an escape mechanism, not a reason to choose confusing names.

Quick Recap

SaleBestseller No. 1

MySQL Crash Course: A Hands-on Introduction to Database Development

$34.52

SaleBestseller No. 2

Learning MySQL: Get a Handle on Your Data

$36.38

SaleBestseller No. 3

MySQL Cookbook: Solutions for Database Developers and Administrators

$56.73

Bestseller No. 4

Database Programming Using Python and MySQL

$8.00

SaleBestseller No. 5

PHP & MySQL: Server-side Web Development

$27.19

Quick design checklist

• Select the intended database, or qualify the table name.
• Choose data types and lengths based on the values the application actually stores.
• Define a primary key and separate business uniqueness rules where needed.
• Use NOT NULL where missing values are invalid; add defaults only when they make sense.
• Use InnoDB for ordinary transactional tables and enforced foreign keys.
• Add indexes for real query and relationship needs, not every column.
• Choose character sets, collations, and date/time semantics deliberately.
• Inspect the result with SHOW CREATE TABLE and test inserts and constraints.

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.