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

HOME

Tot el que hem construït en aquest mòdul —l'esquema, les dades, els JOIN, els informes— es recolza en una suposició que fins ara hem donat per bona: que quan prestecs diu soci_id = 14, existeix un soci 14. Si aquesta suposició falla, els JOIN perden files en silenci, els recomptes menteixen i els informes de la lliçó anterior deixen de ser fiables sense que ningú se n'adoni.

La integritat referencial és la garantia que això no passi. És la tercera regla del model relacional, la vam enunciar a la lliçó 02-01 i l'hem estat fent servir de manera implícita des que vam escriure REFERENCES al CREATE TABLE. Ara toca dominar-la: com es declara, què comprova exactament el gestor, què ha de passar quan s'esborra la fila de la qual d'altres depenen, com detectar el dany que ja existeix i —un advertiment que et pot estalviar mesos de desconcert— per què SQLite no protegeix absolutament res si no li ho demanes.

Amb aquesta lliçó tanquem el mòdul 2. En acabar-la, BiblioRed no només tindrà dades correctes: tindrà un esquema que impedeix activament que deixin de ser-ho.

Contingut

Files òrfenes: d'on vénen i què trenquen
Declarar una clau forana
Què comprova el gestor i quan
Les accions referencials ON DELETE i ON UPDATE
Les cinc opcions, comparades
Triar l'acció correcta a BiblioRed
L'esquema final de BiblioRed amb les seves accions referencials
Claus foranes compostes
Restriccions diferibles
SQLite: PRAGMA foreign_keys = ON
Detectar i netejar files òrfenes
Validar a l'aplicació o a la base de dades?
Errors habituals i consells
Exercicis
Conclusió

Files òrfenes: d'on vénen i què trenquen

Una fila òrfena és una fila la clau forana de la qual apunta a alguna cosa que no existeix. Al full de càlcul de BiblioRed que vam diagnosticar a la lliçó 01-01 hi havia tres maneres de crear-les, i totes tres passaven cada dia:

Teclejar un número de soci inventat. Ningú no comprovava res: si el bibliotecari escrivia 77 en lloc de 17, la fila es guardava tan contenta.
Esborrar una fila de la qual en depenien d'altres. Quan un soci es donava de baixa, algú n'eliminava la línia de la pestanya "Socis"; els seus quinze préstecs històrics continuaven a la pestanya "Préstecs", apuntant al buit.
Renumerar. En reordenar el full de socis, els números canviaven i totes les referències anteriors passaven a assenyalar una altra persona. Aquest és el pitjor dels tres, perquè no deixa rastre: la fila no queda òrfena, queda mal adoptada.

Què trenca exactament una fila òrfena:

Els INNER JOIN l'eliminen sense avisar. L'informe de préstecs per sucursal de la lliçó 02-05 simplement retornaria menys del que hi ha. I com que no hi ha error, ningú no ho investiga.
Els LEFT JOIN la conserven amb NULL, cosa que produeix informes amb forats que algú haurà d'explicar.
Els totals no quadren entre si. COUNT(*) FROM prestecs dóna 4.312 i la suma dels préstecs per soci dóna 4.298. I a partir d'aquí, la confiança en la base de dades s'evapora.

La integritat referencial converteix aquestes tres maneres de crear orfes en errors immediats, en el moment exacte en què s'intenten. Aquest és el seu valor: el problema apareix quan es pot arreglar, no sis mesos després.

Declarar una clau forana

Ja ho vam fer a la lliçó 02-02; ara amb detall.

Forma de columna

CREATE TABLE socis (
    soci_id     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom         VARCHAR(60) NOT NULL,
    sucursal_id INTEGER NOT NULL REFERENCES sucursals (sucursal_id)
);

Compacta i suficient per a casos simples. El gestor genera un nom automàtic per a la restricció, de l'estil socis_sucursal_id_fkey.

Forma de taula, amb nom propi

CREATE TABLE socis (
    soci_id     INTEGER GENERATED BY DEFAULT AS IDENTITY,
    nom         VARCHAR(60) NOT NULL,
    sucursal_id INTEGER NOT NULL,
    CONSTRAINT pk_socis PRIMARY KEY (soci_id),
    CONSTRAINT fk_socis_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
);

És la que fem servir a BiblioRed, per tres raons:

És obligatòria per a claus foranes compostes (apartat 8).
Permet anomenar-la. Quan salti l'error, el missatge dirà fk_socis_sucursal, no socis_sucursal_id_fkey.
Permet eliminar-la i recrear-la amb ALTER TABLE ... DROP CONSTRAINT fk_socis_sucursal, que és just el que farem a l'apartat 7.

Afegir-la després

ALTER TABLE socis
    ADD CONSTRAINT fk_socis_sucursal
    FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id);

En executar-ho, PostgreSQL valida totes les files existents. Si alguna és òrfena, l'operació falla sencera:

ERROR:  insert or update on table "socis" violates foreign key constraint "fk_socis_sucursal"
DETAIL:  Key (sucursal_id)=(99) is not present in table "sucursals".

És exactament el que vols: la restricció no s'activa fins que les dades estiguin netes. L'apartat 11 ensenya a netejar-les.

Requisits de la columna referenciada

La columna a la qual apunta una clau forana ha de tenir clau primària o una restricció UNIQUE. No es pot referenciar una columna qualsevol:

ALTER TABLE prestecs
    ADD CONSTRAINT fk_dolent FOREIGN KEY (soci_id) REFERENCES socis (cognoms);

ERROR:  there is no unique constraint matching given keys
        for referenced table "socis"

I té sentit: si la columna referenciada es pogués repetir, "apuntar a la fila amb cognom Alsina" seria ambigu. Per això exemplars.llibre_id pot apuntar tant a llibres.llibre_id (clau primària) com a llibres.isbn (clau alternativa UNIQUE), encara que el primer és el sensat.

Què comprova el gestor i quan

Una clau forana imposa comprovacions a les dues taules, no només a la filla. Aquest és el quadre complet:

Operació	Taula	Què comprova el gestor
`INSERT` a la filla	`prestecs`	Que el valor de `soci_id` existeixi a `socis` (o sigui `NULL`)
`UPDATE` de la clau forana a la filla	`prestecs`	El mateix: el valor nou ha d'existir
`DELETE` a la pare	`socis`	Que no quedin files filles apuntant a la fila esborrada
`UPDATE` de la clau primària a la pare	`socis`	El mateix: que no quedin filles apuntant al valor antic
`INSERT` a la pare	`socis`	Res: afegir un soci no trenca mai res

Comprovem-ho sobre biblioredb. Primer, des del costat fill:

INSERT INTO prestecs (soci_id, exemplar_id, data_prestec, data_devolucio_prevista)
VALUES (77, 1, '2026-08-02', '2026-08-23');

ERROR:  insert or update on table "prestecs" violates foreign key constraint
        "fk_prestecs_soci"
DETAIL:  Key (soci_id)=(77) is not present in table "socis".

Ara des del costat pare:

DELETE FROM socis WHERE soci_id = 14;

