⚡ CrackLab
beginner~4h

SQL Fundamentals — DDL, DML & Constraints

SQL (Structured Query Language) is the standard language for managing relational databases. Every backend engineer, data engineer, and solution architect must master SQL fundamentals.

Every other SQL topic assumes you already know how to shape and guard data at the schema level. DDL/DML/constraints are the vocabulary the rest of the language is written in — you can't discuss a JOIN, a transaction, or an index meaningfully until you know what a table, a row, and a constraint actually are.

Without constraints enforced by the database itself, data integrity depends entirely on every application, script, and manual query getting validation right, every single time, forever. One missed check in one code path is enough to let invalid data in — and once it's in, it silently corrupts every report and decision built on top of it.

DDL (Data Definition Language) — statements that define schema structure: CREATE, ALTER, DROP. DML (Data Manipulation Language) — statements that manipulate data: SELECT, INSERT, UPDATE, DELETE. Constraint — a rule enforced by the database itself (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) that rejects any write violating it, regardless of which application wrote it.

SQL (Structured Query Language) is the standard language for managing relational databases. Every backend engineer, data engineer, and solution architect must master SQL fundamentals.

SQL is divided into sublanguages

  • DDL (Data Definition Language) — defines database structure: CREATE, ALTER, DROP, TRUNCATE.

  • DML (Data Manipulation Language) — manipulates data: SELECT, INSERT, UPDATE, DELETE.

  • DCL (Data Control Language) — controls access: GRANT, REVOKE.

  • TCL (Transaction Control Language) — manages transactions: COMMIT, ROLLBACK, SAVEPOINT.

Database Constraints enforce data integrity

  • PRIMARY KEY — uniquely identifies each row; NOT NULL + UNIQUE combined.

  • FOREIGN KEY — enforces referential integrity between tables.

  • UNIQUE — ensures column values are distinct.

  • NOT NULL — disallows null values.

  • CHECK — validates column values against an expression.

  • DEFAULT — provides a default value when none is specified.

PostgreSQL 17 additions: temporal tables, improved MERGE, enhanced JSONB, better partitioning.

DDL Execution Flow

  • Parser validates SQL syntax → generates parse tree.

  • Analyzer resolves identifiers (table names, column names).

  • Planner determines execution strategy.

  • Executor performs the operation.

  • Catalog updated (pg_class, pg_attribute for PostgreSQL).

  • Transaction committed (DDL is auto-committed in most databases; PostgreSQL wraps DDL in transactions).

DML Execution

  • Parser → Analyzer → Rewriter → Planner → Optimizer → Executor.

  • For INSERT: data written to heap pages; indexes updated.

  • For UPDATE: old row marked as dead (PostgreSQL MVCC); new row written.

  • For DELETE: row marked as dead; VACUUM later reclaims space.

  • WAL (Write-Ahead Log) records changes for durability.

Step 1: Create database and schema — establish the logical namespace.

Step 2: CREATE TABLE with constraints — define structure and integrity rules.

Step 3: INSERT data — populate tables; constraints validated on each row.

Step 4: UPDATE/DELETE — modify data; foreign keys checked (ON DELETE actions).

Step 5: ALTER TABLE — add/modify columns and constraints without recreating table.

Step 6: Use TRUNCATE vs DELETE — TRUNCATE is DDL (faster, no row-by-row logging); DELETE is DML.

