Creating Tables — MySQL

CREATE TABLE in MySQL

The CREATE TABLE statement is how you define the structure of your data — columns, types, constraints, indexes, and storage options. A well-designed table definition enforces data integrity at the database level, independent of your application code.

This tutorial covers everything from basic column definitions to composite keys, foreign keys, table options, and advanced creation patterns.

Basic CREATE TABLE Syntax

SQL

CREATE TABLE [IF NOT EXISTS] table_name (
  column_name  data_type  [column_constraints],
  column_name  data_type  [column_constraints],
  ...
  [table_constraints]
) [table_options];

A Complete Real-World Example

SQL

CREATE TABLE users (
  id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  email         VARCHAR(254)    NOT NULL,
  username      VARCHAR(50)     NOT NULL,
  password_hash CHAR(60)        NOT NULL,
  first_name    VARCHAR(100)    NOT NULL,
  last_name     VARCHAR(100)    NOT NULL,
  bio           TEXT,
  avatar_url    VARCHAR(2048),
  is_active     TINYINT(1)      NOT NULL DEFAULT 1,
  role          ENUM('user','moderator','admin') NOT NULL DEFAULT 'user',
  created_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                ON UPDATE CURRENT_TIMESTAMP,
  last_login_at DATETIME,

  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email),
  UNIQUE KEY uq_username (username),
  INDEX idx_role (role),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Application user accounts';

Column Definitions

Each column definition follows this pattern:

SQL

column_name  data_type  [NOT NULL | NULL]  [DEFAULT value]
             [AUTO_INCREMENT]  [UNIQUE]  [PRIMARY KEY]
             [COMMENT 'text']  [COLLATE collation]

NULL and NOT NULL

SQL

CREATE TABLE column_examples (
  -- NOT NULL: must have a value on every insert
  required_name  VARCHAR(100) NOT NULL,

  -- NULL: optional, defaults to NULL if not provided
  optional_bio   TEXT NULL,  -- NULL is the default, so NULL keyword is optional

  -- NOT NULL with a default: required in the sense that it always has a value,
  -- but you don't need to specify it in every INSERT
  is_active      TINYINT(1) NOT NULL DEFAULT 1
);

Note

Prefer NOT NULL for columns that should always have a value. NULL-able columns require extra NULL checks in application code, can't use certain indexes efficiently, and make aggregations behave unexpectedly (COUNT(col) ignores NULLs).

DEFAULT Values

SQL

CREATE TABLE defaults_demo (
  -- Literal defaults
  status        VARCHAR(20)     NOT NULL DEFAULT 'active',
  priority      TINYINT         NOT NULL DEFAULT 3,
  is_published  TINYINT(1)      NOT NULL DEFAULT 0,

  -- Dynamic defaults (expressions in parentheses -- MySQL 8.0.13+)
  created_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  uuid_col      VARCHAR(36)     DEFAULT (UUID()),
  hash_id       CHAR(8)         DEFAULT (SUBSTRING(MD5(RAND()), 1, 8)),

  -- NULL as default (explicit)
  deleted_at    DATETIME        DEFAULT NULL
);

Tip

Dynamic default expressions (wrapped in parentheses) were introduced in MySQL 8.0.13. They let you call functions like UUID(), NOW(), or RAND() as column defaults. This is particularly useful for generating unique identifiers without application-level code.

AUTO_INCREMENT

SQL

CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- ... other columns ...
  PRIMARY KEY (id)
);

-- You can also set the starting value
CREATE TABLE invoices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- ... other columns ...
  PRIMARY KEY (id)
) AUTO_INCREMENT = 1000;  -- first invoice will be #1000

-- Change the starting value after creation
ALTER TABLE invoices AUTO_INCREMENT = 5000;

Primary Key

The primary key uniquely identifies each row in a table. Every InnoDB table should have one — without a primary key, InnoDB generates a hidden 6-byte row ID that you can't reference.

Single-column primary key:

SQL

-- Inline syntax (on the column itself)
CREATE TABLE products (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(200) NOT NULL
);

-- Table-level syntax (equivalent, more explicit)
CREATE TABLE products (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name  VARCHAR(200) NOT NULL,
  PRIMARY KEY (id)
);

Composite Primary Key

SQL

