El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Portes cinc mòduls llegint l'esquema de BotigaVerda sense haver-ne escrit ni una línia. Saps que clients.email és UNIQUE, que linies_comanda.comanda_id és ON DELETE CASCADE, que puntuacio està protegida amb un CHECK d'1 a 5 i que totes les claus primàries són id INTEGER GENERATED BY DEFAULT AS IDENTITY. Ho saps perquè ho has llegit a la lliçó 01-06 i perquè els errors de PostgreSQL t'ho han recordat més d'una vegada. El que encara no saps és com es declara tot això.

Aquí comença l'altre costat del llenguatge: el DDL, el subllenguatge de definició de dades del qual parlava 01-01. En aquesta lliçó aprendràs la sintaxi completa de CREATE TABLE —columnes, tipus i les sis restriccions, una a una—, per què convé posar-los nom, com funcionen de debò les columnes d'identitat i en què es diferencien dels SERIAL de tota la vida, què són les columnes generades, com crear taules temporals o a partir d'una consulta, i com s'esborren respectant l'ordre que imposa la integritat referencial. En acabar podràs llegir l'script botigaverda.sql de dalt a baix entenent per què cada decisió és la que és, i en podràs escriure un de teu.

Contingut

Del catàleg mort a la botiga viva
Anatomia de CREATE TABLE
Les restriccions, una a una
A nivell de columna o a nivell de taula
Anomenar les restriccions i per què importa
Columnes d'identitat: IDENTITY, SERIAL i la resta de motors
Columnes generades
IF NOT EXISTS, CREATE TABLE AS SELECT i taules temporals
DROP TABLE i l'ordre que imposa la integritat referencial
Recorregut comentat del DDL de BotigaVerda
Errors habituals i consells
Exercicis
Conclusió

Del catàleg mort a la botiga viva

El mòdul 4 es va tancar amb una frase: "una botiga que no pot donar d'alta un producte, registrar una comanda, corregir un preu ni cancel·lar una compra no és una botiga: és un catàleg mort". Creuar a l'altre costat comença aquí, i comença pel més bàsic: abans de poder inserir una fila cal tenir on posar-la.

Un recordatori de 01-01 sobre els cinc subllenguatges d'SQL, ara amb el mòdul 5 situat al mapa:

