Tabellen en beperkingen maken: DDL

Tot nu toe heb je gewerkt aan tabellen die al bestaan (customers, orders…). DDL ("Data Definition Language") verwijst naar de verklaringen die maak en wijzig het schema zelf: tabellen, kolommen, beperkingen, indexen.

MAAK TABEL: de syntaxis

SQL

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

Voorbeeld: een verlanglijstje dat klanten en producten koppelt:

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

Laten we het stuk voor stuk opsplitsen.

De meest voorkomende soorten

Typ	Wat het bevat
`INTEGER` / `INT`	Gehele getallen (-2,1B … +2,1B)
`BIGINT`	Grote gehele getallen
`NUMERIC(p, s)`	Exacte decimalen — geld, percentages
`TEXT`	Snaren met variabele lengte
`VARCHAR(n)`	Tekenreeksen met een maximale lengte van `n`
`BOOLEAN`	`TRUE` / `FALSE` / `NULL`
`DATE`	Alleen datum
`TIMESTAMP`	Datum + tijd (geen tijdzone)
`TIMESTAMPTZ`	Datum + tijd met tijdzone
`SERIAL`	`INTEGER` met automatisch oplopende reeks

Beperkingen

Een beperking is een regel die PostgreSQL automatisch afdwingt: als een INSERT/UPDATE deze overtreedt, mislukt de bewerking.

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

Beperkingen kunnen inline worden gedeclareerd op kolom- of op tabelniveau beperkingen (onder de velden). Voorbeeld van een samengestelde UNIQUE:

SQL

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

CHECK-beperking

CHECK dwingt een aangepaste regel af:

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 moet ≥ 0 zijn; als iemand INSERT … threshold = -5 probeert, is dat het geval mislukt met een beperkingsfout.

Laten vallen en veranderen

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;

Jouw beurt

Oefening#sql.m8.l3.e1

Pogingen: 0Laden…

Maak een verlanglijsttabel met de volgende kolommen: id (SERIAL PRIMARY KEY), customer_id (INTEGER NOT NULL REFERENCES customers(id)), product_id (INTEGER NOT NULL REFERENCES products(id)), add_on (DATE NOT NULL with DEFAULT CURRENT_DATE). Voeg een UNIEKe beperking toe aan het paar (klant_id, product_id).

Editor laden…

Toon hint

UNIEK (klant_id, product_id) als beperking op tabelniveau onderaan.

Oplossing beschikbaar na 3 pogingen

Oefening beoordelen

Oefening#sql.m8.l3.e2

Pogingen: 0Laden…

Voeg aan de klantentabel een nieuwsbriefkolom toe van het type BOOLEAN, NOT NULL, met DEFAULT false. Gebruik één ALTER TABLE-instructie.

Editor laden…

Toon hint

Zonder DEFAULT zou de ALTER mislukken omdat er al rijen bestaan en de nieuwe kolom NIET NULL is.

Oplossing beschikbaar na 3 pogingen