MySQL: A Detailed Guide to Creating MySQL Tables and…

Last updated: 2026-08-26

Tables are the basic units for storing data in a database—if you design the table structure well, you’re halfway to successful data management.

In this lesson, you'll learn how to create tables, set constraints, and view their structure.

graph TB
    A[CREATE TABLE Element] --> B[Column Definition<br/>Field Name+Data Types]
    A --> C[Constraint Definition]
    C --> D[PRIMARY KEY Primary Key]
    C --> E[FOREIGN KEY Foreign Key]
    C --> F[UNIQUE The Only One]
    C --> G[NOT NULL Not empty]
    C --> H[DEFAULT Default value]
    C --> I[CHECK Inspection]
    A --> J[Table Options<br/>ENGINE/CHARSET/COMMENT]

1. What You'll Learn

Complete syntax for CREATE TABLE
Primary Key Constraint (PRIMARY KEY)
Foreign Key Constraints (FOREIGN KEY)
Unique Constraint (UNIQUE)
NOT NULL constraint
View table structure

2. A True Story About an E-commerce System

(1) Pain Point: Disorganized Data

An e-commerce database has only the orders table, which stores both customer information and product information:

order_id	customer_name	customer_phone	product_name	product_price	quantity
1	Alice	13800001111	iPhone	999	2
2	Alice	13800001111	MacBook	1999	1
3	Bob	13800002222	iPhone	999	1

Question:

Alice's phone number is listed three times, so changing it requires editing three entries.
I've saved the iPhone price twice; if the price goes up, I'll have to edit multiple lines
It's impossible to track "how many of a particular product were sold."

(2) Standardized Solution Method

Split it into 3 tables and link them using foreign keys:

SQL

-- Customer Table
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) UNIQUE
);

-- Product List
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- Orders Table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

3. CREATE TABLE Syntax

(1) Complete Syntax

SQL

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8mb4];

▶ Example: Creating a User Table

SQL

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 150),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Table';

▶ Try it Yourself

TEXT 📖 Display only

Query OK, 0 rows affected (0.02 sec)

4. Primary Key Constraint (PRIMARY KEY)

A primary key is a column in a table that uniquely identifies each row; it cannot be duplicated and cannot be NULL.

(1) Primary Key Type

Type	Description	Example
Single-column primary key	A single field as the primary key	`id INT PRIMARY KEY`
Composite Primary Key	Multiple Fields Combined to Form a Primary Key	`PRIMARY KEY (order_id, product_id)`
Auto-incrementing primary key	Automatically generates an incrementing ID	`id INT AUTO_INCREMENT PRIMARY KEY`

▶ Example: Using Primary Keys

TEXT 📖 Display only

Query OK, 0 rows affected

Query OK, 0 rows affected

SQL

-- Single-column primary key
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- Composite Primary Key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

TEXT 📖 Display only

Output displayed

5. Foreign Key Constraints (FOREIGN KEY)

Foreign keys are used to establish relationships between tables and ensure data integrity.

(1) Foreign Key Cascading Operations

Action	Description
CASCADE	When a record is deleted or updated in the primary table, the corresponding record is deleted or updated in the secondary table
SET NULL	When the primary table is deleted, the foreign key in the secondary table is set to NULL
RESTRICT	Prohibits deletion from the primary table when there are records in the foreign table (default)
NO ACTION	Same as RESTRICT

▶ Example: Foreign Key Cascading

TEXT 📖 Display only

Query OK, 0 rows affected

Query OK, 1 row affected

SQL

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- When deleting a customer，All orders for this customer have been automatically deleted.
DELETE FROM customers WHERE id = 1;
-- orders In the table customer_id=1 Records are automatically deleted

TEXT 📖 Display only

Output displayed

6. Unique Constraint (UNIQUE)

A unique constraint ensures that values in a column are unique (but allows NULL values).

▶ Example: Unique Constraint

TEXT 📖 Display only

Query OK, 0 rows affected

Query OK, 0 rows affected

SQL

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,        -- Single-row, unique
    username VARCHAR(50) UNIQUE,      -- Single-row, unique
    phone VARCHAR(20),
    UNIQUE KEY (phone)                -- Another way to write it
);

-- Composite Unique Constraint
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE KEY (user_id, role_id)    -- The same role cannot be assigned to the same user more than once.
);

TEXT 📖 Display only

Output displayed

7. NOT NULL Constraint

Non-NULL constraints ensure that a column cannot store NULL values.

▶ Example: Not-Null Constraint

TEXT 📖 Display only

Query OK, 0 rows affected