ERROR:  update or delete on table "socis" violates foreign key constraint
        "fk_prestecs_soci" on table "prestecs"
DETAIL:  Key (soci_id)=(14) is still referenced from table "prestecs".

I el cas del NULL, que sí que es permet:

-- llibres.autor_id NO és NOT NULL, així que un llibre sense autor és legal
INSERT INTO llibres (llibre_id, isbn, titol, autor_id, editorial, any_publicacio, idioma)
VALUES (340, NULL, 'Bans municipals de 1912', NULL, 'Ajuntament de Vallmar', 1912, 'es');

INSERT 0 1

Una clau forana nul·la no apunta a res, i això és vàlid. La regla d'integritat referencial parla de valors no nuls. Si vols que la referència sigui obligatòria, cal afegir-hi NOT NULL: és una decisió de disseny independent. A BiblioRed, prestecs.soci_id és NOT NULL (no existeix un préstec sense soci) mentre que llibres.autor_id no ho és (sí que existeixen obres sense autor catalogat).

Desfem la inserció de prova:

DELETE FROM llibres WHERE llibre_id = 340;

Les accions referencials `ON DELETE` i `ON UPDATE`

Fins aquí, el gestor s'ha limitat a prohibir. Però prohibir no sempre és el que vols. Si BiblioRed retira un títol sencer del catàleg, haver d'esborrar-ne abans els exemplars d'un en un és absurd: el natural és que se'n vagin amb ell.

Les accions referencials diuen què ha de fer el gestor amb les files filles quan la fila pare s'esborra o canvia de clau:

CONSTRAINT fk_exemplars_llibre
    FOREIGN KEY (llibre_id) REFERENCES llibres (llibre_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

Són dues clàusules independents:

ON DELETE: què fer quan s'esborra la fila pare.
ON UPDATE: què fer quan canvia el valor de la clau primària de la fila pare.

Si no n'escrius cap, s'aplica NO ACTION, el comportament per defecte de l'estàndard (el que hem vist a l'apartat anterior).

Un laboratori per provar-les

Per experimentar sense tocar BiblioRed, creem dues taules d'usar i llençar:

CREATE TABLE demo_categories (
    categoria_id INTEGER PRIMARY KEY,
    nom          VARCHAR(40) NOT NULL
);

CREATE TABLE demo_items (
    item_id      INTEGER PRIMARY KEY,
    nom          VARCHAR(40) NOT NULL,
    categoria_id INTEGER,
    CONSTRAINT fk_demo FOREIGN KEY (categoria_id)
        REFERENCES demo_categories (categoria_id) ON DELETE CASCADE
);

INSERT INTO demo_categories VALUES (1, 'Narrativa'), (2, 'Tècnica');
INSERT INTO demo_items VALUES (10, 'Item A', 1), (11, 'Item B', 1), (12, 'Item C', 2);

Estat inicial: tres ítems, dues categories.

DELETE FROM demo_categories WHERE categoria_id = 1;

DELETE 1

SELECT * FROM demo_items;

item_id	nom	categoria_id
12	Item C	2

Els ítems A i B han desaparegut. El DELETE sobre una fila n'ha esborrat tres en total, i només la primera apareix al missatge. Aquesta és la naturalesa del CASCADE: és potent i és silenciós.

Ara provem SET NULL:

ALTER TABLE demo_items DROP CONSTRAINT fk_demo;
ALTER TABLE demo_items ADD CONSTRAINT fk_demo
    FOREIGN KEY (categoria_id) REFERENCES demo_categories (categoria_id) ON DELETE SET NULL;

DELETE FROM demo_categories WHERE categoria_id = 2;
SELECT * FROM demo_items;

item_id	nom	categoria_id
12	Item C	(NULL)

L'ítem sobreviu; només perd la seva referència. Netegem el laboratori:

DROP TABLE demo_items;
DROP TABLE demo_categories;

Les cinc opcions, comparades

Acció	Què fa en esborrar/actualitzar la fila pare	Requisit	Risc
`NO ACTION`	Rebutja l'operació si queden filles. És el valor per defecte. La comprovació es fa al final de la instrucció, cosa que permet que un activador intermedi arregli la situació	Cap	Cap
`RESTRICT`	Rebutja l'operació si queden filles. La comprovació és immediata i no es pot diferir	Cap	Cap
`CASCADE`	Propaga: esborra les files filles (`ON DELETE`) o actualitza la seva clau forana (`ON UPDATE`)	Cap	Alt: un `DELETE` pot endur-se milers de files encadenades
`SET NULL`	Posa la clau forana de les filles a `NULL`	La columna no pot ser `NOT NULL`	Mitjà: queden files sense referència
`SET DEFAULT`	Posa la clau forana al valor `DEFAULT` de la columna	La columna ha de tenir `DEFAULT`, i aquest valor ha d'existir a la taula pare	Mitjà: si el valor per defecte no existeix, l'operació falla

`NO ACTION` i `RESTRICT`: la diferència real

En el 99 % dels casos es comporten igual: totes dues impedeixen l'operació. La diferència és quan es comprova:

RESTRICT comprova immediatament, tan bon punt s'executa la fila del DELETE.
NO ACTION comprova al final de la instrucció, i a més es pot diferir fins al COMMIT si la restricció es va declarar DEFERRABLE (apartat 9).

Conseqüència pràctica: RESTRICT no es pot diferir mai. Si preveus que necessitaràs restriccions diferibles, fes servir NO ACTION. A BiblioRed farem servir RESTRICT on volem una prohibició explícita i visible al CREATE TABLE, perquè documenta la intenció millor que deixar el buit sense omplir.

`SET DEFAULT`: la que gairebé no es fa servir mai

-- Requereix que la columna tingui DEFAULT i que aquest valor existeixi a la taula pare
sucursal_id INTEGER DEFAULT 1 REFERENCES sucursals (sucursal_id) ON DELETE SET DEFAULT

La idea és "si desapareix la sucursal d'aquest exemplar, assigna'l a la sucursal 1". El problema és evident: si algun dia algú esborra la sucursal 1, l'acció falla i el DELETE es bloqueja de manera difícil de diagnosticar. Existeix, cal conèixer-la, i a la pràctica es fa servir molt poc. Nota: les clàusules DEFAULT s'estudien a fons a la lliçó 04-04.

Triar l'acció correcta a BiblioRed

La pregunta que cal fer-se per a cada clau forana és sempre la mateixa:

Si desapareix la fila pare, la fila filla continua tenint sentit per si sola?

Si no té sentit i no val res → CASCADE.
Si no té sentit però és valuosa (històric, comptabilitat, auditoria) → RESTRICT.
Si sí que té sentit sense la referència → SET NULL.

Apliquem-ho a les vuit claus foranes de BiblioRed.

`prestecs.soci_id` → `RESTRICT`

Per què esborrar un soci no ha d'arrossegar el seu històric de préstecs. Un préstec és un fet ocorregut: el 5 de març de 2026 va sortir un exemplar per la porta i va tornar el 2 d'abril amb 1,40 € de recàrrec. Aquest fet no deixa d'haver passat perquè la persona es doni de baixa.

