SQL DDL Cheat Sheet: CREATE, ALTER, and DROP

SQL examples use PostgreSQL unless another dialect is named. Short snippets assume their referenced tables already exist. Validate DDL in your target database before applying it.

What is SQL DDL?

Data Definition Language (DDL) is the subset of SQL used to define and manage database structures. Unlike DML (Data Manipulation Language) which handles data operations (INSERT, SELECT, UPDATE, DELETE), DDL focuses on the schema itself — creating, modifying, and removing database objects like tables, types, and constraints.

The three core DDL commands are CREATE, ALTER, and DROP. Mastering these is essential for any developer working with relational databases.

Different SQL dialects such as PostgreSQL MySQL SQL Server and SQLite turned into one ERD — DDL syntax varies by database, but a good SQL-to-ERD tool turns those statements into the same useful model: tables, keys, and relationships.

CREATE TABLE

The most fundamental DDL statement. It defines a new table with its columns, data types, and constraints.

Basic Syntax

CREATE TABLE table_name (
  column_name data_type [constraints],
  ...
  [table_constraints]
);

Complete Example

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email TEXT UNIQUE NOT NULL,
  department_id INTEGER REFERENCES departments(id),
  salary NUMERIC(10, 2) DEFAULT 0 CHECK (salary >= 0),
  hire_date DATE DEFAULT CURRENT_DATE,
  is_active BOOLEAN DEFAULT true
);

Common Data Types by Database

Category	PostgreSQL	MySQL	SQL Server
Auto-increment	`SERIAL`	`INT AUTO_INCREMENT`	`INT IDENTITY(1,1)`
Variable text	`VARCHAR(n)`, `TEXT`	`VARCHAR(n)`, `TEXT`	`NVARCHAR(n)`, `NVARCHAR(MAX)` (JSON text); `json` in SQL Server 2025
Boolean	`BOOLEAN`	`TINYINT(1)`	`BIT`
Timestamp	`TIMESTAMP`	`DATETIME`	`DATETIME2`
UUID	`UUID`	`CHAR(36)`	`UNIQUEIDENTIFIER`
JSON	`JSONB`	`JSON`	`NVARCHAR(MAX)` (JSON text); `json` in SQL Server 2025

Column Constraints

Constraints enforce rules on the data in your tables:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  -- PRIMARY KEY: uniquely identifies each row
  id SERIAL PRIMARY KEY,

  -- NOT NULL: column cannot contain NULL
  name VARCHAR(200) NOT NULL,

  -- UNIQUE: no duplicate values allowed
  sku VARCHAR(50) UNIQUE,

  -- DEFAULT: automatic value when not provided
  status VARCHAR(20) DEFAULT 'draft',

  -- CHECK: validates data with a condition
  price NUMERIC(10, 2) CHECK (price >= 0),

  -- REFERENCES: creates a foreign key relationship
  category_id INTEGER REFERENCES categories(id)
);

Table-Level Constraints

For composite keys and multi-column constraints, define them at the table level:

CREATE TABLE students (id INTEGER PRIMARY KEY);
CREATE TABLE courses (id INTEGER PRIMARY KEY);

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  enrolled_at TIMESTAMP DEFAULT now(),
  grade CHAR(2),

  PRIMARY KEY (student_id, course_id),

  CONSTRAINT fk_student
    FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT fk_course
    FOREIGN KEY (course_id) REFERENCES courses(id),

  CONSTRAINT chk_grade
    CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))
);

ALTER TABLE

Modify an existing table’s structure by adding or changing columns and constraints. The examples here use PostgreSQL; SQL Server omits COLUMN in ADD, and SQLite requires table rebuilding for many constraint changes.

Add a Foreign Key

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(id);

Add a Column

ALTER TABLE users
  ADD COLUMN phone VARCHAR(20);

Add a Unique Constraint

ALTER TABLE users
  ADD CONSTRAINT uq_users_email
  UNIQUE (email);

Add a Check Constraint

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive
  CHECK (price > 0);

CREATE TYPE

Define custom reusable types (PostgreSQL feature also supported by Erd Go):

Enum Type

CREATE TYPE order_status AS ENUM (
  'pending', 'processing', 'shipped',
  'delivered', 'cancelled'
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  status order_status DEFAULT 'pending'
);

Composite Type

CREATE TYPE address AS (
  street VARCHAR(200),
  city VARCHAR(100),
  state CHAR(2),
  zip VARCHAR(10)
);

CREATE TABLE companies (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  headquarters address
);

DROP TABLE

DROP TABLE removes the table definition and its data. Use DELETE to remove selected rows while keeping the table structure. This PostgreSQL example creates a staging table, then removes it:

CREATE TABLE staging_orders (
  id INTEGER PRIMARY KEY
);

DROP TABLE IF EXISTS staging_orders;

IF EXISTS handles a missing table; it does not bypass dependency checks. PostgreSQL normally rejects a drop when other objects depend on the table. Review dependencies before choosing CASCADE, which can remove dependent objects too. DDL transaction behavior varies by database engine.

Quick Reference

Statement	Purpose	Example
`DROP TABLE`	Remove a table and its data	`DROP TABLE IF EXISTS staging_orders;`
`CREATE TABLE`	Define a new table	`CREATE TABLE users (...);`
`CREATE TYPE`	Define custom type	`CREATE TYPE status AS ENUM (...);`
`ALTER TABLE ADD`	Add column/constraint	`ALTER TABLE t ADD COLUMN c INT;`
`PRIMARY KEY`	Unique row identifier	`id SERIAL PRIMARY KEY`
`FOREIGN KEY`	Reference another table	`REFERENCES users(id)`
`UNIQUE`	No duplicate values	`email TEXT UNIQUE`
`NOT NULL`	Value required	`name VARCHAR(100) NOT NULL`
`DEFAULT`	Default value	`status TEXT DEFAULT 'active'`
`CHECK`	Validate data	`CHECK (price >= 0)`

SQL DDL Cheat Sheet: CREATE, ALTER & DROP Statements Explained