CREATE Tables

The CREATE statement is used to create database objects, including databases, tables, indexes, etc. This chapter will introduce in detail how to use the CREATE statement to create databases and tables.

Create Database

Basic Syntax

CREATE DATABASE database_name;

Examples

-- Create database
CREATE DATABASE mydb;

-- Create database (if not exists)
CREATE DATABASE IF NOT EXISTS mydb;

-- Specify character set
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

View Databases

-- View all databases
SHOW DATABASES;

-- Use database
USE mydb;

-- View current database
SELECT DATABASE();

Create Table

Basic Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    table_constraints
);

Simple Example

-- Create users table
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

Data Types

Numeric Types

CREATE TABLE numbers_example (
    tiny_int TINYINT,           -- 1 byte, -128 to 127
    small_int SMALLINT,         -- 2 bytes, -32768 to 32767
    medium_int MEDIUMINT,       -- 3 bytes
    normal_int INT,             -- 4 bytes, -2147483648 to 2147483647
    big_int BIGINT,             -- 8 bytes
    decimal_num DECIMAL(10,2),  -- Exact decimal, 10 digits, 2 decimals
    float_num FLOAT,            -- Single precision floating point
    double_num DOUBLE           -- Double precision floating point
);

String Types

CREATE TABLE strings_example (
    fixed_char CHAR(10),        -- Fixed length string
    var_char VARCHAR(255),      -- Variable length string
    tiny_text TINYTEXT,         -- Max 255 characters
    normal_text TEXT,           -- Max 65,535 characters
    medium_text MEDIUMTEXT,     -- Max 16,777,215 characters
    long_text LONGTEXT          -- Max 4,294,967,295 characters
);

Date/Time Types

CREATE TABLE datetime_example (
    date_col DATE,              -- Date: YYYY-MM-DD
    time_col TIME,              -- Time: HH:MM:SS
    datetime_col DATETIME,      -- DateTime: YYYY-MM-DD HH:MM:SS
    timestamp_col TIMESTAMP,    -- Timestamp
    year_col YEAR               -- Year: YYYY
);

Other Types

CREATE TABLE other_types (
    bool_col BOOLEAN,           -- Boolean (0 or 1)
    enum_col ENUM('small', 'medium', 'large'),  -- Enum
    set_col SET('red', 'green', 'blue'),        -- Set
    json_col JSON,              -- JSON data
    blob_col BLOB               -- Binary data
);

Constraints

PRIMARY KEY

-- Method 1: Specify in column definition
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Method 2: Specify at end of table definition
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

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

AUTO_INCREMENT

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

NOT NULL

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

UNIQUE

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

DEFAULT

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CHECK

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT CHECK (age >= 18),
    email VARCHAR(100) CHECK (email LIKE '%@%')
);

FOREIGN KEY

-- Create parent table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- Create child table (with foreign key)
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Foreign key with cascade operations
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Complete Examples

Example 1: Users Table

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 18),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Example 2: Products Table

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INT DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Example 3: Orders Table

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

Example 4: Order Items Table

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

Advanced Table Creation Options

Specify Storage Engine

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

Specify Character Set

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Add Table Comments

CREATE TABLE users (
    id INT PRIMARY KEY COMMENT 'User ID',
    name VARCHAR(50) COMMENT 'Username'
) COMMENT='User information table';

Create Table from Query Results

CREATE TABLE AS SELECT

-- Copy table structure and data
CREATE TABLE users_backup AS
SELECT * FROM users;

-- Copy only partial data
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

-- Copy only table structure (no data)
CREATE TABLE users_template AS
SELECT * FROM users WHERE 1=0;

Create Temporary Table

-- Create temporary table
CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(50)
);

-- Temporary table is automatically deleted when session ends

View Table Structure

-- View table structure
DESC users;
DESCRIBE users;

-- View CREATE TABLE SQL
SHOW CREATE TABLE users;

-- View all tables
SHOW TABLES;

Practical Example: E-commerce System

Complete Database Design

-- 1. Create database
CREATE DATABASE IF NOT EXISTS ecommerce CHARACTER SET utf8mb4;
USE ecommerce;

-- 2. Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_username (username)
) COMMENT='Users table';

-- 3. User addresses table
CREATE TABLE user_addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    recipient_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    district VARCHAR(50),
    address VARCHAR(200) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) COMMENT='User addresses table';

-- 4. Categories table
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id INT,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) COMMENT='Product categories table';

-- 5. Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    original_price DECIMAL(10,2),
    stock INT DEFAULT 0 CHECK (stock >= 0),
    sales_count INT DEFAULT 0,
    image_url VARCHAR(255),
    is_active 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(id) ON DELETE SET NULL,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FULLTEXT idx_name_desc (name, description)
) COMMENT='Products table';

-- 6. Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_fee DECIMAL(10,2) DEFAULT 0,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    payment_method VARCHAR(20),
    shipping_address_id INT,
    remark TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL,
    shipped_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_user_id (user_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) COMMENT='Orders table';

-- 7. Order items table
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    product_image VARCHAR(255),
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) COMMENT='Order items table';

-- 8. Shopping cart table
CREATE TABLE cart_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_product (user_id, product_id),
    INDEX idx_user_id (user_id)
) COMMENT='Shopping cart table';

Common Errors

Error 1: Table Already Exists

-- Error
CREATE TABLE users (...);  -- Error if table already exists

-- Correct
CREATE TABLE IF NOT EXISTS users (...);

Error 2: Data Type Mismatch

-- Error: VARCHAR without length
CREATE TABLE users (
    name VARCHAR
);

-- Correct
CREATE TABLE users (
    name VARCHAR(50)
);

Error 3: Foreign Key References Non-existent Table

-- Error: Referenced table doesn't exist
CREATE TABLE orders (
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)  -- users table must exist first
);

-- Correct: Create parent table first
CREATE TABLE users (id INT PRIMARY KEY);
CREATE TABLE orders (
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Best Practices

1. Use Meaningful Table and Column Names

-- Good
CREATE TABLE user_orders (...);

-- Not good
CREATE TABLE tbl1 (...);

2. Always Define Primary Key

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- Primary key
    name VARCHAR(50)
);

3. Use Constraints Appropriately

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE,  -- Not null and unique
    age INT CHECK (age >= 0),            -- Check constraint
    status VARCHAR(20) DEFAULT 'active'  -- Default value
);

4. Create Indexes for Foreign Key Columns

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id)  -- Create index for foreign key
);

5. Add Timestamp Fields

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Summary

This chapter introduced the use of CREATE statement:

• Create Database: CREATE DATABASE
• Create Table: CREATE TABLE
• Data Types: Numeric, string, date/time, etc.
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, CHECK
• Advanced Options: Storage engine, character set, comments
• Create from Query: CREATE TABLE AS SELECT
• Temporary Tables: CREATE TEMPORARY TABLE

Mastering the CREATE statement is the foundation of database design. Proper table structure design is crucial for system performance and maintainability.

Next Step: Learn INSERT Data to add data to tables.