Si hi poséssim CASCADE, donar de baixa un soci destruiria estadístiques històriques —préstecs per any, títols més prestats, recaptació— que la direcció fa servir per decidir compres. És destrucció d'informació comptable disfressada de neteja.

Amb RESTRICT, l'intent d'esborrament falla, i això obliga a resoldre la pregunta de negoci de veritat: els socis no s'esborren, es marquen com a inactius (actiu = FALSE, com Ramón Etxebarri). És el que s'anomena esborrament lògic, i és la pràctica correcta per a qualsevol entitat amb historial.

`exemplars.llibre_id` → `CASCADE`

Per què esborrar un llibre sí que ha d'arrossegar els seus exemplars. Aquí la relació és de composició: un exemplar és una còpia física *d'*un llibre. EJ-3081 no és "un objecte que casualment està associat a El mapa del temps"; és un exemplar d'El mapa del temps. Sense el llibre, la fila no significa res: seria un objecte sense títol, sense autor i sense ISBN.

Si el títol desapareix del catàleg, mantenir-ne els quinze exemplars seria conservar brossa referencial. CASCADE és l'acció correcta.

I aquí passa una cosa interessant. Provem d'esborrar un llibre que sí que té préstecs:

DELETE FROM llibres WHERE llibre_id = 331;   -- El mapa del temps, 3 exemplars, 4 préstecs

El CASCADE d'exemplars intenta esborrar els exemplars 1, 2 i 3… però el RESTRICT de prestecs.exemplar_id ho impedeix:

ERROR:  update or delete on table "exemplars" violates foreign key constraint
        "fk_prestecs_exemplar" on table "prestecs"

La cascada s'atura en xocar amb una restricció. És exactament el comportament desitjat: es pot retirar del catàleg un títol que no s'ha prestat mai, però no un amb historial. L'esquema fa complir una regla de negoci real sense que ningú l'hagi programada en cap aplicació.

El quadre complet

Clau forana	`ON DELETE`	`ON UPDATE`	Raonament
`socis.sucursal_id` → `sucursals`	`RESTRICT`	`CASCADE`	No es tanca una sucursal sense reassignar-ne abans els socis
`llibres.autor_id` → `autors`	`SET NULL`	`CASCADE`	El llibre continua existint encara que es depuri la fitxa de l'autor: queda com a obra sense autor catalogat, igual que "Memòria de l'Eixample"
`exemplars.llibre_id` → `llibres`	`CASCADE`	`CASCADE`	Composició: l'exemplar no existeix sense la seva obra
`exemplars.sucursal_id` → `sucursals`	`RESTRICT`	`CASCADE`	Els exemplars s'han de traslladar físicament, no esborrar
`prestecs.soci_id` → `socis`	`RESTRICT`	`CASCADE`	Històric: no es destrueix
`prestecs.exemplar_id` → `exemplars`	`RESTRICT`	`CASCADE`	Històric: no es destrueix
`reserves.soci_id` → `socis`	`CASCADE`	`CASCADE`	Una reserva és una intenció futura, no un fet comptable: sense soci no significa res
`reserves.llibre_id` → `llibres`	`CASCADE`	`CASCADE`	Ídem: sense el títol, la reserva és inútil

L'asimetria entre prestecs (RESTRICT) i reserves (CASCADE) és el cor del raonament: el préstec és història i la reserva és futur. La història es conserva; el futur que ja no pot passar es descarta.

Sobre l'`ON UPDATE CASCADE` generalitzat

Totes les nostres claus primàries són subrogades i, per definició, no canvien mai de valor (lliçó 02-01). Així que ON UPDATE CASCADE no s'activarà mai. Per què posar-lo, doncs?

És una assegurança barata. Si algun dia cal renumerar identificadors durant una migració o una fusió de dos catàlegs, la propagació serà automàtica en lloc d'un guió manual propens a errors. No costa res i evita un desastre improbable però greu. En un esquema amb claus naturals (on la clau sí que es pot corregir) l'ON UPDATE CASCADE deixa de ser una assegurança i passa a ser imprescindible.

L'esquema final de BiblioRed amb les seves accions referencials

Aquest és el lliurable de la lliçó. Substituïm les vuit claus foranes que vam crear a 02-02 per les seves versions amb accions referencials. Executa'l sobre el teu biblioredb:

-- ============================================================
--  BiblioRed - Accions referencials
--  Mòdul 2, lliçó 02-06. Dialecte: PostgreSQL
-- ============================================================

-- socis → sucursals
ALTER TABLE socis DROP CONSTRAINT fk_socis_sucursal;
ALTER TABLE socis ADD CONSTRAINT fk_socis_sucursal
    FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

-- llibres → autors  (SET NULL: l'obra sobreviu a la fitxa de l'autor)
ALTER TABLE llibres DROP CONSTRAINT fk_llibres_autor;
ALTER TABLE llibres ADD CONSTRAINT fk_llibres_autor
    FOREIGN KEY (autor_id) REFERENCES autors (autor_id)
    ON DELETE SET NULL ON UPDATE CASCADE;

