SQL DDL Cheat Sheet

Data Definition Language (DDL) defines and modifies database structures such as tables, schemas, and indexes.

CREATE

Create Database

CREATE DATABASE database_name;
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE database_name;

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

• common table definitions

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- with primary key
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

-- composite primary key
CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

• with index

CREATE TABLE customers (
    id INT,
    email VARCHAR(100),
    INDEX (email)
);

• with foreign key

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

-- composite foreign key
CREATE TABLE payments (
    invoice_id INT,
    payment_number INT,
    FOREIGN KEY (invoice_id, payment_number)
      REFERENCES invoices(id, number)
);

• temporary table

CREATE TEMPORARY TABLE temp_table (...);

• create table if not exists

CREATE TABLE IF NOT EXISTS table_name (...);

CREATE TABLE table (column_field datatype, field2 type2);
CREATE TABLE table (column_field datatype, field2 type2, INDEX (field));
CREATE TABLE table (column_field datatype, field2 type2, PRIMARY KEY (column_field));
CREATE TABLE table (column_field datatype, field2 type2, PRIMARY KEY (column_field,field2));

CREATE TABLE table1 (fk_column_field datatype, field2 type2, ...,
  FOREIGN KEY (fk_column_field) REFERENCES table2 (t2_fieldA))
    [ON UPDATE|ON DELETE] [CASCADE|SET NULL]

CREATE TABLE table1 (fk_column_field datatype, fk_field2 type2, ...,
 FOREIGN KEY (fk_column_field, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))

CREATE TABLE table IF NOT EXISTS;

CREATE TEMPORARY TABLE table;

-- example
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER

Rename Database

ALTER DATABASE database_name CHARACTER SET utf8;

Add Column

ALTER TABLE table_name ADD column_name datatype; -- last position - default
ALTER TABLE table_name ADD column_name datatype FIRST; -- first position
ALTER TABLE table_name ADD column_name datatype AFTER existing_column;

Modify Column

-- modify column - datatype or constraints
ALTER TABLE table_name MODIFY column_name new_datatype;
ALTER TABLE table_name MODIFY column_name new_datatype NOT NULL ...;

Rename Column

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Change Column - rename and modify

ALTER TABLE table_name CHANGE old_name new_name datatype;
ALTER TABLE table_name CHANGE old_name new_name datatype NOT NULL ...;

Defaults

-- set default value
ALTER TABLE table_name ALTER column_name SET DEFAULT ...;
ALTER TABLE table_name ALTER column_name DROP DEFAULT;

Add Index / Unique

ALTER TABLE table_name ADD INDEX index_name (column_name);
ALTER TABLE table_name ADD UNIQUE index_name (column_name);

DROP

Drop Column / Index / Foreign Key

ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP FOREIGN KEY fk_name;

Drop Table

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

Drop Database

DROP DATABASE database_name;

⚠️ Caution - Irreversible: DROP permanently deletes the structure and all its data.

TRUNCATE x DROP x DELETE

• DROP = removes the table and all its data
• DDL
• TRUNCATE = removes all rows from a table
• resets AUTO_INCREMENT
• faster than DELETE for large tables - doesn’t log row-by-row deletes
• WHERE can't be used
• it resets the table to its initial state, but keeps the structure
• it cannot be rolled back in InnoDB, so it’s non-transactional
• it's classified as DDL in MySQL, even though it manipulates data
• DELETE = removes rows from a table
• DML
• suuports WHERE clause to specify which rows to delete
• it’s transactional in InnoDB, so it can be rolled back
• it logs each row deletion, which can be slow for large tables

DELETE FROM table1 / TRUNCATE table1;
DELETE FROM table1 WHERE condition;
DELETE FROM table1, table2 WHERE table1.id1 =
  table2.id2 AND condition;

RENAME

Rename Table

ALTER TABLE old_table_name
RENAME TO new_table_name;

CONSTRAINTS (within CREATE TABLE)

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• NOT NULL
• CHECK - MySQL 8.0.16+
• DEFAULT

Example with constraints

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

INDEXES

Create Index

CREATE INDEX index_name
ON table_name (column_name);

Unique Index

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Drop Index

DROP INDEX index_name ON table_name;

MySQL requires one to specify the table when dropping an index.

Notes

• SQL syntax varies slightly between engines (MySQL, PostgreSQL, SQL Server, SQLite)
• Always back up data and test in a development environment before making schema changes in production
• Indexes improve read performance, but slow down writes. Use them wisely.
• FOREIGN KEY constraints require InnoDB engine.
• MySQL enforces only limited support for CHECK constraints before version 8.0 — older versions ignore them
• Use SHOW CREATE TABLE table_name; to inspect how MySQL interprets your DDL
• DDL x DML
• if it manipulates rows → DML
• if it defines/drops structure or resets internals → DDL
• exception - TRUNCATE looks like DML, but behaves like DDL under the hood

---