MySQL CREATE TABLE Syntax: Complete Reference Guide

The CREATE TABLE statement is one of the most fundamental commands in MySQL, allowing you to define the structure of your database tables. Whether you're designing a simple blog or a complex e-commerce platform, understanding the full capabilities of MySQL's table creation syntax is essential for building efficient, well-structured databases.

In this comprehensive guide, we'll explore every aspect of the CREATE TABLE statement, from basic syntax to advanced features like foreign keys, indexes, and table options. By the end, you'll have a complete reference for creating robust MySQL table schemas.

Basic CREATE TABLE Syntax

The basic structure of a CREATE TABLE statement follows this pattern:

CREATE TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
    table_constraints
) table_options;

Let's break down each component:

• table_name: The name of the table you want to create. Table names should be descriptive and follow naming conventions (typically lowercase with underscores).
• column_name: The name of each column in the table.
• data_type: Specifies what kind of data the column can hold (INT, VARCHAR, DATE, etc.).
• constraints: Rules that enforce data integrity (NOT NULL, UNIQUE, PRIMARY KEY, etc.).
• table_constraints: Constraints that apply to the entire table, such as foreign keys and composite primary keys.
• table_options: Additional settings like storage engine, character set, and collation.

Here's a simple example:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This creates a users table with four columns: an auto-incrementing ID, a unique username, a required email address, and a timestamp that automatically records when the row was created.

MySQL Data Types Reference

Choosing the right data type is crucial for database performance and data integrity. MySQL supports a wide variety of data types, which can be categorized into several groups:

Numeric Types

String Types

Date and Time Types

Other Types

Column Constraints

Constraints enforce rules on the data in your tables, ensuring data integrity and consistency. Here are the most commonly used column constraints:

NOT NULL

Ensures that a column cannot have a NULL value. This is essential for required fields.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

DEFAULT

Sets a default value for a column when no value is specified during insertion.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quantity INT DEFAULT 1
);

AUTO_INCREMENT

Automatically generates a unique numeric value for each new row. Typically used with primary keys. Only one AUTO_INCREMENT column per table is allowed, and it must be indexed.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2)
);

UNIQUE

Ensures that all values in a column are different. Multiple UNIQUE constraints can exist in a single table.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)
);

PRIMARY KEY

Uniquely identifies each row in a table. A table can have only one primary key, which can consist of a single column or multiple columns (composite key). Primary keys implicitly include NOT NULL and UNIQUE constraints.

-- Single column primary key
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Foreign Key Constraints

Foreign keys establish relationships between tables by referencing the primary key of another table. They enforce referential integrity, preventing orphaned records.

Basic Foreign Key Syntax

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

ON DELETE and ON UPDATE Options

Foreign keys support actions that determine what happens when the referenced row is deleted or updated:

• CASCADE: Automatically delete or update matching rows in the child table
• SET NULL: Set the foreign key column to NULL
• RESTRICT: Prevent deletion or update of the parent row (default behavior)
• NO ACTION: Same as RESTRICT in MySQL
• SET DEFAULT: Set the foreign key to its default value (not supported by InnoDB)

CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    author_id INT,
    title VARCHAR(200),
    content TEXT,
    FOREIGN KEY (author_id) REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

In this example, if a user is deleted, all their posts are automatically deleted. If a user's ID is updated, the change cascades to all related posts.

Table Options

Table options allow you to configure storage engine, character encoding, and other table-level settings:

ENGINE

Specifies the storage engine. InnoDB is the default and recommended engine for most use cases due to its support for transactions and foreign keys.

CREATE TABLE transactions (
    transaction_id BIGINT PRIMARY KEY,
    amount DECIMAL(10,2),
    description VARCHAR(255)
) ENGINE=InnoDB;

CHARSET and COLLATE

Define the character set and collation for the table. UTF8MB4 is recommended for full Unicode support, including emojis.

CREATE TABLE multilingual_content (
    content_id INT PRIMARY KEY,
    text_value TEXT
) CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMENT

Add a descriptive comment to the table for documentation purposes.

CREATE TABLE audit_log (
    log_id BIGINT PRIMARY KEY,
    action VARCHAR(50),
    timestamp TIMESTAMP
) COMMENT='Stores all user actions for auditing purposes';

Indexes

Indexes improve query performance by allowing MySQL to quickly locate rows without scanning the entire table. However, they also consume storage space and slow down write operations, so use them judiciously.

PRIMARY KEY Index

Every table should have a primary key, which automatically creates a clustered index.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

UNIQUE INDEX

Creates a unique index that enforces uniqueness and improves lookup performance.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100),
    UNIQUE INDEX idx_email (email)
);

Regular INDEX

Creates a non-unique index to speed up queries on frequently searched columns.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    INDEX idx_user_status (user_id, status)
);

FULLTEXT INDEX

Enables full-text search capabilities on TEXT or VARCHAR columns. Useful for search functionality.

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT INDEX ft_search (title, body)
);

-- Usage example
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('database optimization');

Complete Example: E-commerce Database

Let's put everything together with a complete e-commerce database schema that demonstrates real-world usage of MySQL CREATE TABLE syntax:

-- Users table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Registered users of the e-commerce platform';

-- Product categories
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_category_id INT,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='Product categorization hierarchy';

-- Products table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FULLTEXT INDEX ft_product_search (product_name, description)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='Product catalog';

-- Orders table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_address TEXT,
    billing_address TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    INDEX idx_user_order (user_id, order_date),
    INDEX idx_status (status)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='Customer orders';

-- Order items (junction table for many-to-many relationship)
CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    UNIQUE INDEX idx_order_product (order_id, product_id)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='Individual items within orders';

This schema demonstrates several important concepts:

• Auto-incrementing primary keys for all tables
• Foreign key relationships with appropriate ON DELETE and ON UPDATE actions
• Self-referencing foreign key in categories for hierarchical structure
• Generated columns (subtotal calculated automatically)
• Multiple indexes for query optimization
• Full-text search capability for products
• Timestamp tracking for creation and updates
• Proper character encoding (UTF8MB4) for international support
• Table comments for documentation

Visualize Your Schema with Free ER Diagram

Now that you understand MySQL CREATE TABLE syntax, wouldn't it be great to visualize your database schema? Our free online tool, Free ER Diagram, makes it easy to convert your SQL DDL statements into professional ER diagrams instantly.

Simply paste your CREATE TABLE statements into our tool, and it will automatically generate a visual representation of your database structure, showing all tables, columns, relationships, and cardinalities. It's perfect for:

• Documenting existing databases
• Planning new database designs
• Communicating schema changes to your team
• Identifying potential design issues before implementation

Try it now at erdiagram.utoolab.com — no signup required, completely free, and runs entirely in your browser for maximum privacy.