iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

CREATE TABLE

By · Reviewed & last updated · Part of MySQL Tutorial

A MySQL table needs sensible types, a PRIMARY KEY, and (almost always) a unique constraint on the natural key. Add indexes for FK columns and frequent WHERE/ORDER columns.

A real users table

EXAMPLE
CREATE TABLE users (
    id          BIGINT      UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    name        VARCHAR(120) NOT NULL,
    role        ENUM('admin','editor','member') NOT NULL DEFAULT 'member',
    age         TINYINT     UNSIGNED,
    created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY  uq_users_email (email),
    KEY         idx_users_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE posts (
    id          BIGINT     UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT     UNSIGNED NOT NULL,
    title       VARCHAR(255) NOT NULL,
    body        MEDIUMTEXT,
    published   TINYINT(1)  NOT NULL DEFAULT 0,
    created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY         idx_posts_user (user_id),
    KEY         idx_posts_published (published, created_at),
    CONSTRAINT  fk_posts_user FOREIGN KEY (user_id) REFERENCES users(id)
                    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Why it matters

Always set ENGINE=InnoDB explicitly, utf8mb4 for the charset, and an FK index. The defaults change between versions; pinning them keeps DDL portable.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
CREATE TABLE users (
    id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name  VARCHAR(120) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Try it Yourself »

Discussion

Loading…