Creare tabelle e vincoli: DDL

Finora hai operato su tabelle già esistenti (customers, orders…). DDL ("Data Definition Language") sono le istruzioni che creano e modificano lo schema stesso: tabelle, colonne, vincoli, indici.

CREATE TABLE: la sintassi

SQL

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

Esempio: una lista dei desideri ("wishlist") che collega clienti e prodotti:

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)
);

Analizziamola pezzo per pezzo.

I tipi più usati

Tipo	Cosa contiene
`INTEGER` / `INT`	Numeri interi (-2,1 mld … +2,1 mld)
`BIGINT`	Interi grandi
`NUMERIC(p, s)`	Decimali esatti — denaro, percentuali
`TEXT`	Stringhe di lunghezza variabile
`VARCHAR(n)`	Stringhe con limite massimo `n`
`BOOLEAN`	`TRUE` / `FALSE` / `NULL`
`DATE`	Solo data
`TIMESTAMP`	Data + ora (senza fuso)
`TIMESTAMPTZ`	Data + ora con fuso
`SERIAL`	`INTEGER` con sequenza auto-incrementale

I vincoli (constraints)

Un vincolo è una regola che PostgreSQL applica automaticamente: se un INSERT/UPDATE la viola, fallisce.

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

I vincoli si possono dichiarare inline alla colonna o come vincoli di tabella (sotto i campi). Esempio di UNIQUE composto:

SQL

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

CHECK constraint

CHECK impone una regola personalizzata:

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 deve essere ≥ 0; se qualcuno tenta INSERT … threshold = -5, fallisce con un errore di constraint.

Eliminare e modificare

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;

Prova tu

Esercizio#sql.m8.l3.e1

Tentativi: 0Caricamento…

Crea una tabella wishlists con queste colonne: 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 con DEFAULT CURRENT_DATE). Aggiungi un vincolo UNIQUE sulla coppia (customer_id, product_id).

Caricamento editor…

Mostra suggerimento

UNIQUE (customer_id, product_id) come vincolo di tabella in fondo.

Soluzione disponibile dopo 3 tentativi

Esercizio di ripasso

Esercizio#sql.m8.l3.e2

Tentativi: 0Caricamento…

Aggiungi alla tabella customers una colonna newsletter di tipo BOOLEAN, NOT NULL, con DEFAULT false. Usa una sola istruzione ALTER TABLE.

Caricamento editor…

Mostra suggerimento

Senza DEFAULT, l'ALTER fallirebbe perché esistono già righe e la nuova colonna è NOT NULL.

Soluzione disponibile dopo 3 tentativi