Query OK, 1 row affected

SQL

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,       -- Product Name (Required)
    price DECIMAL(10,2) NOT NULL,     -- Price is required
    description TEXT,                  -- The description may be left blank
    category_id INT NOT NULL
);

-- Insertion Test
INSERT INTO products (name, price) VALUES ('iPhone', 999);
-- Error：category_id Cannot be NULL
-- ERROR 1048 (23000): Column 'category_id' cannot be null

TEXT 📖 Display only

Output displayed

8. Default Constraints (DEFAULT)

▶ Example: Default Value

TEXT 📖 Display only

Query OK, 0 rows affected

Query OK, 1 row affected

---+---------+------------------
id | name    | email            
---+---------+------------------
1  | Alice   | alice@email.com  
2  | Bob     | bob@email.com    
3  | Charlie | charlie@email.com
---+---------+------------------
3 rows in set

SQL

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    view_count INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Omit fields with default values during insertion
INSERT INTO articles (title) VALUES ('My First Article');

-- Search
SELECT * FROM articles;

TEXT 📖 Display only

+----+-------------------+--------+------------+-------------+---------------------+
| id | title             | status | view_count | is_featured | created_at          |
+----+-------------------+--------+------------+-------------+---------------------+
|  1 | My First Article  | draft  |          0 |           0 | 2026-07-03 10:00:00 |
+----+-------------------+--------+------------+-------------+---------------------+

TEXT 📖 Display only

Output displayed

CHECK constraint (MySQL 8.0+)

CHECK constraints ensure that column values meet specified conditions.

9. Viewing Table Structures

❓ FAQ

Q Should the primary key be an auto-incrementing INT or a UUID?

A We recommend an auto-incrementing INT or BIGINT (for better performance and smaller storage footprint). UUIDs are suitable for distributed scenarios but can reduce index performance.

Q Can a table have multiple UNIQUE constraints?

A Yes. A table can have multiple UNIQUE constraints, but it can have only one PRIMARY KEY.

Q Do foreign keys affect performance?

A They have a slight impact (constraints must be checked on every write), but they ensure data integrity. In high-concurrency scenarios, checks can be performed at the application layer.

Q What is the difference between NULL and an empty string?

A NULL means "no value," while an empty string '' is a value. You cannot compare NULL using the = operator; you must use IS NULL.

Q Should table names be in the singular or plural?

A We recommend using the plural (users, orders) to indicate "a set of records." Just be sure to maintain consistent styling throughout the project.

📖 Summary

CREATE TABLE creates a table; you must specify column names, data types, and constraints
PRIMARY KEY: The primary key uniquely identifies each row; an auto-incrementing INT is recommended.
FOREIGN KEY Establishes relationships between tables; supports CASCADE, SET NULL, and RESTRICT
UNIQUE A unique constraint ensures that column values are unique.
NOT NULL: A NOT NULL constraint ensures that a column does not store NULL values.
DEFAULT The default value is automatically filled in upon insertion
CHECK Constraint (8.0+) ensures that column values meet the conditions
DESCRIBE to view the table structure; SHOW CREATE TABLE to view the complete definition

📝 Exercises

Basic Question (Difficulty ⭐): Create the students table, containing id (auto-incrementing primary key), name (not null), age (CHECK 18-100), and email (unique).
Advanced Exercise (Difficulty: ⭐⭐): Create the courses table and the student_courses associated table (many-to-many), set up foreign key constraints, and configure the system to cascade the deletion of course enrollment records when a student is deleted.
Challenge (Difficulty: ⭐⭐⭐): Design the table structure for a blog system (users, posts, comments, tags), including constraints such as primary keys, foreign keys, non-null constraints, and default values.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

MySQL: A Detailed Guide to Creating MySQL Tables and…

1. What You'll Learn

2. A True Story About an E-commerce System

(1) Pain Point: Disorganized Data

(2) Standardized Solution Method

3. CREATE TABLE Syntax

(1) Complete Syntax

▶ Example: Creating a User Table

4. Primary Key Constraint (PRIMARY KEY)

(1) Primary Key Type

▶ Example: Using Primary Keys

5. Foreign Key Constraints (FOREIGN KEY)

(1) Foreign Key Cascading Operations

▶ Example: Foreign Key Cascading

6. Unique Constraint (UNIQUE)

▶ Example: Unique Constraint

7. NOT NULL Constraint

▶ Example: Not-Null Constraint

8. Default Constraints (DEFAULT)

▶ Example: Default Value

CHECK constraints ensure that column values meet specified conditions.

9. Viewing Table Structures

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好