Data Definition Language: Designing and Creating Tables

What Is Data Definition Language?

Data Definition Language (DDL) is the subset of SQL that creates, modifies, and removes the structure of a database rather than its data. The three core DDL statements are CREATE, ALTER, and DROP. In PostgreSQL 16, every object you build—tables, indexes, sequences, schemas—begins with DDL. Understanding DDL before writing a single INSERT statement prevents costly redesigns later.

Planning a Schema Before You Type

A schema is the blueprint of your database: which tables exist, what columns each table holds, what data types those columns use, and how tables relate to one another. Before opening psql or pgAdmin, sketch the entities (nouns in your domain), their attributes (columns), and their relationships (foreign keys). For a bookstore you might identify three entities: authors, books, and orders. Mapping these on paper or in a simple diagram catches problems before bad DDL reaches a production server.

Choosing the Right Data Type

PostgreSQL offers a rich type system. Picking the wrong type wastes storage, breaks calculations, or silently corrupts data. Use these as starting defaults:

• INTEGER — whole numbers that fit in 32 bits (–2 billion to +2 billion). Use for counts, IDs when you control the values.
• BIGINT — 64-bit whole numbers. Use when row counts may exceed ~2 billion.
• NUMERIC(precision, scale) — exact decimal arithmetic. Use for money, measurements where rounding errors are unacceptable.
• VARCHAR(n) — variable-length string with a maximum of n characters. Use when a hard upper bound exists (e.g., a two-letter state code).
• TEXT — variable-length string with no enforced limit. Use for descriptions, comments, long prose.
• BOOLEAN — stores TRUE, FALSE, or NULL. Use for binary flags.
• DATE — calendar date with no time component.
• TIMESTAMPTZ — timestamp with time-zone offset stored as UTC. Prefer this over TIMESTAMP whenever users or servers span time zones.
• UUID — 128-bit universally unique identifier. Use as a primary key when rows must be globally unique across systems.
• JSONB — binary-stored JSON. Use for semi-structured or variable-attribute data; supports indexing.

Avoid using TEXT for everything. It eliminates the type system's ability to catch errors at insertion time.

Writing CREATE TABLE

A `CREATE TABLE` statement names the table, lists columns with their types, and declares constraints inline or as table-level clauses.

```sql

CREATE TABLE authors (

author_id SERIAL PRIMARY KEY,

full_name VARCHAR(120) NOT NULL,

birth_date DATE,

UNIQUE (full_name)

);

```

Key points:

• SERIAL is shorthand for an auto-incrementing INTEGER sequence; use BIGSERIAL for larger tables.
• Column constraints (NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT, CHECK) placed on one column may be written inline.
• A PRIMARY KEY constraint enforces uniqueness and NOT NULL automatically.

Common Column Constraints

| Constraint | Purpose |

|---|---|

| NOT NULL | Prevents NULL from being stored in the column |

| UNIQUE | Guarantees no two rows share the same value |

| DEFAULT | Supplies a value when none is provided on INSERT |

| CHECK | Rejects rows where a Boolean expression is false |

| PRIMARY KEY | Uniquely identifies each row; combines UNIQUE + NOT NULL |

| FOREIGN KEY | Enforces referential integrity between tables |

```sql

CREATE TABLE books (

book_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

title VARCHAR(255) NOT NULL,

price NUMERIC(10,2) NOT NULL CHECK (price >= 0),

published DATE NOT NULL,

author_id INTEGER NOT NULL

REFERENCES authors(author_id)

ON DELETE RESTRICT

);

```

`ON DELETE RESTRICT` prevents deleting an author who still has books, protecting referential integrity.

Modifying and Removing Tables

`ALTER TABLE` changes an existing table's structure without dropping it:

```sql

ALTER TABLE books ADD COLUMN in_stock BOOLEAN NOT NULL DEFAULT TRUE;

ALTER TABLE books ALTER COLUMN title TYPE TEXT;

ALTER TABLE books DROP COLUMN in_stock;

```

`DROP TABLE` permanently removes a table and all its data:

```sql

DROP TABLE books; -- fails if another table references it

DROP TABLE books CASCADE; -- also drops dependent objects

```

Use `CASCADE` with caution. In a shared or production environment, dropping a table is irreversible unless a backup exists. Always verify you are connected to the correct database before executing DROP.

Verifying Your Schema

In psql, `\d tablename` displays columns, types, and constraints. You can also query the information schema, which is portable across SQL databases:

```sql

SELECT column_name, data_type, is_nullable

FROM information_schema.columns

WHERE table_name = 'books';

```

Compare this output against your specification before submitting any DDL script. Automated grading in this course checks `\d` output directly.

Safety Practices for Database Work

DDL statements execute immediately and cannot be rolled back by default in many workflows. Follow these practices:

1. Work in a development or test database until the schema is verified. Never build new schemas directly against production data.

2. Use transactions where supported: wrap DDL in `BEGIN; … COMMIT;` so you can `ROLLBACK` if something goes wrong within the same session.

3. Keep DDL scripts in version control (Git). Treat schema changes like code changes—review before applying.

4. Back up before DROP or destructive ALTER. In a professional environment, confirm a current backup exists before any destructive DDL.

These habits form the professional baseline expected in every workplace that handles database infrastructure.