Java.DBMigrationTools.How do you write a simple “create table” migration?

Short interview answer (what you say aloud)

A simple CREATE TABLE migration is a versioned script that creates a table idempotently, defines primary key, types, constraints, and usually indexes.
It should be deterministic, safe to run once, and easy to roll forward.

---

Canonical example (PostgreSQL)

-- V001__create_users.sql

CREATE TABLE IF NOT EXISTS users (
    id          BIGSERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    password    TEXT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT now()
);

Why this is “correct”

• IF NOT EXISTS → idempotent
• Explicit PK
• Constraints (NOT NULL, UNIQUE)
• Reasonable defaults
• No business logic

---

Slightly more “senior” version (recommended)

CREATE TABLE IF NOT EXISTS users (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    email       VARCHAR(255) NOT NULL,
    password    TEXT NOT NULL,
    status      VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    created_at  TIMESTAMP NOT NULL DEFAULT now(),

    CONSTRAINT pk_users PRIMARY KEY (id),
    CONSTRAINT uq_users_email UNIQUE (email)
);

Why this is better

• Explicit constraint names → clean rollbacks & debugging
• IDENTITY instead of SERIAL (modern Postgres)
• No magic behavior

---

With indexes (common interview follow-up)

CREATE INDEX IF NOT EXISTS idx_users_created_at
    ON users (created_at);

👉 Indexes should usually be created explicitly, not inline.

---

With comments (production-ready)

COMMENT ON TABLE users IS 'Application users';
COMMENT ON COLUMN users.email IS 'Unique user email';

How this looks in real tools

Flyway

V001__create_users.sql

Liquibase (SQL-based)

001-create-users.sql

Common interview mistakes 🚫

• ❌ No primary key
• ❌ Using SERIAL without understanding it
• ❌ No constraints
• ❌ Mixing data inserts with schema creation
• ❌ No idempotency
• ❌ Using TEXT everywhere

---

Rollback question (classic trap)

Q: How do you rollback this migration?

Answer:

DROP TABLE users;

Senior addition:

In production we usually prefer forward-only migrations; rollback scripts are optional and often avoided.

---

One-sentence “gold” answer

A create-table migration is a versioned, idempotent SQL script that defines schema structure, constraints, and indexes without embedding business logic.