-- Composite primary key (must use table-level syntax)
CREATE TABLE order_items (
  order_id    INT UNSIGNED NOT NULL,
  product_id  INT UNSIGNED NOT NULL,
  quantity    INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price  DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- The combination of order_id + product_id must be unique
-- Each column individually can repeat

Note

Composite primary keys are ideal for junction/mapping tables (many-to-many relationships). Using a surrogate auto-increment key on these tables is wasteful — the natural composite key expresses the uniqueness constraint more clearly and creates a useful covering index.

UNIQUE Constraints

SQL

CREATE TABLE users (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email    VARCHAR(254) NOT NULL,
  username VARCHAR(50)  NOT NULL,
  phone    VARCHAR(20),           -- can be NULL

  PRIMARY KEY (id),

  -- Single-column unique
  UNIQUE KEY uq_email (email),
  UNIQUE KEY uq_username (username),

  -- NULL values are exempt from UNIQUE constraints
  -- Multiple rows can have NULL in a UNIQUE column
  UNIQUE KEY uq_phone (phone)    -- multiple NULL phones allowed
);

-- Composite unique constraint
CREATE TABLE team_members (
  team_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  role    VARCHAR(50),
  PRIMARY KEY (team_id, user_id),

  -- A user can be in the same team once, but different teams multiple times
  -- The PK already enforces this here; in other patterns you'd use:
  UNIQUE KEY uq_team_user (team_id, user_id)
);

Foreign Keys

Foreign keys enforce referential integrity between tables. When a column in one table references the primary key of another, a foreign key constraint ensures:

You can't insert a row with a non-existent parent ID
You can't delete a parent row while child rows still reference it (unless ON DELETE CASCADE is set)

SQL

CREATE TABLE categories (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title       VARCHAR(500) NOT NULL,
  body        MEDIUMTEXT   NOT NULL,
  author_id   INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED,           -- nullable: post may have no category

  PRIMARY KEY (id),

  -- Foreign key with explicit name (recommended for debugging)
  CONSTRAINT fk_posts_author
    FOREIGN KEY (author_id)
    REFERENCES users (id)
    ON DELETE RESTRICT    -- prevent deleting a user who has posts
    ON UPDATE CASCADE,    -- if user id changes, update posts too

  CONSTRAINT fk_posts_category
    FOREIGN KEY (category_id)
    REFERENCES categories (id)
    ON DELETE SET NULL    -- if category deleted, set post category to NULL
    ON UPDATE CASCADE
);

Referential Action	ON DELETE behavior	ON UPDATE behavior
RESTRICT (default)	Error if parent row has children	Error if parent key has children
CASCADE	Delete child rows automatically	Update child FK to match new parent key
SET NULL	Set child FK column to NULL	Set child FK column to NULL
NO ACTION	Same as RESTRICT (deferred check in standard SQL)	Same as RESTRICT
SET DEFAULT	Set child FK to column DEFAULT (rare, limited support)	Set child FK to column DEFAULT

Inline vs Table-Level Foreign Keys

SQL

-- Inline foreign key (less common, no constraint name)
author_id INT UNSIGNED NOT NULL REFERENCES users(id),

-- Table-level foreign key with name (recommended)
CONSTRAINT fk_posts_author FOREIGN KEY (author_id) REFERENCES users (id) ON DELETE RESTRICT,

Warning

Always name your foreign key constraints explicitly. MySQL generates cryptic names like posts_ibfk_1 if you don't. Named constraints are much easier to reference in ALTER TABLE, error messages, and debugging.

Regular Indexes

SQL

CREATE TABLE sessions (
  id         CHAR(64)    NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  data       JSON,
  ip_address VARCHAR(45),
  created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME    NOT NULL,

  PRIMARY KEY (id),

  -- Simple index
  INDEX idx_user_id (user_id),

  -- Composite index (covers queries filtering by both user_id and expires_at)
  INDEX idx_user_expires (user_id, expires_at),

  -- Descending index (MySQL 8.0+) — useful for ORDER BY col DESC
  INDEX idx_created_desc (created_at DESC)
);

Tip

Put the most selective column first in a composite index. If a query filters on user_id = 5 AND status = 'active' and user_id is more selective (fewer matching rows), put user_id first. MySQL can also use a composite index (a, b) for queries that filter on only column a.

Table Options

SQL

CREATE TABLE example (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
)
ENGINE = InnoDB               -- storage engine (InnoDB is default and recommended)
DEFAULT CHARSET = utf8mb4     -- default character set for this table
COLLATE = utf8mb4_unicode_ci  -- default collation for this table
AUTO_INCREMENT = 1            -- starting value for auto_increment
ROW_FORMAT = DYNAMIC          -- InnoDB row format
COMMENT = 'A descriptive comment about this table'
COMPRESSION = 'NONE';         -- or 'ZLIB' / 'LZ4' for compressed tables

Option	Common Values	Notes
ENGINE	InnoDB, MyISAM, MEMORY, CSV	Always use InnoDB for new tables
DEFAULT CHARSET	utf8mb4	Overrides database default
COLLATE	utf8mb4_unicode_ci, utf8mb4_bin	Overrides database default
AUTO_INCREMENT	Any positive integer	Sets the next AUTO_INCREMENT value
ROW_FORMAT	DYNAMIC, COMPACT, COMPRESSED	DYNAMIC is the modern default
COMMENT	Any string up to 1024 chars	Shows in SHOW TABLE STATUS and SHOW CREATE TABLE

CREATE TABLE IF NOT EXISTS

SQL

-- Idempotent table creation (no error if already exists)
CREATE TABLE IF NOT EXISTS migrations (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  migration_name VARCHAR(255) NOT NULL UNIQUE,
  applied_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE AS SELECT

You can create a new table and populate it with data from a query in one statement. The new table's columns are derived from the SELECT result.

SQL

-- Create a summary table from existing data
CREATE TABLE monthly_sales_summary AS
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  COUNT(*) AS order_count,
  SUM(total) AS total_revenue,
  AVG(total) AS avg_order_value
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

-- Important: CTAS does NOT copy indexes or constraints from the source
-- You must add them manually:
ALTER TABLE monthly_sales_summary
  ADD PRIMARY KEY (month),
  ADD INDEX idx_revenue (total_revenue);

Warning

CREATE TABLE AS SELECT does not copy PRIMARY KEY, UNIQUE KEY, FOREIGN KEY constraints, or indexes from the source. The resulting table has only column definitions. Always add necessary indexes and constraints after the CTAS statement.

CREATE TABLE LIKE

CREATE TABLE ... LIKE creates an empty copy of an existing table, including all column definitions, indexes, and constraints — but no data.

SQL

-- Create an empty copy with the same structure (including indexes)
CREATE TABLE orders_archive LIKE orders;

-- Useful for:
-- 1. Creating a staging table for bulk imports
CREATE TABLE users_import LIKE users;
LOAD DATA INFILE '/tmp/new_users.csv' INTO TABLE users_import ...;
-- validate, then move to production:
INSERT INTO users SELECT * FROM users_import;

-- 2. Partitioned archive tables
CREATE TABLE orders_2023 LIKE orders;
INSERT INTO orders_2023 SELECT * FROM orders WHERE YEAR(created_at) = 2023;
DELETE FROM orders WHERE YEAR(created_at) = 2023;

Viewing Table Definitions

SQL

-- Show the CREATE TABLE statement (full definition)
SHOW CREATE TABLE usersG

-- Quick column overview
DESCRIBE users;
DESC users;

-- Detailed column info from information_schema
SELECT
  COLUMN_NAME,
  COLUMN_TYPE,
  IS_NULLABLE,
  COLUMN_DEFAULT,
  EXTRA,
  COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'myapp'
  AND TABLE_NAME = 'users'
ORDER BY ORDINAL_POSITION;

+--------------+-----------------+-------------+-------------------+---------------------------+
| COLUMN_NAME  | COLUMN_TYPE     | IS_NULLABLE | COLUMN_DEFAULT    | EXTRA                     |
+--------------+-----------------+-------------+-------------------+---------------------------+
| id           | int unsigned    | NO          | NULL              | auto_increment            |
| email        | varchar(254)    | NO          | NULL              |                           |
| is_active    | tinyint(1)      | NO          | 1                 |                           |
| created_at   | timestamp       | NO          | CURRENT_TIMESTAMP | DEFAULT_GENERATED         |
| updated_at   | timestamp       | NO          | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP|
+--------------+-----------------+-------------+-------------------+---------------------------+