Creating tables and constraints: DDL

So far you've been working on tables that already exist (customers, orders…). DDL ("Data Definition Language") refers to the statements that create and modify the schema itself: tables, columns, constraints, indexes.

CREATE TABLE: the syntax

SQL

CREATE TABLE nome_tabella (
  colonna  TIPO  [vincoli_colonna],
  …,
  [vincoli_tabella]
);

Example: a wishlist that links customers and products:

SQL

CREATE TABLE wishlists (
  id           SERIAL PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers(id),
  product_id   INTEGER NOT NULL REFERENCES products(id),
  added_on     DATE NOT NULL DEFAULT CURRENT_DATE,
  note         TEXT,
  UNIQUE (customer_id, product_id)
);

Let's break it down piece by piece.

The most common types

Type	What it holds
`INTEGER` / `INT`	Whole numbers (-2.1B … +2.1B)
`BIGINT`	Large integers
`NUMERIC(p, s)`	Exact decimals — money, percentages
`TEXT`	Variable-length strings
`VARCHAR(n)`	Strings with a maximum length of `n`
`BOOLEAN`	`TRUE` / `FALSE` / `NULL`
`DATE`	Date only
`TIMESTAMP`	Date + time (no timezone)
`TIMESTAMPTZ`	Date + time with timezone
`SERIAL`	`INTEGER` with auto-incrementing sequence

Constraints

A constraint is a rule that PostgreSQL enforces automatically: if an INSERT/UPDATE violates it, the operation fails.

SQL

PRIMARY KEY                         -- identifica univocamente la riga, non NULL
NOT NULL                            -- vieta NULL
UNIQUE                              -- vieta duplicati su questa colonna
REFERENCES tabella(colonna)         -- foreign key
CHECK (espressione_booleana)        -- es. CHECK (price > 0)
DEFAULT espressione                 -- valore di default se non fornito

Constraints can be declared inline on a column or as table-level constraints (below the fields). Example of a composite UNIQUE:

SQL

UNIQUE (customer_id, product_id)   -- niente coppia (cliente, prodotto) duplicata

CHECK constraint

CHECK enforces a custom rule:

SQL

CREATE TABLE stock_alerts (
  product_id   INTEGER PRIMARY KEY REFERENCES products(id),
  threshold    INTEGER NOT NULL CHECK (threshold >= 0),
  notify_email TEXT    NOT NULL,
  active       BOOLEAN NOT NULL DEFAULT true
);

threshold must be ≥ 0; if someone tries INSERT … threshold = -5, it fails with a constraint error.

Dropping and altering

SQL

DROP TABLE wishlists;                                 -- elimina la tabella (e i dati!)
DROP TABLE IF EXISTS wishlists;                       -- senza errore se non esiste
ALTER TABLE customers ADD COLUMN newsletter BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE customers DROP COLUMN newsletter;

Your turn

Exercise#sql.m8.l3.e1

Attempts: 0Loading…

Create a wishlists table with the following columns: id (SERIAL PRIMARY KEY), customer_id (INTEGER NOT NULL REFERENCES customers(id)), product_id (INTEGER NOT NULL REFERENCES products(id)), added_on (DATE NOT NULL with DEFAULT CURRENT_DATE). Add a UNIQUE constraint on the pair (customer_id, product_id).

Loading editor…

Show hint

UNIQUE (customer_id, product_id) as a table-level constraint at the bottom.

Solution available after 3 attempts

Review exercise

Exercise#sql.m8.l3.e2

Attempts: 0Loading…

Add to the customers table a newsletter column of type BOOLEAN, NOT NULL, with DEFAULT false. Use a single ALTER TABLE statement.

Loading editor…

Show hint

Without DEFAULT, the ALTER would fail because rows already exist and the new column is NOT NULL.

Solution available after 3 attempts