DDL, DML, and Constraints

CREATE, INSERT, UPDATE, DELETE — plus CHECK, UNIQUE, and NOT NULL that guard your data.

Fresher10 min read

Read these first

Normalization and Denormalization

Why this matters

Migrations are DDL; application bugs are often caught by constraints before corrupt rows ship.
INSERT/UPDATE/DELETE semantics differ in locking and trigger behaviour — know which operation your service performs.
Spring Data JPA generates DML, but engineers still write raw SQL for reports and incident fixes.
Interview questions pair constraint types with real examples: why UNIQUE on email, why CHECK on quantity.

Clustered (PK)

Table rows (sorted by PK)

Row 1id=1

Row 2id=2

Row 3id=3

Non-clustered (email)

email index→ row pointer

Table heapunordered rows

A clustered index sorts table rows by key. Non-clustered indexes point to row locations.

DDL: defining structure

Data Definition Language creates and alters schema objects:

Java

CREATE TABLE products (
    id           BIGSERIAL PRIMARY KEY,
    sku          VARCHAR(32) NOT NULL,
    name         TEXT NOT NULL,
    price_cents  INTEGER NOT NULL,
    stock        INTEGER NOT NULL DEFAULT 0,
    category_id  BIGINT REFERENCES categories(id),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_products_category ON products(category_id);

ALTER TABLE products ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

CREATE establishes tables and indexes; ALTER evolves schema without dropping data. VaultCommerce applies DDL through versioned Flyway migrations, never by hand in production.

DML: manipulating rows

Data Manipulation Language operates on table contents:

Java

-- Insert a new SKU
INSERT INTO products (sku, name, price_cents, stock, category_id)
VALUES ('VLT-9001', 'VaultCable USB-C', 1999, 500, 3);

-- Adjust price after supplier negotiation
UPDATE products
SET price_cents = 1799, updated_at = now()
WHERE sku = 'VLT-9001';

-- Remove discontinued item (prefer soft delete in production)
DELETE FROM products WHERE sku = 'VLT-OLD' AND stock = 0;

INSERT adds rows; UPDATE modifies matching rows; DELETE removes them. Each acquires row-level locks on affected tuples.

Constraint types

Constraints enforce rules at the database boundary:

VaultCommerce constraint toolkit

PRIMARY KEY — Unique, non-null row identifier (id).
FOREIGN KEY — order_items.product_id must reference an existing products.id.
UNIQUE — customers.email allows one account per address.
NOT NULL — orders.total_cents cannot be missing.
CHECK — Custom predicates: stock >= 0, price_cents > 0.

Java

ALTER TABLE products
  ADD CONSTRAINT positive_price CHECK (price_cents > 0),
  ADD CONSTRAINT non_negative_stock CHECK (stock >= 0);

ALTER TABLE customers
  ADD CONSTRAINT customers_email_unique UNIQUE (email);

Violations return SQLSTATE 23514 (check) or 23505 (unique) — map these to user-friendly API errors in the service layer.

DDL vs DML in application lifecycle

Aspect	DDL	DML
Purpose	Define/alter schema	Read/write rows
Frequency	Weekly migrations	Every API request
Locking	Can block table (ACCESS EXCLUSIVE)	Row-level locks
VaultCommerce tool	Flyway SQL scripts	JPA repositories + JDBC

Purpose
DDLDefine/alter schema
DMLRead/write rows
Frequency
DDLWeekly migrations
DMLEvery API request
Locking
DDLCan block table (ACCESS EXCLUSIVE)
DMLRow-level locks
VaultCommerce tool
DDLFlyway SQL scripts
DMLJPA repositories + JDBC

Safe DML patterns

Java

// Parameterised query — never concatenate user input
jdbcTemplate.update(
    "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?",
    qty, productId, qty
);

Parameterized statements prevent SQL injection and let Postgres cache query plans. Check getUpdateCount() — zero rows updated means insufficient stock without throwing a constraint error.

Quick recall

Everything you need if you only revisit this box.

DDL (CREATE, ALTER) defines tables, indexes, and constraints; DML (INSERT, UPDATE, DELETE, SELECT) manipulates rows.
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK guard data integrity at the boundary.
VaultCommerce uses Flyway for DDL and JPA/JDBC for DML.
Parameterised DML prevents injection and enables plan reuse.
Prefer soft deletes and restricted DELETE grants on production transactional tables.

Test yourself

Answer these before moving on — recall is what makes it stick.