-- exemplars → llibres  (CASCADE: composició)
ALTER TABLE exemplars DROP CONSTRAINT fk_exemplars_llibre;
ALTER TABLE exemplars ADD CONSTRAINT fk_exemplars_llibre
    FOREIGN KEY (llibre_id) REFERENCES llibres (llibre_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

-- exemplars → sucursals
ALTER TABLE exemplars DROP CONSTRAINT fk_exemplars_sucursal;
ALTER TABLE exemplars ADD CONSTRAINT fk_exemplars_sucursal
    FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

-- prestecs → socis  (RESTRICT: l'històric no es destrueix)
ALTER TABLE prestecs DROP CONSTRAINT fk_prestecs_soci;
ALTER TABLE prestecs ADD CONSTRAINT fk_prestecs_soci
    FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

-- prestecs → exemplars  (RESTRICT: atura la cascada de llibres)
ALTER TABLE prestecs DROP CONSTRAINT fk_prestecs_exemplar;
ALTER TABLE prestecs ADD CONSTRAINT fk_prestecs_exemplar
    FOREIGN KEY (exemplar_id) REFERENCES exemplars (exemplar_id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

-- reserves → socis  (CASCADE: intenció futura, no fet comptable)
ALTER TABLE reserves DROP CONSTRAINT fk_reserves_soci;
ALTER TABLE reserves ADD CONSTRAINT fk_reserves_soci
    FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

-- reserves → llibres
ALTER TABLE reserves DROP CONSTRAINT fk_reserves_llibre;
ALTER TABLE reserves ADD CONSTRAINT fk_reserves_llibre
    FOREIGN KEY (llibre_id) REFERENCES llibres (llibre_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

Comprovació:

biblioredb=> \d prestecs

Foreign-key constraints:
    "fk_prestecs_exemplar" FOREIGN KEY (exemplar_id) REFERENCES exemplars(exemplar_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
    "fk_prestecs_soci" FOREIGN KEY (soci_id) REFERENCES socis(soci_id)
        ON UPDATE CASCADE ON DELETE RESTRICT

I una comprovació que les regles s'apliquen de veritat:

-- El llibre 338 ("Manual de jardineria urbana") té 2 exemplars
-- i ZERO préstecs. La cascada hauria de funcionar.
SELECT COUNT(*) FROM exemplars WHERE llibre_id = 338;   -- 2

-- Ho provem… i ho desfem amb una transacció (lliçó 06-01)
BEGIN;
DELETE FROM llibres WHERE llibre_id = 338;
SELECT COUNT(*) FROM exemplars WHERE llibre_id = 338;   -- 0: la cascada va actuar
ROLLBACK;

SELECT COUNT(*) FROM exemplars WHERE llibre_id = 338;   -- 2: tot restaurat

Aquí apareix per primera vegada una utilitat pràctica de les transaccions: provar una operació destructiva i desfer-la. BEGIN obre la transacció, ROLLBACK l'anul·la completament. És el contingut de la lliçó 06-01; de moment, fes-ho servir com a xarxa de seguretat.

La versió SQLite

SQLite no admet ALTER TABLE ... ADD CONSTRAINT. Per afegir-hi accions referencials cal recrear les taules amb la definició completa. En un CREATE TABLE de SQLite s'escriu igual:

CREATE TABLE prestecs (
    prestec_id              INTEGER PRIMARY KEY,
    soci_id                 INTEGER NOT NULL
        REFERENCES socis (soci_id)         ON DELETE RESTRICT ON UPDATE CASCADE,
    exemplar_id             INTEGER NOT NULL
        REFERENCES exemplars (exemplar_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    data_prestec            TEXT NOT NULL,
    data_devolucio_prevista TEXT NOT NULL,
    data_devolucio          TEXT,
    recarrec                NUMERIC
);

I sempre, sempre, PRAGMA foreign_keys = ON;. Ho veiem a l'apartat 10.

Claus foranes compostes

Si la clau primària de la taula pare és composta (diverses columnes), la clau forana que la referencia també ho ha de ser, amb les columnes en el mateix ordre.

Imagina que BiblioRed decideix catalogar la ubicació física exacta de cada exemplar. Els prestatges es numeren dins de cada sucursal: hi ha un prestatge A-12 a Centre i un altre A-12 a Nord, i no són el mateix. La clau primària natural és aleshores la parella:

CREATE TABLE prestatges (
    sucursal_id    INTEGER     NOT NULL,
    codi_prestatge VARCHAR(10) NOT NULL,
    sala           VARCHAR(40),
    CONSTRAINT pk_prestatges PRIMARY KEY (sucursal_id, codi_prestatge),
    CONSTRAINT fk_prestatges_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

I exemplars la referenciaria així:

ALTER TABLE exemplars ADD COLUMN codi_prestatge VARCHAR(10);

ALTER TABLE exemplars ADD CONSTRAINT fk_exemplars_prestatge
    FOREIGN KEY (sucursal_id, codi_prestatge)
    REFERENCES prestatges (sucursal_id, codi_prestatge)
    ON DELETE SET NULL ON UPDATE CASCADE;

Tres regles que cal conèixer:

L'ordre importa. FOREIGN KEY (a, b) REFERENCES t (x, y) aparella a amb x i b amb y. Invertir-los crea una restricció diferent i probablement absurda.
La forma de taula és obligatòria. Una restricció que afecta dues columnes no cap a la declaració d'una de sola.
Compte amb els NULL parcials. Per defecte (MATCH SIMPLE, el comportament estàndard), si alguna de les columnes és NULL, la restricció no es comprova en absolut. És a dir: un exemplar amb sucursal_id = 2 i codi_prestatge = NULL passaria la validació encara que no existeixi aquest prestatge. Si vols exigir que hi siguin totes dues o cap, cal escriure MATCH FULL.

Una observació de disseny: a l'exemple anterior hi ha un ON DELETE SET NULL sobre una clau forana composta de la qual sucursal_id forma part… i sucursal_id és NOT NULL a exemplars. Això fa que l'acció falli a la pràctica. És un bon recordatori que les claus foranes compostes són més delicades del que semblen, i una de les raons de pes a favor de les claus subrogades simples. Aquestes decisions de modelatge es tracten a fons al mòdul 4.

Aquestes taules són il·lustratives: no les creïs a biblioredb, no formen part de l'esquema del curs.

Restriccions diferibles

Per defecte, les claus foranes es comproven immediatament, en executar cada instrucció. Això planteja un problema en tres situacions reals:

Referències circulars. Si la taula A referencia B i B referencia A, no es pot inserir la primera fila de cap de les dues.
Càrregues massives en ordre arbitrari. Un bolcat de dades que insereix prestecs abans que socis fallarà, encara que en acabar tot sigui coherent.
Intercanvis. Canviar dues files d'identificador entre si passa per un estat intermedi invàlid.

La solució de l'estàndard és declarar la restricció diferible: les seves comprovacions es posposen fins al COMMIT de la transacció.

ALTER TABLE prestecs DROP CONSTRAINT fk_prestecs_soci;
ALTER TABLE prestecs ADD CONSTRAINT fk_prestecs_soci
    FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
    ON DELETE NO ACTION ON UPDATE CASCADE
    DEFERRABLE INITIALLY DEFERRED;

Tres modes possibles:

Declaració	Comportament
(res) o `NOT DEFERRABLE`	Comprovació immediata, sempre. El valor per defecte.
`DEFERRABLE INITIALLY IMMEDIATE`	Immediata per defecte, però es pot diferir en una transacció concreta amb `SET CONSTRAINTS ... DEFERRED`
`DEFERRABLE INITIALLY DEFERRED`	Diferida al `COMMIT` per defecte

Amb la restricció diferida, això funciona:

BEGIN;
  -- Inserim el préstec ABANS que el soci: estat intermedi invàlid
  INSERT INTO prestecs (soci_id, exemplar_id, data_prestec, data_devolucio_prevista)
  VALUES (21, 13, '2026-08-02', '2026-08-23');

  INSERT INTO socis (soci_id, nom, cognoms, data_alta, sucursal_id, actiu)
  VALUES (21, 'Berta', 'Colomer', '2026-08-02', 3, TRUE);
COMMIT;   -- aquí es comprova tot: correcte

Si en arribar al COMMIT la coherència no s'hagués restablert, la transacció sencera s'anul·la.

Quatre advertiments:

RESTRICT no es pot diferir mai. Només NO ACTION admet DEFERRABLE. És la diferència pràctica entre totes dues que anunciàvem a l'apartat 5.
Les restriccions diferides consumeixen més memòria, perquè el gestor ha de recordar totes les comprovacions pendents fins al COMMIT.
L'error apareix en confirmar, no a la instrucció culpable, cosa que dificulta el diagnòstic.
SQLite només admet DEFERRABLE INITIALLY DEFERRED, i únicament si les claus foranes estan activades.

Consell: no les facis servir per defecte. Són una eina per a casos concrets —càrregues massives, migracions, referències circulars—, no una comoditat general. Desfés l'experiment complet si l'has provat, perquè biblioredb torni al seu estat de l'apartat 7:

-- 1) Esborrar les dades de prova (primer la filla, després la pare)
DELETE FROM prestecs WHERE soci_id = 21;
DELETE FROM socis    WHERE soci_id = 21;

-- 2) Restaurar la restricció no diferible
ALTER TABLE prestecs DROP CONSTRAINT fk_prestecs_soci;
ALTER TABLE prestecs ADD CONSTRAINT fk_prestecs_soci
    FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

SELECT COUNT(*) FROM socis;      -- 10
SELECT COUNT(*) FROM prestecs;   -- 12

SQLite: `PRAGMA foreign_keys = ON`

Aquest és probablement l'advertiment més important de la lliçó, i el més fàcil de passar per alt.

SQLite accepta la sintaxi REFERENCES, la desa a l'esquema, la mostra a .schema… i NO L'APLICA, llevat que s'activi explícitament a cada connexió.

Per compatibilitat amb versions antigues, les claus foranes estan desactivades per defecte. El resultat és un esquema que sembla protegit i no ho està:

sqlite> PRAGMA foreign_keys;
0
sqlite> INSERT INTO prestecs (soci_id, exemplar_id, data_prestec, data_devolucio_prevista)
   ...> VALUES (77, 1, '2026-08-02', '2026-08-23');
sqlite>

Ni error, ni avís: una fila òrfena acabada de crear, referida a un soci 77 que no existeix. Ara amb la comprovació activada:

sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO prestecs (soci_id, exemplar_id, data_prestec, data_devolucio_prevista)
   ...> VALUES (77, 1, '2026-08-02', '2026-08-23');
Error: FOREIGN KEY constraint failed

El que cal saber:

És per connexió, no per base de dades. Cada vegada que obres sqlite3 o cada vegada que la teva aplicació obre una connexió, cal executar-ho de nou. No es guarda al fitxer.
No es pot activar dins d'una transacció: si ho intentes, s'ignora en silenci.
Les biblioteques d'accés a dades no sempre ho fan per tu. Alguns ORM i controladors ho activen automàticament; d'altres no. Comprova-ho tu.
Posa-ho com a primera línia de tots els teus guions .sql.

I una eina de diagnòstic específica, molt útil quan heretes un fitxer:

sqlite> PRAGMA foreign_key_check;
prestecs|13|socis|0

Cada línia és una violació existent: taula, rowid de la fila culpable, taula pare i índex de la clau forana. Sense arguments revisa tota la base de dades. És el primer que cal executar en rebre una base SQLite aliena.

Detectar i netejar files òrfenes

Activar les claus foranes impedeix crear orfes nous. No arregla els que ja existeixen: de fet, PostgreSQL es negarà a crear la restricció mentre en quedin. Necessitem detectar-los i netejar-los primer.

Reproduïm l'escenari real: BiblioRed importa els préstecs històrics del full de càlcul a una taula intermèdia sense restriccions, que és com es fan totes les migracions.

CREATE TABLE prestecs_import (
    fila           INTEGER,
    soci_id        INTEGER,
    codi_exemplar  VARCHAR(10),
    data_prestec   DATE
);

INSERT INTO prestecs_import (fila, soci_id, codi_exemplar, data_prestec) VALUES
    (1, 14,   'EJ-3081', '2026-02-03'),
    (2, 77,   'EJ-3085', '2026-02-05'),   -- soci inexistent
    (3, 16,   'EJ-9999', '2026-02-08'),   -- exemplar inexistent
    (4, 15,   'EJ-3084', '2026-02-11'),
    (5, NULL, 'EJ-3082', '2026-02-14'),   -- soci sense identificar
    (6, 77,   'EJ-3090', '2026-02-19');   -- soci inexistent, un altre cop

Detecció amb `LEFT JOIN ... IS NULL`

És el patró anti-join de la lliçó 02-04, aplicat a l'auditoria de dades.

-- Files el soci de les quals no existeix (excloem els NULL: són una altra categoria de problema)
SELECT i.fila, i.soci_id, i.codi_exemplar, i.data_prestec
FROM prestecs_import i
LEFT JOIN socis s ON s.soci_id = i.soci_id
WHERE i.soci_id IS NOT NULL
  AND s.soci_id IS NULL
ORDER BY i.fila;

fila	soci_id	codi_exemplar	data_prestec
2	77	EJ-3085	2026-02-05
6	77	EJ-3090	2026-02-19

-- Files l'exemplar de les quals no existeix
SELECT i.fila, i.soci_id, i.codi_exemplar
FROM prestecs_import i
LEFT JOIN exemplars e ON e.codi = i.codi_exemplar
WHERE e.exemplar_id IS NULL
ORDER BY i.fila;

fila	soci_id	codi_exemplar
3	16	EJ-9999

La mateixa pregunta amb NOT EXISTS, que és igual de vàlida i una mica més llegible:

SELECT i.fila, i.soci_id
FROM prestecs_import i
WHERE i.soci_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM socis s WHERE s.soci_id = i.soci_id);

Un informe d'auditoria complet

SELECT i.fila,
       i.soci_id,
       i.codi_exemplar,
       CASE WHEN i.soci_id IS NULL               THEN 'soci sense identificar'
            WHEN s.soci_id IS NULL               THEN 'soci inexistent'
            WHEN e.exemplar_id IS NULL           THEN 'exemplar inexistent'
            ELSE 'correcta'
       END AS diagnostic
FROM prestecs_import i
LEFT JOIN socis     s ON s.soci_id = i.soci_id
LEFT JOIN exemplars e ON e.codi    = i.codi_exemplar
ORDER BY i.fila;

fila	soci_id	codi_exemplar	diagnostic
1	14	EJ-3081	correcta
2	77	EJ-3085	soci inexistent
3	16	EJ-9999	exemplar inexistent
4	15	EJ-3084	correcta
5	(NULL)	EJ-3082	soci sense identificar
6	77	EJ-3090	soci inexistent

I el resum per a la reunió de projecte, amb el que hem après a 02-05:

SELECT CASE WHEN i.soci_id IS NULL     THEN 'soci sense identificar'
            WHEN s.soci_id IS NULL     THEN 'soci inexistent'
            WHEN e.exemplar_id IS NULL THEN 'exemplar inexistent'
            ELSE 'correcta' END AS diagnostic,
       COUNT(*) AS files
FROM prestecs_import i
LEFT JOIN socis     s ON s.soci_id = i.soci_id
LEFT JOIN exemplars e ON e.codi    = i.codi_exemplar
GROUP BY 1
ORDER BY files DESC;

diagnostic	files
soci inexistent	2
correcta	2
exemplar inexistent	1
soci sense identificar	1

Dos terços de les files tenen problemes. Aquesta és la dada amb què es parlarà amb la direcció.

Les quatre estratègies de neteja

Estratègia	Quan	Com
Corregir	El valor correcte és deduïble (`77` era `17`, un error de teclat)	`UPDATE` fila a fila, amb criteri humà
Posar a `NULL`	La columna ho admet i "desconegut" és acceptable	`UPDATE ... SET soci_id = NULL WHERE ...`
Crear la fila pare	El pare existia de veritat i es va perdre en la migració	`INSERT` a la taula pare
Descartar	La fila no es pot salvar	Moure-la a una taula de quarantena i esborrar-la

La regla d'or: no esborris mai orfes sense guardar-los abans. Pot ser que la informació que falta sigui en un altre lloc, i un cop esborrada no torna.

-- 1) Quarantena: guardem el que no es pot importar
CREATE TABLE prestecs_import_rebutjats AS
SELECT i.*
FROM prestecs_import i
LEFT JOIN socis     s ON s.soci_id = i.soci_id
LEFT JOIN exemplars e ON e.codi    = i.codi_exemplar
WHERE i.soci_id IS NULL OR s.soci_id IS NULL OR e.exemplar_id IS NULL;

SELECT COUNT(*) FROM prestecs_import_rebutjats;   -- 4

-- 2) Importar només el que és vàlid a la taula real
INSERT INTO prestecs (soci_id, exemplar_id, data_prestec, data_devolucio_prevista)
SELECT i.soci_id, e.exemplar_id, i.data_prestec, i.data_prestec + 21
FROM prestecs_import i
INNER JOIN socis     s ON s.soci_id = i.soci_id
INNER JOIN exemplars e ON e.codi    = i.codi_exemplar;

INSERT 0 2

Fixa't en el detall: l'INSERT ... SELECT amb INNER JOIN importa únicament les files que aparellen. Les òrfenes es queden fora per construcció, sense necessitat d'un WHERE que les filtri. És l'ús deliberat d'una propietat de l'INNER JOIN que en altres contextos era un perill.

Ara netegem l'experiment per deixar biblioredb com estava (els dos préstecs importats tindrien els identificadors 13 i 14):

DELETE FROM prestecs WHERE prestec_id > 12;
DROP TABLE prestecs_import_rebutjats;
DROP TABLE prestecs_import;

SELECT COUNT(*) FROM prestecs;   -- 12

Validar a l'aplicació o a la base de dades?

És una discussió recurrent als equips de desenvolupament, i mereix una resposta raonada.

L'argument a favor de l'aplicació: els missatges d'error són millors ("Aquest soci no existeix, el vols donar d'alta?" en lloc d'un text en anglès sobre fk_prestecs_soci), es valida abans d'arribar a la base de dades, i amb un ORM les relacions ja estan descrites al codi.

L'argument a favor de la base de dades, que és el decisiu:

La base de dades no és només teva. Al llarg de la seva vida, biblioredb rebrà escriptures de l'aplicació web, del procés nocturn d'importació, del guió que algú executa des de psql a les onze de la nit, de l'eina d'administració gràfica i de la migració d'aquí a tres anys. Cadascun d'aquests camins hauria de reimplementar les mateixes validacions. Un d'ells no ho farà.
Les dades duren més que el codi. Les aplicacions es reescriuen cada cinc o set anys; les dades es conserven dècades. Una regla que només viu al codi desapareix amb ell.
La concurrència. Comprovar a l'aplicació "existeix el soci 14?" i després inserir el préstec deixa una finestra entre totes dues operacions. Si en aquest interval una altra sessió esborra el soci 14, la comprovació era inútil. La base de dades verifica dins de l'operació, amb els bloqueigs adequats. Aquest raonament es desenvolupa a les lliçons 06-01 i 06-02.
Les operacions massives no passen per l'aplicació. Un UPDATE de 40.000 files s'executa en SQL. Cap validació del codi no el veu.

La conclusió no és "l'una o l'altra", sinó totes dues, en capes:

flowchart TD
    A["Interfície d'usuari<br/>validació immediata, missatges clars"] --> B["Lògica d'aplicació<br/>regles de negoci complexes"]
    B --> C["Base de dades<br/>claus foranes, UNIQUE, NOT NULL, CHECK"]
    C --> D[("Dades<br/>sempre coherents")]
    A -.->|"es pot saltar"| C
    B -.->|"es pot saltar"| C
    style C fill:#2d6a4f,color:#ffffff

L'aplicació valida per a la persona: missatges útils, formularis que guien, errors detectats abans d'enviar. La base de dades valida per a les dades: és l'última línia de defensa, la que ningú no pot saltar-se, la que continua sent-hi quan l'aplicació canviï.

Un cas pràctic: BiblioRed valida al formulari web que el correu tingui format de correu (això la base de dades no ho comprova bé), i la base de dades garanteix que sigui UNIQUE i que el sucursal_id existeixi (això l'aplicació no ho pot garantir de manera fiable). Cada capa fa el que sap fer.

Nota final: les restriccions CHECK —que verifiquen condicions sobre els valors, com "l'estat ha de ser un d'aquests quatre" o "la data de devolució no pot ser anterior a la de préstec"— són l'altra gran eina de validació a la base de dades. Es tracten a fons a la lliçó 04-04, juntament amb DEFAULT i els criteris d'elecció de tipus.

Errors Habituals i Consells

Oblidar PRAGMA foreign_keys = ON a SQLite. L'esquema sembla correcte i no protegeix res. És el parany número u d'aquesta lliçó, i cal repetir-ho a cada connexió.
Posar ON DELETE CASCADE per comoditat. "Així no dóna errors en esborrar" és la pitjor raó possible. Un DELETE sobre una fila pot endur-se'n milers d'encadenades, sense avís i sense paperera.
Esborrar entitats amb historial. Els socis, els clients, els productes venuts no s'esborren: es marquen com a inactius. RESTRICT és el teu aliat precisament perquè t'obliga a plantejar-te aquesta decisió.
Confondre RESTRICT amb NO ACTION. Es comporten igual llevat d'un detall decisiu: RESTRICT no admet diferiment.
Fer servir SET NULL sobre una columna NOT NULL. PostgreSQL rebutja la definició; en altres gestors l'error apareix més tard, en intentar l'esborrament.
Creure que una clau forana nul·la és un error. No ho és: NULL significa "no apunta a ningú". Si la referència ha de ser obligatòria, afegeix-hi NOT NULL.
Deixar les restriccions sense nom. El dia que hagis de fer DROP CONSTRAINT, hauràs d'anar a buscar el nom automàtic al catàleg.
Referenciar una columna sense UNIQUE. El gestor ho rebutja, i amb raó: la referència seria ambigua.
Esborrar orfes sense guardar-los. Mou-los sempre a una taula de quarantena abans. El que s'esborra no torna.
Confiar només en la validació de l'aplicació. Hi haurà un altre camí d'escriptura. Sempre n'hi ha.
Consell: documenta l'acció referencial escollida amb un comentari al CREATE TABLE. D'aquí a dos anys, "per què això és RESTRICT i allò CASCADE?" serà una pregunta real, i la resposta és una decisió de negoci, no tècnica.
Consell: quan heretis una base de dades, executa abans que res l'auditoria d'orfes de cada clau forana (o PRAGMA foreign_key_check a SQLite). Et dirà en trenta segons amb quina qualitat de dades estàs treballant.

Exercicis

Exercici 1: Decidir l'acció referencial

BiblioRed vol afegir tres taules noves. Per a cada clau forana, decideix ON DELETE i justifica l'elecció en una frase.

ressenyes (ressenya_id, soci_id, llibre_id, text, puntuacio, data): ressenyes escrites pels socis sobre els llibres.
multes (multa_id, prestec_id, import, data_emissio, pagada): sancions econòmiques derivades d'un préstec.
esdeveniments (esdeveniment_id, sucursal_id, titol, data): activitats culturals organitzades per cada sucursal.
inscripcions (inscripcio_id, esdeveniment_id, soci_id, data_inscripcio): socis apuntats a aquests esdeveniments.

Exercici 2: Predir l'efecte d'un esborrament

Amb l'esquema final de l'apartat 7 i el joc de dades del curs, digues què passa amb cada instrucció i quantes files es veuen afectades en total.

DELETE FROM autors WHERE autor_id = 8; (Marina Escolà, sense obres)
DELETE FROM autors WHERE autor_id = 4; (Óscar Barreda, dues obres)
DELETE FROM llibres WHERE llibre_id = 339; (Memòria de l'Eixample, 1 exemplar sense préstecs)
DELETE FROM llibres WHERE llibre_id = 331; (El mapa del temps, 3 exemplars, 4 préstecs, 2 reserves)
DELETE FROM socis WHERE soci_id = 20; (Elena Roig, sense préstecs ni reserves)
DELETE FROM socis WHERE soci_id = 16; (Núria Bastos, 2 préstecs, 1 reserva)
DELETE FROM sucursals WHERE sucursal_id = 4; (Est, 1 soci, 2 exemplars)

Exercici 3: Auditoria i neteja

BiblioRed ha rebut d'una altra biblioteca un fitxer amb socis per incorporar. Crea'l com a taula intermèdia:

CREATE TABLE socis_import (
    fila        INTEGER,
    nom         VARCHAR(60),
    cognoms     VARCHAR(80),
    email       VARCHAR(120),
    sucursal_id INTEGER
);

INSERT INTO socis_import (fila, nom, cognoms, email, sucursal_id) VALUES
    (1, 'Rosa',   'Cabanes', '[email protected]',   2),
    (2, 'Teo',    'Ninot',   '[email protected]',      9),
    (3, 'Amina',  'Bakri',   '[email protected]',    1),
    (4, 'Lluc',   'Ferrer',  '[email protected]',   3),
    (5, 'Selma',  'Duarte',  NULL,                         7),
    (6, 'Jordi',  'Pons',    '[email protected]',     NULL);

Escriu una consulta que detecti les files la sucursal_id de les quals no existeix.
Escriu una consulta que detecti les files l'email de les quals ja està en ús per un soci actual.
Escriu un informe d'auditoria amb una columna diagnostic que classifiqui cada fila.
Importa únicament les files vàlides i comprova quantes n'han entrat. Després, deixa biblioredb com estava.

Solucions

Solució 1

Taula	Clau forana	`ON DELETE`	Justificació
`ressenyes`	`soci_id` → `socis`	`SET NULL`	La ressenya té valor per als altres lectors encara que el seu autor es doni de baixa: passa a ser anònima. (`CASCADE` seria defensable si la política de privacitat exigís esborrar tot rastre del soci; és una decisió legal, no tècnica.)
`ressenyes`	`llibre_id` → `llibres`	`CASCADE`	Una ressenya d'un llibre que ja no és al catàleg no té cap lector possible.
`multes`	`prestec_id` → `prestecs`	`RESTRICT`	És un registre econòmic. A més, `prestecs.soci_id` ja és `RESTRICT`, així que la protecció és coherent a tota la cadena.
`esdeveniments`	`sucursal_id` → `sucursals`	`RESTRICT` (o `SET NULL`)	Els esdeveniments passats són historial d'activitat; esborrar-los en tancar una sucursal destruiria les estadístiques anuals.
`inscripcions`	`esdeveniment_id` → `esdeveniments`	`CASCADE`	Sense esdeveniment, la inscripció no significa res.
`inscripcions`	`soci_id` → `socis`	`CASCADE`	Igual que les reserves: és una intenció futura, no un fet comptable.

El patró que emergeix: fets econòmics i històrics → RESTRICT; intencions i elements accessoris → CASCADE; contingut amb valor propi → SET NULL.

Solució 2

#	Què passa	Files afectades
1	Èxit. Marina Escolà no té obres, així que el `SET NULL` no toca res.	1 (l'autora)
2	Èxit amb `SET NULL`. Els llibres 334 i 338 sobreviuen amb `autor_id = NULL`; els seus exemplars i préstecs queden intactes.	3 (1 autor + 2 llibres modificats)
3	Èxit amb `CASCADE`. S'esborra el llibre i, en cascada, el seu exemplar `EJ-3095`, que no té préstecs. No hi ha reserves del 339.	2 (1 llibre + 1 exemplar)
4	ERROR. La cascada intenta esborrar els exemplars 1, 2 i 3, però el `RESTRICT` de `fk_prestecs_exemplar` ho impedeix: hi ha 4 préstecs apuntant-hi. No s'esborra res.	0
5	Èxit. Elena Roig no té res associat.	1
6	ERROR. El `RESTRICT` de `fk_prestecs_soci` bloqueja l'esborrament pels seus 2 préstecs. La reserva s'hauria esborrat en cascada, però l'operació sencera s'anul·la.	0
7	ERROR. El `RESTRICT` de `fk_socis_sucursal` (Lucía Vendrell està donada d'alta a Est) i el de `fk_exemplars_sucursal` (2 exemplars) ho impedeixen. Cal reassignar abans socis i exemplars.	0

Observació important sobre els casos 4, 6 i 7: la fallada és atòmica. Encara que la cascada hagués començat a esborrar files abans de xocar amb el RESTRICT, tot es desfà: la instrucció és una unitat. Això ho garanteixen les transaccions, tema de la lliçó 06-01.

Solució 3

-- 1  Sucursal inexistent (excloent els NULL, que són un altre cas)
SELECT i.fila, i.cognoms, i.sucursal_id
FROM socis_import i
LEFT JOIN sucursals su ON su.sucursal_id = i.sucursal_id
WHERE i.sucursal_id IS NOT NULL
  AND su.sucursal_id IS NULL
ORDER BY i.fila;

fila	cognoms	sucursal_id
2	Ninot	9
5	Duarte	7

-- 2  Correu ja en ús: violaria uq_socis_email
SELECT i.fila, i.cognoms, i.email
FROM socis_import i
INNER JOIN socis s ON s.email = i.email
ORDER BY i.fila;

fila	cognoms	email
4	Ferrer	[email protected]

-- 3  Informe d'auditoria
SELECT i.fila,
       i.nom || ' ' || i.cognoms AS soci,
       CASE WHEN i.sucursal_id IS NULL   THEN 'sense sucursal assignada'
            WHEN su.sucursal_id IS NULL  THEN 'sucursal inexistent'
            WHEN s.soci_id IS NOT NULL   THEN 'correu duplicat'
            ELSE 'correcta'
       END AS diagnostic
FROM socis_import i
LEFT JOIN sucursals su ON su.sucursal_id = i.sucursal_id
LEFT JOIN socis s      ON s.email        = i.email
ORDER BY i.fila;

fila	soci	diagnostic
1	Rosa Cabanes	correcta
2	Teo Ninot	sucursal inexistent
3	Amina Bakri	correcta
4	Lluc Ferrer	correu duplicat
5	Selma Duarte	sucursal inexistent
6	Jordi Pons	sense sucursal assignada

Nota: la fila 6 no viola cap clau forana —NULL és una referència vàlida— però sí que violaria el NOT NULL de socis.sucursal_id. Són dues restriccions diferents i totes dues s'han d'auditar.

-- 4  Importar només el que és vàlid
INSERT INTO socis (nom, cognoms, email, data_alta, sucursal_id, actiu)
SELECT i.nom, i.cognoms, i.email, '2026-08-02', i.sucursal_id, TRUE
FROM socis_import i
INNER JOIN sucursals su ON su.sucursal_id = i.sucursal_id
WHERE NOT EXISTS (SELECT 1 FROM socis s WHERE s.email = i.email);

INSERT 0 2

Hi entren Rosa Cabanes i Amina Bakri. L'INNER JOIN amb sucursals descarta per construcció les files 2, 5 i 6, i el NOT EXISTS descarta la 4.

-- Comprovació i neteja
SELECT COUNT(*) FROM socis;   -- 12

DELETE FROM socis WHERE cognoms IN ('Cabanes', 'Bakri');
DROP TABLE socis_import;

SELECT COUNT(*) FROM socis;   -- 10

Conclusió

Tanquem el mòdul amb les garanties que fan que tot l'anterior continuï sent cert amb el pas del temps:

Una fila òrfena és una clau forana que apunta a alguna cosa inexistent. Naixia cada dia al full de càlcul de BiblioRed per teclat, per esborraments i per renumeracions, i el seu dany és silenciós: els INNER JOIN l'eliminen sense avisar i els totals deixen de quadrar.
Una clau forana es declara en forma de columna o, millor, de taula amb nom propi (CONSTRAINT fk_...), i només pot referenciar columnes amb PRIMARY KEY o UNIQUE. En afegir-la amb ALTER TABLE, el gestor valida totes les files existents.
El gestor comprova a l'INSERT i a l'UPDATE de la taula filla, i al DELETE i a l'UPDATE de la clau primària de la taula pare. Una clau forana nul·la és legal: si la referència ha de ser obligatòria, cal afegir-hi NOT NULL.
Les cinc accions referencials —NO ACTION, RESTRICT, CASCADE, SET NULL i SET DEFAULT— defineixen què passa amb les filles quan desapareix el pare. RESTRICT no admet diferiment; aquesta és la seva única diferència real amb NO ACTION.
El criteri d'elecció a BiblioRed: el préstec és història (RESTRICT) i la reserva és futur (CASCADE); l'exemplar és una part del llibre (CASCADE) i el llibre sobreviu a la fitxa del seu autor (SET NULL). Un soci no s'esborra: es marca com a inactiu.
Les claus foranes compostes exigeixen la forma de taula, respecten l'ordre de les columnes i, amb MATCH SIMPLE, no es comproven si alguna columna és nul·la.
Les restriccions diferibles (DEFERRABLE INITIALLY DEFERRED) posposen la comprovació al COMMIT, i serveixen per a referències circulars, càrregues massives i intercanvis. No són una comoditat general.
SQLite no aplica les claus foranes si no s'activa PRAGMA foreign_keys = ON, a cada connexió. PRAGMA foreign_key_check audita una base sencera.
Els orfes que ja existeixen es detecten amb el patró LEFT JOIN ... IS NULL (o NOT EXISTS), es classifiquen amb CASE WHEN, es guarden en quarantena i només llavors es descarten. Un INSERT ... SELECT amb INNER JOIN importa per construcció únicament el que és vàlid.
I la conclusió de fons: es valida a les dues capes. L'aplicació valida per a la persona; la base de dades és l'última línia de defensa, perquè hi haurà altres camins d'escriptura, perquè les dades duren més que el codi i perquè només el gestor pot verificar dins de l'operació, sense finestres de concurrència.

Amb això acaba el mòdul 2. Has recorregut el camí complet del món relacional: la teoria del model i les seves regles d'integritat, el llenguatge SQL i la creació de l'esquema, el CRUD sobre una taula, la reunió de diverses taules amb JOIN i subconsultes, el resum amb agregats i agrupacions, i les garanties referencials que ho sostenen. biblioredb ja no és una base buida: és un sistema d'informació amb set taules, dades coherents, informes de gestió i defenses pròpies. Al mòdul 3, Bases de Dades No Relacionals, canviem de món: veurem què és NoSQL, quines famílies existeixen, com es modelen les dades quan no hi ha esquema fix ni claus foranes que les protegeixin —i què es guanya i què es perd en aquest tracte—. BiblioRed ve amb nosaltres: les seves ressenyes i el seu registre d'activitat són, com vam decidir a la lliçó 01-02, el cas d'ús perfecte per a MongoDB.

Integritat Referencial

Contingut

Files òrfenes: d'on vénen i què trenquen

Declarar una clau forana

Forma de columna

Forma de taula, amb nom propi

Afegir-la després

Requisits de la columna referenciada

Què comprova el gestor i quan

Les accions referencials ON DELETE i ON UPDATE

Un laboratori per provar-les

Les cinc opcions, comparades

NO ACTION i RESTRICT: la diferència real

SET DEFAULT: la que gairebé no es fa servir mai

Triar l'acció correcta a BiblioRed

prestecs.soci_id → RESTRICT

exemplars.llibre_id → CASCADE

El quadre complet

Sobre l'ON UPDATE CASCADE generalitzat

L'esquema final de BiblioRed amb les seves accions referencials

La versió SQLite

Claus foranes compostes

Restriccions diferibles

SQLite: PRAGMA foreign_keys = ON

Detectar i netejar files òrfenes

Detecció amb LEFT JOIN ... IS NULL

Un informe d'auditoria complet

Les quatre estratègies de neteja

Validar a l'aplicació o a la base de dades?

Errors Habituals i Consells

Exercicis

Exercici 1: Decidir l'acció referencial

Exercici 2: Predir l'efecte d'un esborrament

Exercici 3: Auditoria i neteja

Solucions

Solució 1

Solució 2

Solució 3

Conclusió

Fonaments de Bases de Dades

Mòdul 1: Introducció a les Bases de Dades

Mòdul 2: Bases de Dades Relacionals

Mòdul 3: Bases de Dades No Relacionals

Mòdul 4: Disseny d'Esquemes

Mòdul 5: Normalització

Mòdul 6: Transaccions, Rendiment i Seguretat

Mòdul 7: Exercicis Pràctics

Mòdul 8: Casos d'Estudi

Mòdul 9: Recursos Addicionals