Step 7: DROP TABLE — removes table permanently; CASCADE drops dependent objects.

  • Always define PRIMARY KEY on every table — every row needs a unique identifier.

  • Use SERIAL or IDENTITY (PostgreSQL 17) for surrogate primary keys; never rely on natural keys for PK.

  • Add FOREIGN KEY constraints — enforce referential integrity at database level, not application level.

  • Use CHECK constraints for business rules — salary > 0, age BETWEEN 18 AND 100.

  • Always specify NOT NULL — null-handling is a common source of bugs; be explicit.

  • Use VARCHAR with max length instead of TEXT for indexed columns — improves index efficiency.

  • Default values reduce application logic — DEFAULT NOW(), DEFAULT TRUE.

  • CASCADE DELETE carefully — can cause unintended mass deletions; prefer SET NULL or RESTRICT.

  • Use TIMESTAMPTZ (timestamp with timezone) not TIMESTAMP in PostgreSQL for global apps.

  • Run DDL in transactions (PostgreSQL supports this) — roll back if multi-step migration fails.

  • Using NULL as a business value — NULL means "unknown", not zero or empty string.

  • Forgetting ON DELETE action on FOREIGN KEY — default RESTRICT may surprise application developers.

  • VARCHAR without length limit — use TEXT for truly unlimited; VARCHAR for bounded strings.

  • Using FLOAT for money — always use DECIMAL/NUMERIC for monetary values; floating-point imprecision.

  • No DEFAULT on nullable columns — every nullable column should have a sensible default.

  • DROP TABLE without IF EXISTS — fails if table doesn't exist; use IF EXISTS for scripts.

  • TRUNCATE inside a transaction — some databases (MySQL) cause implicit commit.

  • Adding NOT NULL column to large table — locks table in MySQL; use online DDL or default value trick.

  • TRUNCATE is ~100x faster than DELETE for clearing a table (no row-level logging).

  • Use COPY (PostgreSQL) or LOAD DATA INFILE (MySQL) for bulk inserts — 10-100x faster than INSERT.

  • Defer constraint checking: SET CONSTRAINTS ALL DEFERRED — allows loading data in any order.

  • Partial indexes on filtered tables: CREATE INDEX ON orders(status) WHERE status = 'PENDING'.

  • Use UNLOGGED tables for temporary staging data (PostgreSQL) — no WAL overhead, 3-5x faster.

  • Batch INSERTs: INSERT INTO ... VALUES (...),(...),(...) — 100 rows per statement is optimal.

  • pg_dump/restore for schema migration — faster than DDL scripts for large schemas.

Constraints are a security boundary, not just a data-quality one — a CHECK constraint or FOREIGN KEY prevents a compromised or buggy application from writing data that violates business rules, even if that application's own validation was bypassed entirely. Grant DDL privileges (CREATE/ALTER/DROP) only to migration tooling and admins, never to a general application role — an app role only ever needs DML privileges.

Watch for constraint-violation errors in application logs as a leading indicator of a bug upstream — a spike in NOT NULL/UNIQUE/CHECK violations usually means a new code path is generating bad data, not that the constraint itself is wrong. Track failed-write rates per table after any schema migration, since a newly added constraint can reject writes that used to silently succeed.

  • Use Flyway or Liquibase for schema migrations — versioned, auditable, reversible.

  • Never run DDL directly on production — always through a migration tool with review process.

  • Test constraint additions on a production-sized copy first — some take table locks.

  • Monitor table bloat with pg_stat_user_tables — high dead tuples need VACUUM.

  • Use CREATE INDEX CONCURRENTLY in PostgreSQL — builds index without locking table.

  • For MySQL: use pt-online-schema-change for large table DDL.

  • Document all constraints and their business reasons in a Data Dictionary.

  • Create the employees/departments schema from this chapter. Add a CHECK constraint that hire_date cannot be in the future. Test it by trying to insert a future date.

  • Add a new column 'performance_rating' (1-5) to the employees table with a CHECK constraint. Add DEFAULT 3. Verify the default is applied to existing rows.

  • Practice TRUNCATE vs DELETE: insert 1000 rows, time a DELETE of all rows, then re-insert and time a TRUNCATE. Observe the difference.

  • Create a migration script using proper transaction: BEGIN; ALTER TABLE; CREATE INDEX CONCURRENTLY (must be outside transaction); COMMIT; — note why index creation must be separate.

  • DDL: CREATE, ALTER, DROP, TRUNCATE — defines structure (transactional in PostgreSQL).

  • DML: SELECT, INSERT, UPDATE, DELETE — manipulates data; fully transactional.

  • Constraints: PRIMARY KEY (unique+not null), FOREIGN KEY (referential integrity), UNIQUE, NOT NULL, CHECK, DEFAULT.

  • TRUNCATE vs DELETE: TRUNCATE is DDL, minimal logging, O(1); DELETE is DML, row-logged, O(n).

  • Use DECIMAL/NUMERIC for money; TIMESTAMPTZ for global apps; SERIAL for auto-increment.

  • ON DELETE: CASCADE (delete children), SET NULL, RESTRICT (prevent).

  • Zero-downtime DDL on large tables: PostgreSQL 11+ instant defaults; MySQL gh-ost.

Want a visual for this concept?

Generate a diagram tailored to “SQL Fundamentals — DDL, DML & Constraints” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Sign in to generate a visual →

Practice quiz

Next Step

Continue to SELECT Queries — WHERE, GROUP BY, HAVING, ORDER BY →← Back to all SQL chapters