MySQL DDL and Schema Design Cheatsheet

MySQL•Permalink

create, alter, rename, and drop databases and tables in MySQL, with practical patterns for primary keys, unique keys, and foreign keys.

View

Standard Detailed Compact

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## create and alter tables

Create a database

↗#

CREATE DATABASE IF NOT EXISTS app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

# Create a schema if it does not already exist.

Create a users table

↗#

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  full_name VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;

# Create a table with an auto-increment primary key and timestamps.

Add a column

↗#

ALTER TABLE users ADD COLUMN phone VARCHAR(50) NULL AFTER email;

# Add a nullable column to an existing table.

Modify a column definition

↗#

ALTER TABLE users MODIFY COLUMN phone VARCHAR(100) NULL;

# Change type, size, or nullability.

Rename a column

↗#

ALTER TABLE users CHANGE COLUMN full_name display_name VARCHAR(255) NOT NULL;

# Rename a column while specifying its full definition.

Drop a column

↗#

ALTER TABLE users DROP COLUMN phone;

# Remove a column from a table.

Rename a table

↗#

RENAME TABLE users TO app_users;

# Rename a table without recreating it.

Drop a table if it exists

↗#

DROP TABLE IF EXISTS old_logs;

# Delete a table definition and all rows.

## constraints and keys

Add a unique key

↗#

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

# Prevent duplicate values for a column or column pair.

Create a table with a foreign key

↗#

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_orders_user_id (user_id),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE=InnoDB;

# Reference a parent table and enforce relational integrity.

Drop a foreign key

↗#

ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

# Remove a foreign key constraint from a table.

Create a table with a composite primary key

↗#

CREATE TABLE user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_code VARCHAR(50) NOT NULL,
  granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_code)
) ENGINE=InnoDB;

# Use multiple columns as the logical primary key.

Drop a database

↗#