Subllenguatge	Instruccions	On s'estudia
DQL — consulta	`SELECT`	Mòduls 2, 3, 4, 6, 7
DDL — definició	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`	05-01 i 05-06
DML — manipulació	`INSERT`, `UPDATE`, `DELETE`, `MERGE`	05-02 a 05-05
TCL — transaccions	`BEGIN`, `COMMIT`, `ROLLBACK`	Mòdul 9 (ús bàsic des de 05-03)
DCL — control	`GRANT`, `REVOKE`	Lliçó 11-03

I un canvi de mentalitat que convé interioritzar ja: el DDL defineix les regles que la base de dades farà complir per tu. Cada NOT NULL, cada CHECK, cada FOREIGN KEY que escriguis és un error que la teva aplicació no podrà cometre mai, ni avui ni d'aquí a tres anys, ni des del codi, ni des d'un script, ni des d'una consola oberta a les tres de la matinada. És la diferència entre confiar que tothom es recordi de validar i que sigui impossible no validar.

Anatomia de `CREATE TABLE`

La forma general:

CREATE TABLE nom_taula (
    columna1  TIPUS  [restriccions de columna],
    columna2  TIPUS  [restriccions de columna],
    ...
    [restriccions de taula]
);

Comencem per la taula més simple de BotigaVerda, categories, tal com és a l'script del curs:

CREATE TABLE categories (
    id          INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom         VARCHAR(60)  NOT NULL UNIQUE,
    descripcio  TEXT
);

CREATE TABLE

Tres columnes i quatre decisions ja preses:

Element	Decisió	Per què
`id INTEGER`	Clau subrogada	01-05: uniformitat, estabilitat i eficiència
`GENERATED BY DEFAULT AS IDENTITY`	El motor genera el valor	Sense seqüències manuals ni risc de col·lisió
`nom VARCHAR(60) NOT NULL UNIQUE`	Obligatori i únic	És la clau natural de la taula (01-05, secció 3)
`descripcio TEXT`	Sense restriccions	Pot faltar i no té cap límit raonable de longitud

Fixa't que descripcio no porta NULL explícit. En SQL, una columna admet nuls llevat que diguis el contrari. Escriure descripcio TEXT NULL és legal i significa exactament el mateix; el curs no ho fa perquè afegeix soroll.

Comprovar el que has creat

Dins de psql, \d et retorna la definició real tal com la veu el motor:

botigaverda=> \d categories

                             Table "public.categories"
   Column    |         Type          | Nullable |           Default
-------------+-----------------------+----------+----------------------------------
 id          | integer               | not null | generated by default as identity
 nom         | character varying(60) | not null |
 descripcio  | text                  |          |
Indexes:
    "categories_pkey" PRIMARY KEY, btree (id)
    "categories_nom_key" UNIQUE CONSTRAINT, btree (nom)
Referenced by:
    TABLE "productes" CONSTRAINT "productes_categoria_id_fkey" FOREIGN KEY (categoria_id) REFERENCES categories(id) ON DELETE RESTRICT

Aquesta sortida és la teva millor eina de diagnòstic durant tot el mòdul. Et diu els tipus, si admeten nuls, els valors per omissió, els índexs que sustenten la PK i el UNIQUE, i quines altres taules depenen d'aquesta. Agafa-li estima.

Les restriccions, una a una

SQL té sis restriccions declaratives. Aquestes són, en ordre de menor a major abast:

Restricció	Què garanteix	Abast
`NOT NULL`	La columna sempre té valor	Una cel·la
`DEFAULT`	Valor si no s'indica (no és una restricció estricta)	Una cel·la
`CHECK`	El valor compleix una condició	Una fila
`UNIQUE`	No hi ha dues files amb el mateix valor	La taula
`PRIMARY KEY`	`UNIQUE` + `NOT NULL`, i només una per taula	La taula
`FOREIGN KEY`	El valor existeix en una altra taula	Dues taules

3.1. `NOT NULL`

La més simple i la més oblidada.

nom VARCHAR(150) NOT NULL

Qualsevol INSERT o UPDATE que deixi aquella columna sense valor es rebutja:

ERROR:  null value in column "nom" of relation "productes" violates not-null constraint
DETAIL:  Failing row contains (21, null, 1, 1, 2.60, 1.15, 140, t, 2026-03-01).

La decisió de si una columna admet nuls no és tècnica, és de negoci, i ja la vas prendre conceptualment a 04-03. A BotigaVerda:

Columna	Nuls?	Raó
`comandes.client_id`	No	Una comanda sense client no significa res
`comandes.empleat_id`	Sí	`NULL` = comanda web, sense comercial. És una dada que no existeix
`productes.cost`	Sí	Es pot desconèixer en donar d'alta un producte
`productes.preu`	No	Sense preu no es pot vendre

Regla pràctica: declara NOT NULL per defecte i admet nuls només quan tinguis una resposta clara a la pregunta "què vol dir que aquí no hi hagi res?". Una columna nul·lable sense semàntica documentada és una font garantida d'errors.

3.2. `DEFAULT`

El valor que es fa servir si l'INSERT no esmenta la columna:

stock      INTEGER NOT NULL DEFAULT 0,
actiu      BOOLEAN NOT NULL DEFAULT TRUE,
data_alta  DATE    NOT NULL DEFAULT CURRENT_DATE

Els tres casos que veuràs a la pràctica:

Tipus de `DEFAULT`	Exemple	Quan s'avalua
Constant	`DEFAULT 0`, `DEFAULT TRUE`, `DEFAULT 'pendent'`	Es desa tal qual
Funció	`DEFAULT CURRENT_DATE`, `DEFAULT NOW()`	En el moment d'inserir cada fila, no en crear la taula
Expressió	`DEFAULT (CURRENT_DATE + 30)`	Igual: a cada inserció

Aquest matís de la funció és important i confon molta gent: DEFAULT CURRENT_DATE no congela la data en què vas crear la taula. Cada fila rep la data del dia en què es va inserir.

-- En donar d'alta un producte avui, data_alta s'omple sola
INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock)
VALUES ('Cigrons ecològics 500 g', 1, 1, 2.60, 1.15, 140);

I les tres columnes omeses (actiu, data_alta i la mateixa id) s'omplen amb els seus valors per omissió. DEFAULT és el que fa que un INSERT curt continuï produint una fila completa i coherent.

DEFAULT no és una restricció. No impedeix res: només omple buits. Si insereixes NULL explícitament en una columna amb DEFAULT, es desa NULL (o falla, si hi ha NOT NULL). El DEFAULT només actua quan omets la columna.

3.3. `PRIMARY KEY`

Marca la columna —o el conjunt de columnes— que identifica cada fila. Implica UNIQUE i NOT NULL alhora, i només n'hi pot haver una per taula.

Forma simple, a nivell de columna:

id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Forma composta, obligatòriament a nivell de taula, perquè afecta diverses columnes:

-- Exemple didàctic: una taula pont SENSE id propi.
-- No forma part de BotigaVerda; serveix per veure la sintaxi.
CREATE TABLE productes_etiquetes (
    producte_id  INTEGER NOT NULL REFERENCES productes(id)  ON DELETE CASCADE,
    etiqueta     VARCHAR(40) NOT NULL,
    data_alta    DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (producte_id, etiqueta)
);

CREATE TABLE

Es llegeix: "un producte no pot portar dues vegades la mateixa etiqueta". La PK composta és la regla de negoci; no cal cap UNIQUE addicional.

Aquesta era exactament l'alternativa que 01-05 va plantejar per a linies_comanda i que BotigaVerda no va triar:

-- Alternativa NO triada per a linies_comanda
PRIMARY KEY (comanda_id, producte_id)

Hauria significat "un producte només pot aparèixer una vegada a cada comanda", i això impediria facturar dues línies del mateix producte amb descomptes diferents. Per això linies_comanda té el seu propi id.

	PK composta	`id` subrogat + `UNIQUE`
Expressa la regla de negoci	Directament	Amb un `UNIQUE` a part
Referenciar la fila des d'una altra taula	Cal copiar totes les columnes	N'hi ha prou amb un `INTEGER`
Uniformitat de l'esquema	Trenca el patró `id`	El manté
Quan triar-la	Taules pont pures, sense fills	Gairebé sempre la resta

3.4. `UNIQUE`

Impedeix valors repetits. A diferència de PRIMARY KEY, en pots tenir tants com vulguis i sí que admet nuls.

email VARCHAR(120) NOT NULL UNIQUE

En intentar registrar dues vegades el mateix email:

ERROR:  duplicate key value violates unique constraint "clients_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

Únic compost, a nivell de taula:

-- Un client, una ressenya per producte.
-- Exemple puntual: BotigaVerda NO porta avui aquesta restricció.
CREATE TABLE ressenyes_v2 (
    id          INTEGER  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    producte_id INTEGER  NOT NULL REFERENCES productes(id) ON DELETE CASCADE,
    client_id   INTEGER  NOT NULL REFERENCES clients(id)   ON DELETE CASCADE,
    puntuacio   SMALLINT NOT NULL CHECK (puntuacio BETWEEN 1 AND 5),
    data        DATE     NOT NULL,
    UNIQUE (producte_id, client_id)
);

Compte amb la semàntica del UNIQUE compost: prohibeix repetir la combinació, no cada columna per separat. El mateix client pot ressenyar vint productes i el mateix producte pot rebre vint ressenyes; el que no hi pot haver és dues files amb la mateixa parella.

`UNIQUE` i els `NULL`: reprenent 04-03

Aquí torna la lògica de tres valors. Com que NULL no és igual a NULL, una columna UNIQUE pot contenir moltes files nul·les:

CREATE TABLE prova_unique (
    id   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    codi VARCHAR(20) UNIQUE
);

INSERT INTO prova_unique (codi) VALUES ('A'), (NULL), (NULL), (NULL);

INSERT 0 4

SELECT id, codi FROM prova_unique ORDER BY id;

id	codi
1	A
2	(null)
3	(null)
4	(null)

Tres nuls conviuen sense cap problema en una columna UNIQUE, mentre que una segona 'A' hauria fallat. Des de PostgreSQL 15 pots canviar aquest comportament:

codi VARCHAR(20) UNIQUE NULLS NOT DISTINCT

Amb això, el segon NULL donaria error. És útil quan el nul significa "sense codi" i vols que només hi pugui haver una fila així.

Nota de dialecte: aquest comportament no és universal. PostgreSQL, Oracle i SQLite permeten diversos nuls en una columna única; SQL Server en permet només un (tracta tots els NULL com a iguals a efectes de l'índex únic). Si migres un esquema entre motors, és una de les trampes més silencioses.

I una nota que veuràs desenvolupada al mòdul 8: tant PRIMARY KEY com UNIQUE s'implementen creant un índex per sota. Per això són restriccions barates de comprovar i per això ocupen espai en disc. Les estructures i el cost, al seu moment.

3.5. `CHECK`

Restringeix els valors admissibles amb una expressió booleana. És la restricció més expressiva i la més infrautilitzada.

BotigaVerda fa servir sis tipus de CHECK. Aquests són els reals de l'script:

-- Domini tancat de valors (comandes)
estat           VARCHAR(20) NOT NULL
                CHECK (estat IN ('pendent','pagat','enviat','lliurat','cancellat')),
metode_pagament VARCHAR(20) NOT NULL
                CHECK (metode_pagament IN ('targeta','transferencia','paypal','contrareemborsament')),

-- Rang tancat (ressenyes)
puntuacio       SMALLINT NOT NULL CHECK (puntuacio BETWEEN 1 AND 5),

-- Positivitat estricta (linies_comanda)
quantitat       INTEGER NOT NULL CHECK (quantitat > 0),

-- Fracció entre 0 i 1 (linies_comanda)
descompte       NUMERIC(4,2) NOT NULL DEFAULT 0
                CHECK (descompte >= 0 AND descompte <= 1),

-- No negativitat dels diners (productes, comandes, devolucions, empleats)
preu            NUMERIC(10,2) NOT NULL CHECK (preu >= 0)

Cadascun protegeix una regla que el tipus per si sol no garanteix. SMALLINT admet 7 i admet −3; el CHECK és el que impedeix una ressenya de 7 estrelles. NUMERIC(4,2) admet 99.99; el CHECK és el que impedeix un descompte del 9999 %.

Un CHECK amb més d'una columna s'ha de declarar a nivell de taula, perquè a nivell de columna només es pot referir a la seva:

-- Exemple puntual, no és a BotigaVerda
CREATE TABLE productes_v2 (
    id   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom  VARCHAR(150)  NOT NULL,
    preu NUMERIC(10,2) NOT NULL,
    cost NUMERIC(10,2),
    CHECK (cost IS NULL OR cost <= preu)     -- marge mai negatiu
);

Fixa't en el cost IS NULL OR. Sense ell, la restricció seria inútil d'una manera subtil: NULL <= preu dóna UNKNOWN, i un CHECK que dóna UNKNOWN es considera satisfet. És la regla que més sorprèn dels CHECK:

Resultat de l'expressió	S'accepta la fila?
`TRUE`	Sí
`FALSE`	No
`UNKNOWN` (per un `NULL`)	Sí

És a dir, CHECK deixa passar els nuls llevat que els prohibeixis expressament. Si vols exigir valor, això és feina de NOT NULL, no del CHECK.

Què no pot fer un CHECK:

No pot consultar altres taules. CHECK (preu > (SELECT AVG(preu) FROM productes)) no és vàlid: PostgreSQL rebutja subconsultes en un CHECK. Per a regles entre taules hi ha les claus foranes i, si no n'hi ha prou, els triggers del mòdul 10.
No pot fer servir funcions no deterministes. CHECK (data_alta <= CURRENT_DATE) està desaconsellat i PostgreSQL ho permet però avisa a la documentació: una fila vàlida avui podria deixar de ser-ho demà, i una restauració de còpia de seguretat fallaria sense motiu aparent.

3.6. `FOREIGN KEY`, `ON DELETE` i `ON UPDATE`

La restricció que connecta les taules i garanteix la integritat referencial de 01-05. Té dues sintaxis equivalents:

-- A nivell de columna, amb REFERENCES (la que fa servir BotigaVerda)
categoria_id INTEGER REFERENCES categories(id) ON DELETE RESTRICT

-- A nivell de taula, amb FOREIGN KEY (obligatòria si la clau és composta)
FOREIGN KEY (categoria_id) REFERENCES categories(id) ON DELETE RESTRICT

La forma completa:

[CONSTRAINT nom]
FOREIGN KEY (col1 [, col2 ...])
REFERENCES taula_pare (col1 [, col2 ...])
[ON DELETE accio]
[ON UPDATE accio]

I les cinc accions possibles, ja conegudes de 01-05, ara amb la seva sintaxi:

Acció	Sintaxi	Què fa en esborrar el pare
Per defecte	(res) → `NO ACTION`	Rebutja amb error, comprovant al final de la sentència
Rebuig immediat	`ON DELETE RESTRICT`	Rebutja sense esperar
Propagar l'esborrat	`ON DELETE CASCADE`	Esborra també les files filles
Anul·lar la referència	`ON DELETE SET NULL`	Posa `NULL` a la FK de les filles (exigeix columna nul·lable)
Valor per omissió	`ON DELETE SET DEFAULT`	Posa el `DEFAULT` de la columna filla (que ha d'existir al pare)

La distinció NO ACTION / RESTRICT és subtil i gairebé sempre irrellevant: NO ACTION permet que una altra part de la mateixa sentència arregli la situació abans de la comprovació final; RESTRICT, no. A la pràctica, tots dos es tradueixen en "no em deixis fer-ho".

Aplicat a l'esquema real:

CREATE TABLE linies_comanda (
    id            INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    comanda_id    INTEGER       NOT NULL REFERENCES comandes(id)  ON DELETE CASCADE,
    producte_id   INTEGER       NOT NULL REFERENCES productes(id) ON DELETE RESTRICT,
    quantitat     INTEGER       NOT NULL CHECK (quantitat > 0),
    preu_unitari  NUMERIC(10,2) NOT NULL CHECK (preu_unitari >= 0),
    descompte     NUMERIC(4,2)  NOT NULL DEFAULT 0
                  CHECK (descompte >= 0 AND descompte <= 1)
);

Dues FK a la mateixa taula amb accions oposades, i totes dues correctes: una línia no existeix sense la seva comanda (CASCADE), però un producte venut no s'ha de poder esborrar mai (RESTRICT), perquè destruiria l'històric de facturació.

Sobre ON UPDATE: es dispara quan canvia la clau primària del pare. Amb claus subrogades no es fa servir mai, perquè un id autoincremental no canvia. És precisament un dels avantatges de les claus subrogades que 01-05 enumerava. Si la teva PK fos natural (un codi d'article, un NIF), ON UPDATE CASCADE passaria a ser imprescindible.

Totes les FK de BotigaVerda, amb la seva sintaxi

Taula	Columna	Referència	Acció declarada
`productes`	`categoria_id`	`categories(id)`	`ON DELETE RESTRICT`
`productes`	`proveidor_id`	`proveidors(id)`	`ON DELETE RESTRICT`
`clients`	`referit_per_id`	`clients(id)`	`ON DELETE SET NULL`
`empleats`	`cap_id`	`empleats(id)`	`ON DELETE SET NULL`
`comandes`	`client_id`	`clients(id)`	`ON DELETE RESTRICT`
`comandes`	`empleat_id`	`empleats(id)`	`ON DELETE SET NULL`
`linies_comanda`	`comanda_id`	`comandes(id)`	`ON DELETE CASCADE`
`linies_comanda`	`producte_id`	`productes(id)`	`ON DELETE RESTRICT`
`ressenyes`	`producte_id`	`productes(id)`	`ON DELETE CASCADE`
`ressenyes`	`client_id`	`clients(id)`	`ON DELETE CASCADE`
`devolucions`	`comanda_id`	`comandes(id)`	`ON DELETE CASCADE`

Onze claus foranes, quatre CASCADE, tres SET NULL i quatre RESTRICT. Veuràs les tres accions en funcionament, amb recomptes abans i després, a la lliçó 05-04.

A nivell de columna o a nivell de taula

Totes les restriccions llevat de NOT NULL i DEFAULT es poden escriure de dues maneres.

A nivell de columna, just darrere del tipus:

CREATE TABLE proveidors (
    id     INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom    VARCHAR(120) NOT NULL,
    pais   VARCHAR(60)  NOT NULL,
    email  VARCHAR(120),
    actiu  BOOLEAN      NOT NULL DEFAULT TRUE
);

A nivell de taula, al final, com a elements separats per comes:

CREATE TABLE proveidors (
    id     INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    nom    VARCHAR(120) NOT NULL,
    pais   VARCHAR(60)  NOT NULL,
    email  VARCHAR(120),
    actiu  BOOLEAN      NOT NULL DEFAULT TRUE,
    PRIMARY KEY (id),
    CHECK (pais IN ('Espanya','Portugal','França','Alemanya'))
);

Quan fer servir cadascuna:

Situació	Forma
Restricció sobre una columna	Nivell de columna: més compacte i es llegeix al costat del tipus
Restricció sobre diverses columnes	Obligatòriament nivell de taula (`PRIMARY KEY (a, b)`, `UNIQUE (a, b)`, `CHECK (a <= b)`)
Vols anomenar la restricció	Qualsevol de les dues, però a nivell de taula queda més llegible

Anomenar les restriccions i per què importa

Si no li poses nom, PostgreSQL en genera un seguint un patró fix:

Restricció	Nom autogenerat
`PRIMARY KEY`	`<taula>_pkey`
`UNIQUE`	`<taula>_<columna>_key`
`FOREIGN KEY`	`<taula>_<columna>_fkey`
`CHECK`	`<taula>_<columna>_check`
`NOT NULL`	(no és una restricció amb nom propi)

Per això tots els errors que has anat veient al curs tenen aquella forma: categories_nom_key, ressenyes_puntuacio_check, comandes_client_id_fkey.

El patró funciona bé mentre hi ha una restricció per columna. Tan bon punt n'hi ha dues, PostgreSQL comença a numerar:

CREATE TABLE demo_noms (
    id   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    preu NUMERIC(10,2) NOT NULL CHECK (preu >= 0) CHECK (preu < 10000)
);

botigaverda=> \d demo_noms

Check constraints:
    "demo_noms_preu_check" CHECK (preu >= 0::numeric)
    "demo_noms_preu_check1" CHECK (preu < 10000::numeric)

demo_noms_preu_check1. Quina de les dues era? Impossible saber-ho sense mirar la definició. I ara imagina't aquell nom en un missatge d'error, a les onze de la nit, en un log de producció.

La versió amb noms explícits:

CREATE TABLE demo_noms (
    id   INTEGER GENERATED BY DEFAULT AS IDENTITY,
    preu NUMERIC(10,2) NOT NULL,
    CONSTRAINT pk_demo_noms        PRIMARY KEY (id),
    CONSTRAINT chk_demo_preu_min   CHECK (preu >= 0),
    CONSTRAINT chk_demo_preu_maxim CHECK (preu < 10000)
);

I l'error deixa de ser un jeroglífic:

ERROR:  new row for relation "demo_noms" violates check constraint "chk_demo_preu_maxim"
DETAIL:  Failing row contains (1, 25000.00).

Aplicat a BotigaVerda, la taula comandes quedaria així:

CREATE TABLE comandes (
    id                 INTEGER       GENERATED BY DEFAULT AS IDENTITY,
    client_id          INTEGER       NOT NULL,
    empleat_id         INTEGER,
    data_comanda       DATE          NOT NULL,
    estat              VARCHAR(20)   NOT NULL,
    metode_pagament    VARCHAR(20)   NOT NULL,
    despeses_enviament NUMERIC(10,2) NOT NULL DEFAULT 0,

    CONSTRAINT pk_comandes          PRIMARY KEY (id),
    CONSTRAINT fk_comandes_client   FOREIGN KEY (client_id)
               REFERENCES clients(id)  ON DELETE RESTRICT,
    CONSTRAINT fk_comandes_empleat  FOREIGN KEY (empleat_id)
               REFERENCES empleats(id) ON DELETE SET NULL,
    CONSTRAINT chk_comandes_estat
               CHECK (estat IN ('pendent','pagat','enviat','lliurat','cancellat')),
    CONSTRAINT chk_comandes_metode_pagament
               CHECK (metode_pagament IN ('targeta','transferencia','paypal','contrareemborsament')),
    CONSTRAINT chk_comandes_despeses_enviament
               CHECK (despeses_enviament >= 0)
);

Les tres raons per les quals val la pena aquella verbositat:

Missatges d'error llegibles. chk_comandes_estat et diu quina regla has trencat sense obrir l'esquema.
Migracions. Per treure o modificar una restricció cal anomenar-la: ALTER TABLE comandes DROP CONSTRAINT chk_comandes_estat; (lliçó 05-06). Amb noms autogenerats, cada migració comença amb una recerca arqueològica a information_schema.
Portabilitat i reproductibilitat. Dos entorns creats amb scripts lleugerament diferents poden acabar amb ..._check i ..._check1 intercanviats. Els noms explícits eliminen aquella loteria.

Una convenció de noms raonable i molt estesa:

Prefix	Restricció	Exemple
`pk_`	`PRIMARY KEY`	`pk_comandes`
`fk_`	`FOREIGN KEY`	`fk_comandes_client`
`uq_`	`UNIQUE`	`uq_clients_email`
`chk_`	`CHECK`	`chk_comandes_estat`

Per què l'script del curs no els fa servir. botigaverda.sql prescindeix dels noms explícits deliberadament: així els missatges d'error que veus a les lliçons són els que PostgreSQL genera de fàbrica, que és el que et trobaràs en connectar-te a qualsevol base de dades aliena. En un projecte teu, anomena-les.

Columnes d'identitat: `IDENTITY`, `SERIAL` i la resta de motors

Totes les PK de BotigaVerda són així:

id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Al darrere hi ha una seqüència: un objecte del motor que va lliurant números creixents. Quan insereixes sense indicar id, PostgreSQL demana el valor següent a la seqüència.

`BY DEFAULT` davant d'`ALWAYS`

Són dues variants amb una diferència important:

	`GENERATED BY DEFAULT AS IDENTITY`	`GENERATED ALWAYS AS IDENTITY`
Ometre l'`id` en inserir	El genera la seqüència	El genera la seqüència
Indicar l'`id` explícitament	S'accepta el teu valor	Error, llevat d'`OVERRIDING SYSTEM VALUE`
Risc de desajust de la seqüència	Sí	No
Ús típic	Càrregues inicials, migracions, dades de prova	Producció estricta

Amb ALWAYS:

CREATE TABLE demo_identitat (
    id  INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom VARCHAR(40) NOT NULL
);

INSERT INTO demo_identitat (id, nom) VALUES (1, 'Forçat');

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

BotigaVerda fa servir BY DEFAULT justament perquè el seu script de càrrega insereix els id a mà (INSERT INTO categories (id, nom, ...) VALUES (1, ...)), i això permet que els exemples del curs parlin del "client 7" o de la "comanda 12" amb ids estables i reproduïbles. El preu d'aquesta comoditat és el bloc de setval del final de l'script, que veuràs explicat a 05-02.

Si l'id de la teva taula no s'ha de fixar mai a mà, GENERATED ALWAYS és més segur: elimina d'arrel la possibilitat de desajustar la seqüència.

L'antic `SERIAL`

Abans de PostgreSQL 10 no existia IDENTITY i es feia servir el pseudotipus SERIAL:

-- Estil antic, encara molt freqüent en codi existent
id SERIAL PRIMARY KEY

SERIAL no és un tipus real: és sucre sintàctic que PostgreSQL expandeix a tres coses:

CREATE SEQUENCE productes_id_seq;
id INTEGER NOT NULL DEFAULT nextval('productes_id_seq');
ALTER SEQUENCE productes_id_seq OWNED BY productes.id;

	`SERIAL`	`GENERATED ... AS IDENTITY`
Estàndard SQL	No, és de PostgreSQL	Sí (SQL:2003)
Variants	`SMALLSERIAL`, `SERIAL`, `BIGSERIAL`	`SMALLINT`, `INTEGER`, `BIGINT` + `IDENTITY`
Pot impedir valors manuals	No	Sí, amb `ALWAYS`
La seqüència s'esborra amb la taula	Sí (per `OWNED BY`)	Sí
Permisos	Cal concedir permís sobre la seqüència a part	Gestionat amb la taula
Recomanació actual	Codi heretat	Preferida en obra nova

Sabràs reconèixer SERIAL en qualsevol esquema antic; escriu IDENTITY al teu.

Autoincrement als altres motors

És una de les divergències més grans entre dialectes:

Motor	Sintaxi habitual	Notes
PostgreSQL 16	`INTEGER GENERATED BY DEFAULT AS IDENTITY`	També `SERIAL` (heretat). Estàndard
MySQL / MariaDB	`INT AUTO_INCREMENT PRIMARY KEY`	Només una per taula i ha d'estar indexada. MySQL 8 no admet `IDENTITY`
SQLite	`INTEGER PRIMARY KEY` (o `AUTOINCREMENT`)	`INTEGER PRIMARY KEY` ja és un àlies de `rowid` i autoincrementa; `AUTOINCREMENT` només afegeix la garantia de no reutilitzar ids esborrats
SQL Server	`INT IDENTITY(1,1) PRIMARY KEY`	Els paràmetres són llavor i increment. Des del 2012 també hi ha seqüències
Oracle	`NUMBER GENERATED BY DEFAULT AS IDENTITY` (12c+)	Abans: seqüència explícita + trigger `BEFORE INSERT`

Nota de dialecte: si escrius DDL que hagi de funcionar en diversos motors, la columna d'identitat serà gairebé sempre el primer que hauràs de bifurcar. És una de les raons per les quals existeixen les eines de migració de l'apartat final de 05-06.

Columnes generades

Una columna generada és una columna el valor de la qual es calcula a partir d'altres columnes de la mateixa fila. No s'insereix ni s'actualitza: el motor la manté.

-- Exemple puntual: variant de linies_comanda amb l'import calculat.
-- NO forma part de l'esquema de BotigaVerda.
CREATE TABLE linies_comanda_v2 (
    id            INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    comanda_id    INTEGER       NOT NULL REFERENCES comandes(id) ON DELETE CASCADE,
    producte_id   INTEGER       NOT NULL REFERENCES productes(id),
    quantitat     INTEGER       NOT NULL CHECK (quantitat > 0),
    preu_unitari  NUMERIC(10,2) NOT NULL CHECK (preu_unitari >= 0),
    descompte     NUMERIC(4,2)  NOT NULL DEFAULT 0
                  CHECK (descompte >= 0 AND descompte <= 1),
    import        NUMERIC(12,4)
                  GENERATED ALWAYS AS (quantitat * preu_unitari * (1 - descompte)) STORED
);

CREATE TABLE

Ara l'expressió que portes escrivint des de 02-02 —quantitat * preu_unitari * (1 - descompte)— viu a l'esquema, no a cada consulta:

INSERT INTO linies_comanda_v2 (comanda_id, producte_id, quantitat, preu_unitari, descompte)
VALUES (20, 5, 8, 1.95, 0.15);

INSERT 0 1

SELECT id, quantitat, preu_unitari, descompte, import FROM linies_comanda_v2;

id	quantitat	preu_unitari	descompte	import
1	8	1.95	0.15	13.2600

I si intentes escriure-hi:

-- ⚠️ INCORRECTA
UPDATE linies_comanda_v2 SET import = 99 WHERE id = 1;

ERROR:  column "import" can only be updated to DEFAULT
DETAIL:  Column "import" is a generated column.

Regles de les columnes generades a PostgreSQL 16:

Han de ser STORED (es desen en disc). Les VIRTUAL (calculades en llegir) encara no estan admeses.
L'expressió ha de ser immutable: només columnes de la mateixa fila i funcions deterministes. Res de CURRENT_DATE, ni subconsultes, ni altres taules.
No pot tenir DEFAULT ni ser columna d'identitat.

Convé posar-la a `linies_comanda`?

És una decisió de disseny amb arguments en tots dos sentits:

A favor	En contra
La fórmula s'escriu una vegada i no pot divergir entre consultes	Ocupa espai en disc a totes les files
Impossible oblidar-se del `(1 - descompte)`, l'error clàssic de 02-02	Canviar la fórmula obliga a un `ALTER TABLE` que reescriu la taula
Es pot indexar i agregar directament	És redundància: viola la 3FN (01-05) de manera controlada
Blinda el càlcul davant d'aplicacions diferents que ataquen la mateixa base	Un `SELECT` amb l'expressió costa pràcticament el mateix

BotigaVerda no la fa servir, per dues raons didàctiques i una de pràctica: escriure l'expressió a mà és exactament el que t'ha ensenyat a pensar en imports durant tres mòduls; el descompte com a fracció ja és una decisió de disseny explicada; i amb 47 files l'estalvi seria zero. En un sistema real amb milions de línies i una desena d'aplicacions consultant, la balança s'inclina clarament a favor.

Nota de dialecte: les columnes generades són força portables. MySQL 5.7+ les té amb GENERATED ALWAYS AS (...) STORED | VIRTUAL; SQLite 3.31+ igual; SQL Server les anomena computed columns (AS expressió [PERSISTED]); Oracle fa servir GENERATED ALWAYS AS (...) VIRTUAL. La sintaxi varia poc, però qui admet VIRTUAL sí que varia, i PostgreSQL és dels que no.

`IF NOT EXISTS`, `CREATE TABLE AS SELECT` i taules temporals

`CREATE TABLE IF NOT EXISTS`

Evita l'error si la taula ja existeix:

CREATE TABLE IF NOT EXISTS categories (
    id  INTEGER     GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom VARCHAR(60) NOT NULL UNIQUE
);

NOTICE:  relation "categories" already exists, skipping
CREATE TABLE

És un avís, no un error, i la sentència es dóna per bona. Sona còmode, però fes-lo servir amb compte: si la taula existeix amb una definició diferent, IF NOT EXISTS no la corregeix, la ignora en silenci. Per a scripts de creació des de zero, DROP TABLE IF EXISTS seguit de CREATE TABLE (el que fa botigaverda.sql) és més honest: garanteix que l'estructura resultant és exactament la que has escrit. Per a migracions de debò, cap de les dues: fitxer versionat i ALTER TABLE (lliçó 05-06).

`CREATE TABLE ... AS SELECT` (CTAS)

Crea una taula a partir del resultat d'una consulta, deduint-ne columnes i tipus:

CREATE TABLE productes_cars AS
SELECT id,
       nom,
       preu,
       stock
FROM productes
WHERE preu > 10;

SELECT 7

La sortida no és CREATE TABLE, és SELECT 7: t'està dient quantes files ha copiat.

SELECT id, nom, preu, stock FROM productes_cars ORDER BY id;

id	nom	preu	stock
1	Oli d'oliva verge extra 500 ml	12.50	120
6	Crema facial d'àloe vera 50 ml	18.90	60
8	Oli corporal d'ametlles 200 ml	14.25	45
10	Detergent ecològic concentrat 1 L	11.20	70
13	Espelmes de cera de soja (pack 2)	13.75	0
15	Te verd matcha cerimonial 30 g	22.00	40
20	Càpsules d'espirulina 120 u	16.40	55

El que CTAS copia i el que no, i això és la font de disgustos número u:

Es copia	No es copia
Els noms de columna	La clau primària
Els tipus de dades	Els `UNIQUE`, `CHECK` i `NOT NULL`
Les dades	Les claus foranes
	Els valors `DEFAULT` i les columnes d'identitat
	Els índexs

És a dir: productes_cars.id no és clau primària i admet duplicats i nuls. CTAS crea un contenidor de dades, no una taula ben definida. Els seus usos legítims són tres: còpies de seguretat ràpides abans d'un UPDATE perillós (veuràs justament això a 05-03), taules intermèdies d'anàlisi, i materialitzar el resultat d'una consulta costosa.

Si només vols l'estructura, sense dades:

CREATE TABLE productes_buida AS
SELECT * FROM productes WHERE FALSE;

SELECT 0

I si el que vols és una còpia amb les restriccions, la instrucció és una altra:

CREATE TABLE productes_copia (LIKE productes INCLUDING ALL);

CREATE TABLE

LIKE ... INCLUDING ALL sí que copia valors per omissió, restriccions, índexs i identitat —però no les dades ni les claus foranes—. És el més semblant a "clonar la taula" que ofereix PostgreSQL.

`CREATE TEMP TABLE`

Una taula temporal existeix només dins de la teva sessió i desapareix en desconnectar-te:

CREATE TEMP TABLE preus_revisats AS
SELECT id, nom, preu, ROUND(preu * 1.05, 2) AS preu_nou
FROM productes
WHERE categoria_id = 4;

SELECT 4

SELECT * FROM preus_revisats ORDER BY id;

id	nom	preu	preu_nou
14	Infusió de camamilla ecològica 20 u	3.25	3.41
15	Te verd matcha cerimonial 30 g	22.00	23.10
16	Kombutxa de gingebre 750 ml	4.95	5.20
17	Suc de taronja premsat en fred 1 L	5.40	5.67

Propietats que la fan útil:

Aïllada: una altra sessió no la veu, i pot tenir una taula temporal amb el mateix nom sense interferir.
Efímera: s'esborra en tancar la sessió, o en acabar la transacció si hi afegeixes ON COMMIT DROP.
Amaga la taula real: si crees una TEMP TABLE productes, les teves consultes passaran a llegir la temporal. Molt pràctic per a proves; molt perillós si se t'oblida.

Per a treballs d'un sol pas, les CTE del mòdul 10 solen ser millors. Les temporals brillen quan necessites rellegir el resultat intermedi diverses vegades.

`DROP TABLE` i l'ordre que imposa la integritat referencial

DROP TABLE [IF EXISTS] nom [, ...] [CASCADE | RESTRICT];

RESTRICT (per omissió): falla si algun altre objecte depèn de la taula.
CASCADE: esborra també els objectes dependents (restriccions d'altres taules, vistes...).
IF EXISTS: no protesta si la taula no existeix.

Prova d'esborrar categories amb BotigaVerda carregada:

DROP TABLE categories;

ERROR:  cannot drop table categories because other objects depend on it
DETAIL:  constraint productes_categoria_id_fkey on table productes depends on table categories
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

productes.categoria_id la referencia. Amb CASCADE:

DROP TABLE categories CASCADE;

NOTICE:  drop cascades to constraint productes_categoria_id_fkey on table productes
DROP TABLE

Llegeix bé aquell NOTICE: no ha esborrat la taula productes, ha esborrat la seva restricció de clau forana. DROP TABLE ... CASCADE elimina les dependències, no les taules filles. Tot i així, el resultat és que productes es queda sense la barrera que protegia el seu categoria_id, cosa que gairebé mai no és el que volies.

L'ordre de creació i d'esborrat

La integritat referencial imposa un ordre estricte, i és la raó de l'estructura de botigaverda.sql:

flowchart TD
    subgraph CREAR["Crear: de pares a fills"]
        C1["1 · categories<br/>proveidors"] --> C2["2 · productes<br/>clients · empleats"]
        C2 --> C3["3 · comandes"]
        C3 --> C4["4 · linies_comanda<br/>ressenyes · devolucions"]
    end
    subgraph ESBORRAR["Esborrar: de fills a pares"]
        B1["1 · devolucions · ressenyes<br/>linies_comanda"] --> B2["2 · comandes"]
        B2 --> B3["3 · empleats · clients<br/>productes"]
        B3 --> B4["4 · proveidors<br/>categories"]
    end

Per això l'script del curs comença així:

DROP TABLE IF EXISTS devolucions    CASCADE;
DROP TABLE IF EXISTS ressenyes      CASCADE;
DROP TABLE IF EXISTS linies_comanda CASCADE;
DROP TABLE IF EXISTS comandes       CASCADE;
DROP TABLE IF EXISTS empleats       CASCADE;
DROP TABLE IF EXISTS clients        CASCADE;
DROP TABLE IF EXISTS productes      CASCADE;
DROP TABLE IF EXISTS proveidors     CASCADE;
DROP TABLE IF EXISTS categories     CASCADE;

Nou DROP en ordre invers al de creació, amb IF EXISTS (perquè funcioni la primera vegada, quan no hi ha res) i amb CASCADE (per si l'ordre fallés). És el que fa l'script idempotent: el pots executar cent vegades i sempre deixarà la base en el mateix estat.

També ho podries escriure en una sola sentència, que resol l'ordre sola:

DROP TABLE IF EXISTS
    categories, proveidors, productes, clients, empleats,
    comandes, linies_comanda, ressenyes, devolucions CASCADE;

Avís. DROP TABLE és irreversible tan bon punt confirmes la transacció i no pregunta. A PostgreSQL et pots protegir amb BEGIN; ... ROLLBACK;, perquè el seu DDL és transaccional (lliçó 05-06); a MySQL, no. I hi ha un cas especial de les relacions reflexives: clients.referit_per_id i empleats.cap_id apunten a la seva pròpia taula, així que cap taula externa no les bloqueja, però un DROP de clients sense CASCADE continuarà fallant per comandes i ressenyes.

Recorregut comentat del DDL de BotigaVerda

Ara sí: l'script de 01-06, llegit amb tot l'après. Aquestes són les decisions i el seu perquè.

`categories` i `proveidors` — les taules sense dependències

CREATE TABLE categories (
    id          INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom         VARCHAR(60)  NOT NULL UNIQUE,
    descripcio  TEXT
);

CREATE TABLE proveidors (
    id     INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom    VARCHAR(120) NOT NULL,
    pais   VARCHAR(60)  NOT NULL,
    email  VARCHAR(120),
    actiu  BOOLEAN      NOT NULL DEFAULT TRUE
);

Decisió	Per què
`categories.nom` és `UNIQUE`, `proveidors.nom` no	El nom de categoria és la clau natural del negoci; dos proveïdors es podrien dir igual en teoria i el negoci no ho prohibeix
`proveidors.email` admet nuls	No tots els proveïdors donen contacte comercial
`actiu BOOLEAN NOT NULL DEFAULT TRUE`	És l'esborrat lògic (05-04): un proveïdor nou neix actiu, i mai no s'esborra físicament. El proveïdor 5 està inactiu i conserva els seus quatre productes
`descripcio TEXT` sense longitud	`TEXT` i `VARCHAR` sense límit són idèntics a PostgreSQL en rendiment; `VARCHAR(n)` només afegeix una comprovació de longitud

`productes` — el catàleg

CREATE TABLE productes (
    id           INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom          VARCHAR(150)  NOT NULL,
    categoria_id INTEGER       REFERENCES categories(id) ON DELETE RESTRICT,
    proveidor_id INTEGER       REFERENCES proveidors(id) ON DELETE RESTRICT,
    preu         NUMERIC(10,2) NOT NULL CHECK (preu >= 0),
    cost         NUMERIC(10,2) CHECK (cost >= 0),
    stock        INTEGER       NOT NULL DEFAULT 0 CHECK (stock >= 0),
    actiu        BOOLEAN       NOT NULL DEFAULT TRUE,
    data_alta    DATE          NOT NULL DEFAULT CURRENT_DATE
);

Decisió	Per què
`categoria_id` i `proveidor_id` admeten nuls	Es pot donar d'alta un producte abans de classificar-lo o d'assignar-li proveïdor. La FK continua exigint que, si hi ha valor, existeixi
Les dues FK són `RESTRICT`	No s'esborra una categoria amb productes ni un proveïdor amb catàleg. Es desactiven
`preu NOT NULL`, `cost` nul·lable	Sense preu no hi ha venda; el cost es pot desconèixer. D'aquí que l'`AVG(cost)` de 04-04 ignorés nuls
`NUMERIC(10,2)` i no `FLOAT`	Diners exactes (01-04). Deu dígits, dos decimals: fins a 99 999 999,99 €
`stock CHECK (stock >= 0)`	Impedeix estoc negatiu. Compte: no impedeix vendre sense estoc, perquè això és lògica de negoci en una altra taula; la base no la coneix
`data_alta DEFAULT CURRENT_DATE`	Una alta sense data es data avui sola

`clients` i `empleats` — les reflexives

CREATE TABLE clients (
    id             INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom            VARCHAR(60)  NOT NULL,
    cognoms        VARCHAR(90)  NOT NULL,
    email          VARCHAR(120) NOT NULL UNIQUE,
    ciutat         VARCHAR(80),
    pais           VARCHAR(60)  NOT NULL,
    data_registre  DATE         NOT NULL DEFAULT CURRENT_DATE,
    referit_per_id INTEGER      REFERENCES clients(id) ON DELETE SET NULL
);

Decisió	Per què
`email NOT NULL UNIQUE`	És la clau candidata de 01-05. L'`id` és la PK, però el `UNIQUE` impedeix registrar dues vegades el mateix client
`referit_per_id REFERENCES clients(id)`	Una taula es pot referenciar a si mateixa dins de la seva pròpia definició. És la relació reflexiva dels `SELF JOIN` de 03-06
`ON DELETE SET NULL` a la reflexiva	Si s'esborra qui va referir, el referit continua sent client. Exigeix que la columna sigui nul·lable, i ho és
`ciutat` nul·lable, `pais` no	El país sempre es coneix (determina ports i impostos); la ciutat pot faltar

empleats segueix el mateix patró amb cap_id, i amb un detall extra: salari NUMERIC(10,2) CHECK (salari >= 0) és nul·lable, perquè a la pràctica no tothom té accés a aquella dada.

`linies_comanda` — la taula pont

Ja l'has vista sencera a l'apartat 3.6. Només un recordatori de per què preu_unitari és allà duplicant productes.preu: no és redundància, són dades diferents. L'un és el preu actual del catàleg, l'altre el preu facturat. Les línies 1 i 4 del conjunt de dades ho demostren: 11,95 € i 17,50 € davant dels 12,50 € i 18,90 € d'avui. És la desnormalització deliberada de 01-05, secció 8.

`ressenyes` i `devolucions` — les satèl·lit

CREATE TABLE ressenyes (
    id          INTEGER  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    producte_id INTEGER  NOT NULL REFERENCES productes(id) ON DELETE CASCADE,
    client_id   INTEGER  NOT NULL REFERENCES clients(id)   ON DELETE CASCADE,
    puntuacio   SMALLINT NOT NULL CHECK (puntuacio BETWEEN 1 AND 5),
    comentari   TEXT,
    data        DATE     NOT NULL
);

Aquí hi ha una decisió que convé mirar de cara: ressenyes no té cap restricció que garanteixi que el client hagi comprat aquell producte, ni que impedeixi que ressenyi dues vegades el mateix. Ho vas comprovar a l'exercici 3 de 01-06: inserir una ressenya del client 14 sobre un producte que mai no va comprar funciona.

És intencionat, i tanca una lliçó important: una restricció declarativa només pot mirar la fila que s'està inserint i, com a molt, l'existència de la clau en una altra taula. "Aquest client va comprar aquest producte" exigeix recórrer comandes i linies_comanda, i això no cap en un CHECK ni en una FOREIGN KEY. Necessitaries un trigger (mòdul 10) o lògica d'aplicació.

La regla: la base de dades protegeix les invariants estructurals; l'aplicació protegeix les regles de procés. Confondre les dues coses porta o bé a esquemes ingenus, o bé a esquemes impossibles de mantenir.

El resum de l'esquema en una taula

Taula	PK	`UNIQUE`	`CHECK`	FK sortints	Columnes nul·lables
`categories`	`id`	`nom`	—	0	`descripcio`
`proveidors`	`id`	—	—	0	`email`
`productes`	`id`	—	3	2	`categoria_id`, `proveidor_id`, `cost`
`clients`	`id`	`email`	—	1 (reflexiva)	`ciutat`, `referit_per_id`
`empleats`	`id`	—	1	1 (reflexiva)	`cap_id`, `salari`, `ciutat`
`comandes`	`id`	—	3	2	`empleat_id`
`linies_comanda`	`id`	—	3	2	—
`ressenyes`	`id`	—	1	2	`comentari`
`devolucions`	`id`	—	1	1	—

Errors habituals i consells

Oblidar el NOT NULL. Una columna sense ell admet nuls, i descobrir-ho en producció amb un AVG que retorna un número estrany és car. Declara NOT NULL per defecte i justifica cada excepció.
Fer servir FLOAT o REAL per als diners. 0.1 + 0.2 no dóna 0.3 en coma flotant. Sempre NUMERIC(10,2) (01-04).
Creure que un CHECK impedeix els nuls. Un CHECK que dóna UNKNOWN s'accepta. Si necessites valor, és NOT NULL; si necessites cobrir el nul dins del CHECK, escriu col IS NULL OR ....
Posar ON DELETE SET NULL en una columna NOT NULL. La taula es crea, però el primer esborrat del pare falla amb null value ... violates not-null constraint. Les dues declaracions són incompatibles en el moment de la veritat.
Fer servir CASCADE per comoditat. Un DELETE es pot propagar per mitja base de dades en silenci. Reserva'l per a composició real (05-04).
No anomenar les restriccions. Funciona fins a la primera migració; després, cada DROP CONSTRAINT comença amb una recerca a information_schema.
Crear taules en ordre alfabètic. L'ordre el mana la integritat referencial: pares abans que fills, i a l'inrevés per esborrar.
Confiar en CREATE TABLE AS SELECT com a còpia fidel. No copia PK, ni UNIQUE, ni CHECK, ni FK, ni identitat, ni índexs. Per a això hi ha LIKE ... INCLUDING ALL.
Confondre CREATE TABLE IF NOT EXISTS amb una migració. Si la taula existeix amb una altra estructura, la ignora en silenci i et deixa dos entorns diferents.
Consell: escriu el DDL en un fitxer versionat, mai a mà a la consola. És el germen de les migracions de 05-06 i l'única manera que dos entorns siguin iguals.
Consell: \d taula abans d'escriure qualsevol INSERT. T'estalvia la meitat dels errors del mòdul.
Consell: prova les teves restriccions. Escriu un INSERT que hagi de fallar i comprova que falla. Una restricció que mai no has vist saltar pot no estar fent el que et penses.
Consell: indexa les claus foranes. PostgreSQL indexa la PK i els UNIQUE, però no les FK. Sense aquell índex els JOIN i els esborrats en cascada s'arrosseguen (mòdul 8).

Exercicis

Exercici 1

BotigaVerda vol llançar un programa de cupons de descompte. Escriu el CREATE TABLE de la taula cupons amb aquests requisits, fent servir restriccions amb nom explícit:

id enter, clau primària generada pel motor, sense poder forçar-se a mà.
codi text de fins a 20 caràcters, obligatori i únic.
descripcio text lliure opcional.
descompte fracció entre 0.01 i 0.50, obligatòria (mateix criteri que linies_comanda.descompte).
data_inici i data_fi, totes dues obligatòries, amb data_fi posterior o igual a data_inici.
usos_maxims enter opcional; si té valor, ha de ser més gran que 0.
usos_actuals enter obligatori, amb valor per omissió 0 i mai negatiu.
client_id opcional: si el cupó és nominal, apunta a un client; si aquell client s'esborra, el cupó ha de quedar sense titular en lloc de desaparèixer.
actiu booleà obligatori, per omissió cert.

Exercici 2

Per a cadascuna d'aquestes sis afirmacions, digues si és certa o falsa i justifica-ho en una frase. Si pots, comprova-ho executant-ho.

Una columna UNIQUE no pot contenir dues files amb NULL a PostgreSQL.
PRIMARY KEY (comanda_id, producte_id) permet que un producte aparegui dues vegades a la mateixa comanda.
CHECK (cost <= preu) rebutja una fila amb cost a NULL.
DEFAULT CURRENT_DATE desa la data en què es va crear la taula.
CREATE TABLE copia AS SELECT * FROM productes produeix una taula amb la mateixa clau primària.
Amb GENERATED ALWAYS AS IDENTITY, l'script de càrrega de BotigaVerda funcionaria igual.

Exercici 3

Aquest DDL té cinc problemes. Troba'ls, explica quina conseqüència té cadascun i reescriu la taula corregida.

-- ⚠️ INCORRECTA
CREATE TABLE incidencies (
    id             SERIAL,
    comanda_id     INTEGER REFERENCES comandes(id) ON DELETE SET NULL,
    tipus          VARCHAR(20),
    import         FLOAT,
    prioritat      INTEGER CHECK (prioritat BETWEEN 1 AND 5),
    data_obertura  DATE DEFAULT CURRENT_DATE,
    data_tancament DATE,
    CHECK (data_tancament > data_obertura)
);

Solucions

Solució 1

CREATE TABLE cupons (
    id           INTEGER      GENERATED ALWAYS AS IDENTITY,
    codi         VARCHAR(20)  NOT NULL,
    descripcio   TEXT,
    descompte    NUMERIC(4,2) NOT NULL,
    data_inici   DATE         NOT NULL,
    data_fi      DATE         NOT NULL,
    usos_maxims  INTEGER,
    usos_actuals INTEGER      NOT NULL DEFAULT 0,
    client_id    INTEGER,
    actiu        BOOLEAN      NOT NULL DEFAULT TRUE,

    CONSTRAINT pk_cupons             PRIMARY KEY (id),
    CONSTRAINT uq_cupons_codi        UNIQUE (codi),
    CONSTRAINT fk_cupons_client      FOREIGN KEY (client_id)
               REFERENCES clients(id) ON DELETE SET NULL,
    CONSTRAINT chk_cupons_descompte  CHECK (descompte >= 0.01 AND descompte <= 0.50),
    CONSTRAINT chk_cupons_dates      CHECK (data_fi >= data_inici),
    CONSTRAINT chk_cupons_usos_max   CHECK (usos_maxims IS NULL OR usos_maxims > 0),
    CONSTRAINT chk_cupons_usos_act   CHECK (usos_actuals >= 0)
);

CREATE TABLE

Els quatre punts que calia encertar:

Requisit	Com es resol
"sense poder forçar-se a mà"	`GENERATED ALWAYS`, no `BY DEFAULT`
"`data_fi` posterior o igual"	`CHECK` a nivell de taula: implica dues columnes
"si té valor, més gran que 0"	`usos_maxims IS NULL OR usos_maxims > 0`. Sense l'`IS NULL OR` funcionaria igual (un `CHECK` amb `UNKNOWN` s'accepta), però escriure-ho fa explícita la intenció
"quedar sense titular en lloc de desaparèixer"	`ON DELETE SET NULL`, i `client_id` ha de ser nul·lable

Solució 2

#	Afirmació	Veredicte	Justificació
1	`UNIQUE` no admet dos `NULL`	Falsa	`NULL` no és igual a `NULL` (04-03): n'hi pot haver tants com vulguis. Llevat de `UNIQUE NULLS NOT DISTINCT` (PG 15+) o SQL Server, que només n'admet un
2	La PK composta permet repetir producte	Falsa	És just el que impedeix, i per això `linies_comanda` no la fa servir
3	`CHECK (cost <= preu)` rebutja `cost` nul	Falsa	`NULL <= preu` dóna `UNKNOWN`, i un `CHECK` amb `UNKNOWN` s'accepta
4	`DEFAULT CURRENT_DATE` congela la data de creació	Falsa	S'avalua a cada inserció; cada fila porta la data de la seva alta
5	CTAS copia la clau primària	Falsa	No copia PK, `UNIQUE`, `CHECK`, FK, identitat ni índexs
6	Amb `ALWAYS`, l'script de càrrega funcionaria igual	Falsa	L'script insereix els `id` explícitament. Amb `ALWAYS` donaria `cannot insert a non-DEFAULT value into column "id"` llevat que afegissis `OVERRIDING SYSTEM VALUE` a cada `INSERT`

Solució 3

Els cinc problemes:

#	Problema	Conseqüència
1	No hi ha `PRIMARY KEY`	`SERIAL` genera valors creixents però no garanteix unicitat: res no impedeix inserir dues files amb el mateix `id` a mà. La taula no té identificador fiable
2	`import FLOAT`	Coma flotant per als diners (01-04): errors d'arrodoniment acumulatius. Ha de ser `NUMERIC(10,2)`
3	`tipus VARCHAR(20)` sense `NOT NULL` ni `CHECK`	Domini obert: hi caben `'devolucio'`, `'Devolució'`, `'DEV'`, `''` i `NULL`. Els informes per tipus seran inútils
4	`comanda_id` amb `ON DELETE SET NULL`	Una incidència sense comanda no significa res. Hauria de ser `NOT NULL` + `ON DELETE CASCADE` (la incidència mor amb la comanda) o `RESTRICT` (no es pot esborrar una comanda amb incidències obertes)
5	`CHECK (data_tancament > data_obertura)` amb `>` estricte	Una incidència oberta i tancada el mateix dia es rebutja. Ha de ser `>=`. Amb `data_tancament` a `NULL` (incidència oberta) sí que funciona, perquè el `CHECK` dóna `UNKNOWN` i s'accepta

I de propina, dues millores que no són errors però sí mals hàbits: SERIAL en obra nova i cap restricció amb nom.

-- ✅ CORRECTA
CREATE TABLE incidencies (
    id             INTEGER       GENERATED ALWAYS AS IDENTITY,
    comanda_id     INTEGER       NOT NULL,
    tipus          VARCHAR(20)   NOT NULL,
    import         NUMERIC(10,2),
    prioritat      SMALLINT      NOT NULL DEFAULT 3,
    data_obertura  DATE          NOT NULL DEFAULT CURRENT_DATE,
    data_tancament DATE,

    CONSTRAINT pk_incidencies         PRIMARY KEY (id),
    CONSTRAINT fk_incidencies_comanda FOREIGN KEY (comanda_id)
               REFERENCES comandes(id) ON DELETE CASCADE,
    CONSTRAINT chk_incidencies_tipus
               CHECK (tipus IN ('devolucio','retard','producte_malmes','error_comanda','altre')),
    CONSTRAINT chk_incidencies_import    CHECK (import IS NULL OR import >= 0),
    CONSTRAINT chk_incidencies_prioritat CHECK (prioritat BETWEEN 1 AND 5),
    CONSTRAINT chk_incidencies_dates     CHECK (data_tancament IS NULL OR data_tancament >= data_obertura)
);

Conclusió

Ja saps escriure l'esquema que portaves cinc mòduls llegint:

CREATE TABLE declara columnes amb el seu tipus i les seves restriccions, a nivell de columna (una sola columna) o a nivell de taula (diverses, obligatòriament).
Les sis restriccions: NOT NULL (obligatorietat), DEFAULT (farciment, no restricció, avaluat a cada inserció), CHECK (domini d'una fila, i accepta els nuls perquè UNKNOWN es dóna per bo), UNIQUE (que sí que admet diversos NULL a PostgreSQL, reprenent 04-03), PRIMARY KEY (simple o composta) i FOREIGN KEY amb els seus ON DELETE / ON UPDATE.
Anomenar les restriccions (CONSTRAINT chk_comandes_estat ...) converteix comandes_estat_check1 en un missatge d'error llegible i fa possibles les migracions de 05-06.
Les columnes d'identitat: GENERATED BY DEFAULT (permet forçar l'id, i per això la fa servir BotigaVerda) davant de GENERATED ALWAYS (més segur), davant de l'antic SERIAL; i les quatre sintaxis incompatibles de MySQL, SQLite, SQL Server i Oracle.
Les columnes generades (GENERATED ALWAYS AS (...) STORED) poden ficar a l'esquema la fórmula de l'import de línia, amb els seus avantatges i el seu cost.
IF NOT EXISTS (que ignora en silenci una definició diferent), CTAS (que copia dades però cap restricció) i CREATE TEMP TABLE (aïllada i efímera).
DROP TABLE amb RESTRICT/CASCADE, i l'ordre que imposa la integritat referencial: crear de pares a fills, esborrar de fills a pares. És l'estructura exacta de botigaverda.sql i la raó que sigui idempotent.
I el recorregut comentat de l'esquema real, amb la frontera ben marcada: la base de dades protegeix les invariants estructurals; les regles de procés —"només pot ressenyar qui va comprar"— són de l'aplicació o d'un trigger del mòdul 10.

Ja tens on posar les dades. A la lliçó següent, Instrucció INSERT, començaràs a posar-les: per què cal llistar sempre les columnes, com inserir quaranta files en una sola sentència, què fa realment el bloc de setval del final de l'script del curs, com recuperar amb RETURNING l'id que acaba de generar el motor —la peça que et faltava per registrar una comanda i les seves línies—, com inserir el resultat d'una consulta amb INSERT ... SELECT, i què significa exactament cadascun dels quatre errors que un INSERT et pot donar.

Crear taules i restriccions amb CREATE TABLE

Contingut

Del catàleg mort a la botiga viva

Anatomia de CREATE TABLE

Comprovar el que has creat

Les restriccions, una a una

3.1. NOT NULL

3.2. DEFAULT

3.3. PRIMARY KEY

3.4. UNIQUE

UNIQUE i els NULL: reprenent 04-03

3.5. CHECK

3.6. FOREIGN KEY, ON DELETE i ON UPDATE

Totes les FK de BotigaVerda, amb la seva sintaxi

A nivell de columna o a nivell de taula

Anomenar les restriccions i per què importa

Columnes d'identitat: IDENTITY, SERIAL i la resta de motors

BY DEFAULT davant d'ALWAYS

L'antic SERIAL

Autoincrement als altres motors

Columnes generades

Convé posar-la a linies_comanda?

IF NOT EXISTS, CREATE TABLE AS SELECT i taules temporals

CREATE TABLE IF NOT EXISTS

CREATE TABLE ... AS SELECT (CTAS)

CREATE TEMP TABLE

DROP TABLE i l'ordre que imposa la integritat referencial

L'ordre de creació i d'esborrat

Recorregut comentat del DDL de BotigaVerda

categories i proveidors — les taules sense dependències

productes — el catàleg

clients i empleats — les reflexives

linies_comanda — la taula pont

ressenyes i devolucions — les satèl·lit

El resum de l'esquema en una taula

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final