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

HOME

A la lliçó anterior vam dibuixar el plànol de BiblioRed: set relacions, les seves claus i els seus vincles. Avui aprenem l'idioma amb què s'explica aquest plànol a un sistema gestor, i el fem servir per aixecar-lo de veritat dins de biblioredb.

SQL és, de bon tros, el llenguatge de propòsit específic més longeu i més rendible d'aprendre de la informàtica: va néixer als anys setanta, va sobreviure a totes les modes i avui es parla no només amb PostgreSQL, MySQL, SQLite o Oracle, sinó també amb motors analítics, amb magatzems al núvol i fins i tot amb capes de consulta sobre bases NoSQL. El que aprenguis aquí et servirà en tots ells.

Aquesta lliçó és la frontissa del curs: comença explicant el llenguatge i acaba amb un lliurable concret, l'script CREATE TABLE complet de les set taules de BiblioRed, que has d'executar abans de passar a la lliçó següent. Tingues psql o sqlite3 obert mentre la llegeixes.

Contingut

Què és SQL i per què és declaratiu
L'estàndard i per què existeixen dialectes
Els cinc subllenguatges: DDL, DML, DQL, DCL i TCL
Regles d'escriptura: identificadors, majúscules, cometes i comentaris
Tipus de dades que necessitem ara
CREATE TABLE i les restriccions bàsiques
Claus autoincrementals a PostgreSQL i a SQLite
Lliurable: l'esquema complet de BiblioRed
ALTER TABLE: modificar el que ja està creat
DROP TABLE i l'ordre de destrucció
Errors habituals i consells
Exercicis
Conclusió

Què és SQL i per què és declaratiu

SQL (Structured Query Language) és el llenguatge estàndard per definir, manipular i consultar bases de dades relacionals. El seu tret més característic és que és declaratiu: descrius quin resultat vols, no com obtenir-lo.

Compara. Així es resoldria "els préstecs oberts de la sucursal Nord" en un llenguatge imperatiu com Python, treballant amb fitxers:

obrir el fitxer de préstecs
per a cada línia:
    si data_devolucio és buida:
        buscar l'exemplar corresponent al fitxer d'exemplars
        si la seva sucursal és 2:
            afegir la línia al resultat
tancar el fitxer

Has hagut de decidir l'ordre dels bucles, quin fitxer es recorre primer i com es cerca. Ara en SQL:

SELECT p.prestec_id, p.data_prestec
FROM prestecs p
JOIN exemplars e ON e.exemplar_id = p.exemplar_id
WHERE p.data_devolucio IS NULL
  AND e.sucursal_id = 2;

No hi ha bucles, ni ordre de recorregut, ni estructures de dades. Només la descripció del resultat. L'optimitzador de consultes —el component que vam estudiar a la lliçó 01-04— és qui decideix si recorre primer prestecs o exemplars, si fa servir un índex o llegeix la taula sencera, i amb quin algorisme aparella les files. I aquesta decisió la torna a prendre cada vegada, amb les estadístiques del moment: la mateixa consulta que avui es resol d'una manera pot resoldre's demà d'una altra, més ràpida, sense que tu canviïs ni una lletra.

Les tres conseqüències pràctiques d'això:

Escrius menys i més clar. Una consulta de cinc línies substitueix cinquanta de codi imperatiu.
No optimitzes a mà el que el gestor optimitza millor. Reordenar les taules del FROM per "anar més de pressa" és, en general, temps perdut: l'optimitzador reordena pel seu compte.
Quan alguna cosa va lenta, es diagnostica mirant el pla, no el SQL. És el que farem amb EXPLAIN a la lliçó 06-03.

SQL no és purament declaratiu ni purament relacional (ja vam veure que admet duplicats), però aquesta barreja pragmàtica és justament el que el va fer triomfar.

L'estàndard i per què existeixen dialectes

SQL està estandarditzat per ISO i ANSI des del 1986, amb revisions successives (SQL-92, SQL:1999, SQL:2003, SQL:2011, SQL:2023) que vam veure a la lliçó 01-03. Tanmateix, cap gestor no implementa l'estàndard exactament, i tots hi afegeixen coses pròpies. Els motius:

L'estàndard arriba tard: els fabricants inventen una funcionalitat útil, es popularitza i anys després s'estandarditza amb una altra sintaxi.
L'estàndard deixa zones opcionals o sense definir (tipus de data, autoincrement, límits de resultats), i cada fabricant omple el buit a la seva manera.
Cada motor té capacitats pròpies que l'estàndard no contempla.

Resultat: un nucli comú ampli —SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, JOIN, GROUP BY— que funciona a tot arreu, i una perifèria específica de cada dialecte.

Necessitat	Estàndard / PostgreSQL	SQLite	MySQL
Clau autoincremental	`GENERATED ALWAYS AS IDENTITY` / `SERIAL`	`INTEGER PRIMARY KEY AUTOINCREMENT`	`AUTO_INCREMENT`
Concatenar text	`'a' \|\| 'b'`	`'a' \|\| 'b'`	`CONCAT('a','b')`
Limitar files	`LIMIT 10` (`FETCH FIRST 10 ROWS ONLY` és l'estàndard)	`LIMIT 10`	`LIMIT 10`
Data actual	`CURRENT_DATE`	`DATE('now')`	`CURDATE()`
Comparació de text sense distingir majúscules	`ILIKE`	`LIKE` (ja és insensible en ASCII)	`LIKE`

En aquest curs fem servir PostgreSQL com a referència i assenyalem les diferències de SQLite quan importen. Consell permanent: escriu SQL tan estàndard com puguis i fes servir extensions del dialecte només quan aportin alguna cosa real; el teu SQL serà més fàcil de portar i, sobretot, d'entendre per una altra persona.

Els cinc subllenguatges: DDL, DML, DQL, DCL i TCL

Encara que es parli de "SQL" en singular, les seves instruccions s'agrupen per funció. Conèixer els grups t'ajuda a orientar-te i a saber en quina lliçó del curs es tracta cada cosa.

Subllenguatge	Nom complet	Per a què serveix	Instruccions principals	On es veu al curs
DDL	Data Definition Language	Definir i modificar l'estructura	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `RENAME`	Aquesta lliçó i 04-04
DML	Data Manipulation Language	Canviar les dades	`INSERT`, `UPDATE`, `DELETE`, `MERGE`	02-03
DQL	Data Query Language	Consultar les dades	`SELECT`	02-03, 02-04, 02-05
DCL	Data Control Language	Gestionar permisos	`GRANT`, `REVOKE`	06-04
TCL	Transaction Control Language	Delimitar transaccions	`COMMIT`, `ROLLBACK`, `SAVEPOINT`	06-01

Dos matisos que convé tenir clars des del principi:

Molta gent considera DQL part del DML, i per això de vegades veuràs "quatre subllenguatges". Tant se val el recompte; el que importa és la funció.
A PostgreSQL, el DDL és transaccional: pots crear taules dins d'una transacció i desfer-ho amb ROLLBACK. En molts altres gestors (MySQL amb InnoDB, Oracle) una instrucció DDL confirma implícitament el que hi hagués pendent. És una diferència amb conseqüències reals a l'hora d'escriure scripts de migració.

Avui treballem exclusivament amb DDL.

Regles d'escriptura: identificadors, majúscules, cometes i comentaris

Paraules clau i identificadors

Les paraules clau (SELECT, FROM, CREATE TABLE) formen part del llenguatge.
Els identificadors són els noms que hi poses tu: taules, columnes, restriccions, índexs.

SQL no distingeix majúscules de minúscules a les paraules clau: select, SELECT i SeLeCt són equivalents. La convenció universal és escriure les paraules clau en MAJÚSCULES i els identificadors en minúscules, perquè fa la consulta llegible d'un cop d'ull:

-- Llegible
SELECT titol, isbn FROM llibres WHERE any_publicacio > 2010;

-- Legal, però costa de llegir
select TITOL, ISBN from LLIBRES where ANY_PUBLICACIO > 2010;

El parany de les majúscules als identificadors

PostgreSQL, seguint l'estàndard, converteix els identificadors sense cometes a minúscules. Per tant DataAlta, dataalta i DATAALTA són la mateixa columna. Però si escrius l'identificador entre cometes dobles, es respecta literalment i a partir d'aquí sempre caldrà posar-lo entre cometes:

CREATE TABLE prova ("DataAlta" DATE);

SELECT DataAlta FROM prova;     -- ERROR: no existeix la columna "dataalta"
SELECT "DataAlta" FROM prova;   -- Correcte, però condemna a cometes per sempre

Consell ferm: no facis servir cometes dobles als identificadors. Anomena-ho tot en minuscules_amb_guio_baix, sense accents, sense ç i sense espais. És la convenció de PostgreSQL i t'estalvia una categoria sencera de problemes. En aquest curs, tots els noms de BiblioRed segueixen aquesta regla.

Cometes simples davant de cometes dobles

Aquesta distinció és font constant d'errors en qui ve d'altres llenguatges:

Signe	Significat en SQL	Exemple
`'cometa simple'`	Literal de text (un valor)	`WHERE estat = 'disponible'`
`"cometa doble"`	Identificador (un nom)	`SELECT "DataAlta"`

SELECT * FROM exemplars WHERE estat = "disponible";
-- ERROR a PostgreSQL: no existeix la columna «disponible»
-- PostgreSQL busca una COLUMNA anomenada disponible, no un text

SELECT * FROM exemplars WHERE estat = 'disponible';   -- Correcte

SQLite és més permissiu i en alguns casos accepta cometes dobles com a text; no t'hi acostumis, perquè aquest SQL no funcionarà a PostgreSQL.

Per incloure un apòstrof dins d'un literal, es duplica:

SELECT 'L''Hospitalet';   -- retorna: L'Hospitalet

Punt i coma

El ; acaba una instrucció. A psql i a sqlite3 és obligatori: sense ell, el client entén que la instrucció continua a la línia següent i es queda esperant. Si veus un indicador com biblioredb-# en lloc de biblioredb=#, és exactament això: et falta el punt i coma.

Comentaris

-- Comentari d'una línia: des dels dos guions fins al final

/* Comentari
   de diverses línies.
   Útil per documentar un script complet. */

SELECT titol   -- també es pot comentar al final d'una línia
FROM llibres;

Estil recomanat

Escriu consultes multilínia amb una clàusula per línia. Costa el mateix i es llegeix infinitament millor:

SELECT s.nom,
       s.cognoms,
       s.email
FROM socis s
WHERE s.actiu = TRUE
  AND s.sucursal_id = 2
ORDER BY s.cognoms;

Tipus de dades que necessitem ara

Cada columna té un tipus, i aquest tipus és la implementació de la regla d'integritat de domini de la lliçó anterior. Aquí veiem només el que cal per crear les taules de BiblioRed; el catàleg complet i els criteris fins d'elecció són la lliçó 04-04.

Números enters

Tipus	Rang aproximat	Ús típic
`SMALLINT`	±32.000	Anys, comptadors petits
`INTEGER` (`INT`)	±2.100 milions	L'opció per defecte per a claus i identificadors
`BIGINT`	±9,2·10¹⁸	Taules enormes, identificadors globals

A SQLite existeix un únic tipus enter, INTEGER, que admet fins a 8 bytes; els noms SMALLINT o BIGINT s'accepten però acaben sent el mateix.

Números decimals: la regla dels diners

Aquí hi ha una decisió que separa els professionals dels aficionats.

Tipus	Com emmagatzema	Exactitud
`NUMERIC(p, s)` / `DECIMAL(p, s)`	Decimal, amb `p` dígits totals i `s` decimals	Exacta
`REAL`, `DOUBLE PRECISION`, `FLOAT`	Coma flotant binària (IEEE 754)	Aproximada

La coma flotant no pot representar exactament valors com 0,10 en binari, igual que en decimal no podem escriure 1/3 amb un nombre finit de xifres. Això produeix resultats com aquest:

SELECT 0.1::REAL + 0.2::REAL;     -- 0.30000001
SELECT 0.1::NUMERIC + 0.2::NUMERIC;  -- 0.3

A BiblioRed la columna recarrec guarda euros. Amb coma flotant, sumar deu mil recàrrecs de 0,10 € pot donar 999,9998 en lloc de 1000,00, i el compte anual de la biblioteca no quadrarà mai.

Regla: per als diners, sempre NUMERIC(p, s). NUMERIC(6, 2) admet fins a 9999.99, més que suficient per a un recàrrec de biblioteca. Reserva la coma flotant per a magnituds científiques on la precisió aproximada és acceptable (temperatures, coordenades, mesures físiques).

Avís de SQLite: no té tipus decimal exacte. NUMERIC(6,2) s'accepta, però internament es pot emmagatzemar com a coma flotant. Per practicar tant se val; en producció amb diners, és un argument més a favor de PostgreSQL.

Text

Tipus	Descripció
`VARCHAR(n)`	Cadena de longitud variable amb màxim `n` caràcters
`CHAR(n)`	Longitud fixa; omple amb espais. Gairebé mai no és el que vols
`TEXT`	Cadena sense límit declarat

A PostgreSQL, VARCHAR(n) i TEXT tenen el mateix rendiment: VARCHAR(n) no és més ràpid, només hi afegeix una comprovació de longitud. Per això el criteri és semàntic: fes servir VARCHAR(n) quan el límit sigui una regla real (un ISBN té 13 caràcters, ni un més) i TEXT quan no hi hagi límit natural (una ressenya, unes observacions).

Dates i hores

Tipus	Guarda	Exemple
`DATE`	Només data	`2026-07-14`
`TIME`	Només hora	`18:30:00`
`TIMESTAMP`	Data i hora	`2026-07-14 18:30:00`
`TIMESTAMP WITH TIME ZONE`	Data, hora i zona horària	`2026-07-14 18:30:00+02`

El format universal és ISO 8601: AAAA-MM-DD. Fes-lo servir sempre i evitaràs l'eterna ambigüitat entre 03/04/2026 (3 d'abril o 4 de març?).

A BiblioRed, les dates de préstec i devolució són DATE: a la biblioteca li importa el dia, no l'hora exacta. Si en el futur es volguessin calcular recàrrecs per hores, caldria migrar a TIMESTAMP.

SQLite no té tipus de data: guarda les dates com a text '2026-07-14', com a nombre de dies julians o com a enter Unix. Declarar DATE és legal i serveix com a documentació, però SQLite no validarà que el contingut sigui una data real. És una diferència de fons: PostgreSQL és de tipatge estricte i SQLite, d'afinitat de tipus.

Booleans

BOOLEAN admet TRUE, FALSE i NULL. A BiblioRed, socis.actiu indica si el carnet continua vigent.

SQLite no té BOOLEAN: fa servir enters 0 i 1. Accepta la paraula BOOLEAN al CREATE TABLE i reconeix TRUE/FALSE des de la versió 3.23, emmagatzemant-los com a 1 i 0.

`CREATE TABLE` i les restriccions bàsiques

La instrucció que crea una relació:

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

Les quatre restriccions que farem servir avui:

Restricció	Què garanteix	Regla d'integritat implicada
`PRIMARY KEY`	Valor únic i no nul; identifica la fila	Integritat d'entitat
`NOT NULL`	La columna no queda mai buida	Integritat de domini
`UNIQUE`	No hi ha dues files amb el mateix valor	Clau alternativa
`REFERENCES altra_taula(col)`	El valor existeix a la taula referenciada	Integritat referencial

N'hi ha dues més, CHECK i DEFAULT, que s'estudien amb deteniment a 04-04; avui no les fem servir per no avançar decisions de disseny.

Exemple comentat amb la taula més senzilla de BiblioRed:

CREATE TABLE sucursals (
    sucursal_id     INTEGER      PRIMARY KEY,  -- clau primària: única i NOT NULL implícit
    nom             VARCHAR(60)  NOT NULL UNIQUE, -- obligatori i sense repetir
    adreca          VARCHAR(120) NOT NULL,
    telefon         VARCHAR(20),               -- admet NULL: pot no conèixer-se
    data_obertura   DATE
);

Punt important: telefon és VARCHAR, no un número. Els telèfons no se sumen ni es promitgen, poden començar per zero i porten espais o el prefix +34. La regla general és: si no hi faràs aritmètica, no és un número. El mateix s'aplica a l'ISBN i als codis postals.

Restriccions de columna davant de restriccions de taula

Les restriccions es poden escriure al costat de la columna o al final, com a element independent. Les dues formes següents són equivalents:

-- Forma de columna (compacta)
CREATE TABLE exemple_a (
    llibre_id INTEGER NOT NULL REFERENCES llibres(llibre_id)
);

-- Forma de taula, amb nom propi per a la restricció
CREATE TABLE exemple_b (
    llibre_id INTEGER NOT NULL,
    CONSTRAINT fk_exemple_llibre FOREIGN KEY (llibre_id) REFERENCES llibres(llibre_id)
);

La forma de taula és obligatòria quan la restricció afecta diverses columnes (una clau primària composta, per exemple) i recomanable quan vols donar-li un nom llegible. Per què importa el nom? Perquè quan la restricció es violi, el missatge d'error l'esmentarà:

ERROR:  insert or update on table "exemplars" violates foreign key constraint "fk_exemplars_llibre"

Un nom autoexplicatiu converteix un error críptic en un diagnòstic immediat. La convenció que seguirem: fk_<taula>_<referencia>, uq_<taula>_<columna>, pk_<taula>.

Claus autoincrementals a PostgreSQL i a SQLite

A la lliçó anterior vam decidir fer servir claus primàries subrogades. Algú ha de generar aquests números, i no volem fer-ho a mà. Cada gestor ho resol a la seva manera.

PostgreSQL

-- Forma moderna, estàndard SQL:2003. És la recomanada.
CREATE TABLE sucursals (
    sucursal_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom         VARCHAR(60) NOT NULL UNIQUE
);

-- Forma clàssica de PostgreSQL, equivalent i encara molt vista.
CREATE TABLE sucursals (
    sucursal_id SERIAL PRIMARY KEY,
    nom         VARCHAR(60) NOT NULL UNIQUE
);

Diferències entre totes dues:

Aspecte	`SERIAL`	`GENERATED ... AS IDENTITY`
Origen	Extensió pròpia de PostgreSQL	Estàndard SQL
Mecanisme	Crea una seqüència i li posa un `DEFAULT`	Seqüència gestionada internament
Permet inserir un valor manual?	Sí, sempre	`ALWAYS`: no, llevat d'`OVERRIDING SYSTEM VALUE`. `BY DEFAULT`: sí
En esborrar la taula	La seqüència queda lligada i s'esborra	S'esborra amb la taula

Per a BiblioRed farem servir GENERATED BY DEFAULT AS IDENTITY: és estàndard i, a més, ens deixa inserir identificadors explícits, que és just el que necessitarem a la lliçó següent per carregar el joc de dades amb els soci_id i llibre_id que ja tenim fixats (14, 15, 16, 331…).

SQLite

CREATE TABLE sucursals (
    sucursal_id INTEGER PRIMARY KEY,   -- s'autoincrementa tot sol
    nom         TEXT NOT NULL UNIQUE
);

A SQLite, una columna declarada exactament com INTEGER PRIMARY KEY és un àlies del rowid intern i s'autoincrementa automàticament si no li dónes valor. La paraula AUTOINCREMENT és opcional i només afegeix la garantia que un identificador esborrat no es reutilitzi mai, a canvi d'una taula interna addicional. La documentació oficial de SQLite recomana no fer-la servir llevat que aquesta garantia calgui.

Compte amb el detall: ha de ser INTEGER, en majúscules o minúscules però aquesta paraula exacta. INT PRIMARY KEY no activa el comportament.

Taula resum

	PostgreSQL	SQLite
Recomanat	`INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY`	`INTEGER PRIMARY KEY`
Alternativa	`SERIAL PRIMARY KEY`	`INTEGER PRIMARY KEY AUTOINCREMENT`
Comença a	1	1 (o el màxim existent + 1)

Lliurable: l'esquema complet de BiblioRed

Ha arribat el moment. Aquest és l'script que converteix el plànol de la lliçó 02-01 en taules reals.

L'ordre importa

Una taula no pot referenciar-ne una altra que encara no existeix. Cal crear-les seguint les dependències:

flowchart TD
    A["1. sucursals<br/><i>sense dependències</i>"] --> B["2. socis<br/><i>→ sucursals</i>"]
    C["1. autors<br/><i>sense dependències</i>"] --> D["3. llibres<br/><i>→ autors</i>"]
    A --> E["4. exemplars<br/><i>→ llibres, sucursals</i>"]
    D --> E
    B --> F["5. prestecs<br/><i>→ socis, exemplars</i>"]
    E --> F
    B --> G["6. reserves<br/><i>→ socis, llibres</i>"]
    D --> G

Un ordre vàlid: sucursals, autors, socis, llibres, exemplars, prestecs, reserves.

L'script (PostgreSQL)

Connecta't primer a la base de dades que vas crear a la lliçó 01-04:

$ psql -U alumne -d biblioredb
biblioredb=>

I executa:

-- ============================================================
--  BiblioRed - Esquema relacional
--  Mòdul 2, lliçó 02-02. Dialecte: PostgreSQL
-- ============================================================

-- 1. SUCURSALS: les quatre biblioteques de la xarxa.
--    Sense claus foranes: és l'arrel de l'esquema.
CREATE TABLE sucursals (
    sucursal_id    INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    nom            VARCHAR(60)  NOT NULL,
    adreca         VARCHAR(120) NOT NULL,
    telefon        VARCHAR(20),
    data_obertura  DATE,
    CONSTRAINT pk_sucursals PRIMARY KEY (sucursal_id),
    CONSTRAINT uq_sucursals_nom UNIQUE (nom)
);

-- 2. AUTORS: catàleg d'autors. Tampoc no depèn de ningú.
--    any_naixement és SMALLINT: un any hi cap de sobres.
CREATE TABLE autors (
    autor_id       INTEGER     GENERATED BY DEFAULT AS IDENTITY,
    nom            VARCHAR(60) NOT NULL,
    cognoms        VARCHAR(80) NOT NULL,
    nacionalitat   VARCHAR(40),
    any_naixement  SMALLINT,
    CONSTRAINT pk_autors PRIMARY KEY (autor_id)
);

-- 3. SOCIS: les persones amb carnet.
--    email és UNIQUE però admet NULL: no tothom dóna correu,
--    i en SQL diversos NULL no es consideren duplicats entre si.
--    sucursal_id és NOT NULL: tot soci es dóna d'alta en una sucursal.
CREATE TABLE socis (
    soci_id      INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    nom          VARCHAR(60)  NOT NULL,
    cognoms      VARCHAR(80)  NOT NULL,
    email        VARCHAR(120),
    data_alta    DATE         NOT NULL,
    sucursal_id  INTEGER      NOT NULL,
    actiu        BOOLEAN      NOT NULL,
    CONSTRAINT pk_socis PRIMARY KEY (soci_id),
    CONSTRAINT uq_socis_email UNIQUE (email),
    CONSTRAINT fk_socis_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
);

-- 4. LLIBRES: l'OBRA, no l'objecte físic.
--    isbn: clau alternativa (UNIQUE) i no clau primària, perquè
--    no tot el fons té ISBN. VARCHAR, no número: no s'hi opera.
--    autor_id admet NULL: hi ha obres anònimes o d'autoria no catalogada.
CREATE TABLE llibres (
    llibre_id       INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    isbn            VARCHAR(13),
    titol           VARCHAR(200) NOT NULL,
    autor_id        INTEGER,
    editorial       VARCHAR(80),
    any_publicacio  SMALLINT,
    idioma          VARCHAR(20),
    CONSTRAINT pk_llibres PRIMARY KEY (llibre_id),
    CONSTRAINT uq_llibres_isbn UNIQUE (isbn),
    CONSTRAINT fk_llibres_autor
        FOREIGN KEY (autor_id) REFERENCES autors (autor_id)
);

-- 5. EXEMPLARS: l'objecte físic que es presta.
--    codi és l'etiqueta enganxada al llom ('EJ-3081'): clau alternativa.
--    estat: 'disponible', 'prestat', 'reparacio', 'baixa'.
--    (La restricció CHECK que ho garanteix s'afegeix a la lliçó 04-04.)
CREATE TABLE exemplars (
    exemplar_id      INTEGER     GENERATED BY DEFAULT AS IDENTITY,
    codi             VARCHAR(10) NOT NULL,
    llibre_id        INTEGER     NOT NULL,
    sucursal_id      INTEGER     NOT NULL,
    estat            VARCHAR(20) NOT NULL,
    data_adquisicio  DATE,
    CONSTRAINT pk_exemplars PRIMARY KEY (exemplar_id),
    CONSTRAINT uq_exemplars_codi UNIQUE (codi),
    CONSTRAINT fk_exemplars_llibre
        FOREIGN KEY (llibre_id) REFERENCES llibres (llibre_id),
    CONSTRAINT fk_exemplars_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
);

-- 6. PRESTECS: uneix un soci amb un EXEMPLAR concret.
--    data_devolucio NULL = préstec encara obert.
--    recarrec NUMERIC(6,2): són diners, mai coma flotant.
CREATE TABLE prestecs (
    prestec_id               INTEGER GENERATED BY DEFAULT AS IDENTITY,
    soci_id                  INTEGER NOT NULL,
    exemplar_id              INTEGER NOT NULL,
    data_prestec             DATE    NOT NULL,
    data_devolucio_prevista  DATE    NOT NULL,
    data_devolucio           DATE,
    recarrec                 NUMERIC(6,2),
    CONSTRAINT pk_prestecs PRIMARY KEY (prestec_id),
    CONSTRAINT fk_prestecs_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id),
    CONSTRAINT fk_prestecs_exemplar
        FOREIGN KEY (exemplar_id) REFERENCES exemplars (exemplar_id)
);

-- 7. RESERVES: uneix un soci amb un LLIBRE (l'obra), no amb un exemplar:
--    el soci reserva el títol i se li assigna la primera còpia que s'alliberi.
--    estat: 'activa', 'atesa', 'cancellada'.
CREATE TABLE reserves (
    reserva_id       INTEGER     GENERATED BY DEFAULT AS IDENTITY,
    soci_id          INTEGER     NOT NULL,
    llibre_id        INTEGER     NOT NULL,
    data_reserva     DATE        NOT NULL,
    data_expiracio   DATE,
    estat            VARCHAR(20) NOT NULL,
    CONSTRAINT pk_reserves PRIMARY KEY (reserva_id),
    CONSTRAINT fk_reserves_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id),
    CONSTRAINT fk_reserves_llibre
        FOREIGN KEY (llibre_id) REFERENCES llibres (llibre_id)
);

Sortida esperada, una línia per instrucció:

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

Comprovació

biblioredb=> \dt

              List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+---------
 public | autors     | table | alumne
 public | exemplars  | table | alumne
 public | llibres    | table | alumne
 public | prestecs   | table | alumne
 public | reserves   | table | alumne
 public | socis      | table | alumne
 public | sucursals  | table | alumne
(7 rows)

I el detall d'una taula concreta:

biblioredb=> \d exemplars

                              Table "public.exemplars"
      Column       |         Type          | Nullable |             Default
-------------------+-----------------------+----------+----------------------------------
 exemplar_id       | integer               | not null | generated by default as identity
 codi              | character varying(10) | not null |
 llibre_id         | integer               | not null |
 sucursal_id       | integer               | not null |
 estat             | character varying(20) | not null |
 data_adquisicio   | date                  |          |
Indexes:
    "pk_exemplars" PRIMARY KEY, btree (exemplar_id)
    "uq_exemplars_codi" UNIQUE CONSTRAINT, btree (codi)
Foreign-key constraints:
    "fk_exemplars_llibre" FOREIGN KEY (llibre_id) REFERENCES llibres(llibre_id)
    "fk_exemplars_sucursal" FOREIGN KEY (sucursal_id) REFERENCES sucursals(sucursal_id)

Si veus això, l'esquema està dret.

La versió SQLite

El mateix script, amb tres canvis: la clau autoincremental, TEXT en lloc de VARCHAR (SQLite ho tracta igual, però és la seva convenció) i una línia imprescindible al principi.

-- OBLIGATÒRIA! SQLite ignora les claus foranes si no s'activen,
-- i cal fer-ho a CADA sessió. Sense això, l'esquema es crea igualment
-- però no protegeix res: podràs inserir préstecs de socis inexistents.
PRAGMA foreign_keys = ON;

CREATE TABLE sucursals (
    sucursal_id   INTEGER PRIMARY KEY,
    nom           TEXT    NOT NULL UNIQUE,
    adreca        TEXT    NOT NULL,
    telefon       TEXT,
    data_obertura TEXT            -- SQLite no té tipus DATE
);

CREATE TABLE autors (
    autor_id      INTEGER PRIMARY KEY,
    nom           TEXT    NOT NULL,
    cognoms       TEXT    NOT NULL,
    nacionalitat  TEXT,
    any_naixement INTEGER
);

CREATE TABLE socis (
    soci_id     INTEGER PRIMARY KEY,
    nom         TEXT    NOT NULL,
    cognoms     TEXT    NOT NULL,
    email       TEXT    UNIQUE,
    data_alta   TEXT    NOT NULL,
    sucursal_id INTEGER NOT NULL REFERENCES sucursals(sucursal_id),
    actiu       INTEGER NOT NULL          -- 0 / 1: SQLite no té BOOLEAN
);

CREATE TABLE llibres (
    llibre_id      INTEGER PRIMARY KEY,
    isbn           TEXT UNIQUE,
    titol          TEXT NOT NULL,
    autor_id       INTEGER REFERENCES autors(autor_id),
    editorial      TEXT,
    any_publicacio INTEGER,
    idioma         TEXT
);

CREATE TABLE exemplars (
    exemplar_id     INTEGER PRIMARY KEY,
    codi            TEXT    NOT NULL UNIQUE,
    llibre_id       INTEGER NOT NULL REFERENCES llibres(llibre_id),
    sucursal_id     INTEGER NOT NULL REFERENCES sucursals(sucursal_id),
    estat           TEXT    NOT NULL,
    data_adquisicio TEXT
);

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

CREATE TABLE reserves (
    reserva_id     INTEGER PRIMARY KEY,
    soci_id        INTEGER NOT NULL REFERENCES socis(soci_id),
    llibre_id      INTEGER NOT NULL REFERENCES llibres(llibre_id),
    data_reserva   TEXT    NOT NULL,
    data_expiracio TEXT,
    estat          TEXT    NOT NULL
);

Comprovació:

sqlite> .tables
autors     exemplars  llibres    prestecs   reserves   socis      sucursals
sqlite> PRAGMA foreign_keys;
1

El PRAGMA foreign_keys = ON és tan important que la lliçó 02-06 hi torna.

`ALTER TABLE`: modificar el que ja està creat

Els esquemes canvien. ALTER TABLE modifica l'estructura sense perdre les dades.

Afegir una columna

BiblioRed vol registrar el telèfon mòbil dels socis:

ALTER TABLE socis ADD COLUMN telefon VARCHAR(20);

ALTER TABLE

La columna s'afegeix al final i totes les files existents queden amb NULL. Per això, afegir una columna NOT NULL a una taula amb dades falla: no hi ha valor que posar a les files que ja hi són.

ALTER TABLE socis ADD COLUMN dni VARCHAR(9) NOT NULL;

ERROR:  column "dni" of relation "socis" contains null values

La solució habitual és en tres passos: afegir-la nullable, omplir-la amb un UPDATE (lliçó 02-03) i després imposar el NOT NULL.

Reanomenar una columna o una taula

ALTER TABLE socis RENAME COLUMN telefon TO telefon_mobil;
ALTER TABLE socis RENAME TO usuaris;   -- reanomenar la taula sencera
ALTER TABLE usuaris RENAME TO socis;   -- ho deixem com estava

Canviar el tipus d'una columna

-- PostgreSQL: la sintaxi és ALTER COLUMN ... TYPE
ALTER TABLE llibres ALTER COLUMN editorial TYPE VARCHAR(120);

Ampliar la mida és segur. Reduir-la o canviar de família de tipus pot fallar si les dades existents no hi caben o no es poden convertir; en aquest cas PostgreSQL avorta i no toca res.

Afegir o treure restriccions

ALTER TABLE socis ALTER COLUMN email SET NOT NULL;
ALTER TABLE socis ALTER COLUMN email DROP NOT NULL;

ALTER TABLE llibres ADD CONSTRAINT uq_llibres_isbn UNIQUE (isbn);
ALTER TABLE llibres DROP CONSTRAINT uq_llibres_isbn;

Aquí es veu el rendiment pràctic d'haver posat nom a les restriccions: per eliminar-ne una cal anomenar-la, i uq_llibres_isbn és força més manejable que el nom automàtic llibres_isbn_key.

Eliminar una columna

ALTER TABLE socis DROP COLUMN telefon_mobil;

Això esborra les dades d'aquesta columna i no hi ha desfer (fora d'una transacció). Pensa-t'ho dues vegades.

La limitació de SQLite

SQLite només admet un subconjunt d'ALTER TABLE:

Operació	PostgreSQL	SQLite
`ADD COLUMN`	Sí	Sí
`RENAME TO` (taula)	Sí	Sí
`RENAME COLUMN`	Sí	Sí (des de 3.25)
`DROP COLUMN`	Sí	Sí (des de 3.35, amb restriccions)
`ALTER COLUMN ... TYPE`	Sí	No
`ADD CONSTRAINT`	Sí	No

El procediment oficial a SQLite per al que no admet és: crear una taula nova amb l'estructura correcta, copiar-hi les dades amb INSERT INTO ... SELECT, esborrar la vella i reanomenar la nova. És tediós, i és un altre argument a favor de PostgreSQL per a un esquema que ha d'evolucionar.

`DROP TABLE` i l'ordre de destrucció

DROP TABLE reserves;

Elimina la taula i totes les seves dades, de manera immediata i sense confirmació. A PostgreSQL, si ho executes dins d'una transacció encara pots fer ROLLBACK (lliçó 06-01); a SQLite i a la majoria dels gestors, no.

Dues variants útils:

-- No falla si la taula no existeix: imprescindible en scripts reexecutables
DROP TABLE IF EXISTS reserves;

-- Elimina també els objectes que en depenen (perillós!)
DROP TABLE llibres CASCADE;

L'ordre invers al de creació

Si intentes esborrar una taula a la qual n'apunten d'altres, el gestor t'ho impedeix:

DROP TABLE llibres;

ERROR:  cannot drop table llibres because other objects depend on it
DETAIL:  constraint fk_exemplars_llibre on table exemplars depends on table llibres

Això és la integritat referencial funcionant exactament com ha de fer-ho. Per buidar l'esquema i tornar a començar, cal anar en ordre invers al de creació: primer les filles, després les pares.

DROP TABLE IF EXISTS reserves;
DROP TABLE IF EXISTS prestecs;
DROP TABLE IF EXISTS exemplars;
DROP TABLE IF EXISTS llibres;
DROP TABLE IF EXISTS socis;
DROP TABLE IF EXISTS autors;
DROP TABLE IF EXISTS sucursals;

Guarda aquestes set línies al principi del teu script de creació, comentades. Quan vulguis refer l'esquema des de zero —i ho voldràs unes quantes vegades durant el curs— les descomentes i executes el fitxer sencer.

Errors Habituals i Consells

Fer servir cometes dobles per a valors de text. WHERE estat = "disponible" fa que PostgreSQL busqui una columna anomenada disponible. Els valors van entre cometes simples, sempre.
Oblidar el punt i coma. Si psql mostra biblioredb-# en comptes de biblioredb=#, la instrucció continua oberta. Escriu ; i prem Retorn.
Anomenar columnes amb majúscules, accents o ç. "DataAlta" o "Adreça" et condemnen a posar cometes a cada consulta que escriguis durant la resta de la vida de la base de dades. data_alta, adreca.
Crear les taules en ordre equivocat. REFERENCES llibres falla si llibres encara no existeix. Segueix l'arbre de dependències.
Fer servir FLOAT o REAL per als diners. Sumes que no quadren, cèntims que s'evaporen, tancaments comptables impossibles. NUMERIC(p, s).
Guardar telèfons, ISBN o codis postals com a números. Perds els zeros inicials, els prefixos i els guions. Si no s'hi opera aritmèticament, és text.
Oblidar PRAGMA foreign_keys = ON a SQLite. L'esquema es crea amb aspecte correcte però no valida res, i descobreixes les files òrfenes mesos després. Cal executar-lo a cada sessió.
Afegir una columna NOT NULL a una taula amb dades. Falla sempre. Afegeix-la nullable, omple-la i després imposa la restricció.
Consell: guarda l'script de l'esquema en un fitxer (esquema_biblioredb.sql) i executa'l amb psql -U alumne -d biblioredb -f esquema_biblioredb.sql o amb .read esquema_biblioredb.sql a SQLite. Un esquema que només existeix a l'historial de la consola és un esquema perdut.
Consell: posa nom a totes les restriccions. El dia que salti un error, el nom serà la meitat del diagnòstic.

Exercicis

Exercici 1: Classificar instruccions per subllenguatge

Indica a quin subllenguatge pertany cada instrucció i en quina lliçó del curs es tracta:

SELECT titol FROM llibres;
ALTER TABLE socis ADD COLUMN telefon VARCHAR(20);
UPDATE exemplars SET estat = 'disponible' WHERE exemplar_id = 1;
GRANT SELECT ON llibres TO taulell;
ROLLBACK;
DROP TABLE reserves;
INSERT INTO autors (nom, cognoms) VALUES ('Marina', 'Escolà');

Exercici 2: Detectar i corregir errors en un `CREATE TABLE`

BiblioRed vol una taula nova, multes, per a les sancions acumulades de cada soci. Un company ha escrit això:

CREATE TABLE Multes (
    ID           INT,
    Soci         INT NOT NULL REFERENCES socis(soci_id),
    Import       FLOAT NOT NULL,
    Data         VARCHAR(10) NOT NULL,
    Motiu        VARCHAR(200),
    Pagada       VARCHAR(2) NOT NULL,
    "Nº Avis"    INT
)

Troba almenys sis problemes i reescriu la taula correctament per a PostgreSQL, seguint les convencions del curs.

Exercici 3: Fer evolucionar l'esquema

Escriu les instruccions ALTER TABLE necessàries per a cada canvi que demana la direcció de BiblioRed. Indica també, quan correspongui, si l'operació pot fallar sobre una taula que ja té dades i com evitar-ho.

Avís: aquest exercici és de redacció, no d'execució. Si decideixes provar-lo, fes-ho sobre una base de dades a part, o desfés després els canvis: la resta del mòdul assumeix l'esquema tal com va quedar a l'apartat 8, amb la columna llibres.idioma inclosa.

Afegir a sucursals una columna email_contacte de fins a 120 caràcters, opcional.
Afegir a llibres una columna num_pagines entera, opcional.
La columna llibres.editorial es queda curta: ampliar-la a 150 caràcters.
sucursals.data_obertura ha de passar a ser obligatòria.
Reanomenar reserves.data_expiracio a data_caducitat.
Eliminar de llibres la columna idioma, que no fa servir ningú.

Solucions

Solució 1

#	Instrucció	Subllenguatge	Lliçó
1	`SELECT`	DQL (o DML a la classificació de quatre grups)	02-03
2	`ALTER TABLE`	DDL	02-02 (aquesta)
3	`UPDATE`	DML	02-03
4	`GRANT`	DCL	06-04
5	`ROLLBACK`	TCL	06-01
6	`DROP TABLE`	DDL	02-02 (aquesta)
7	`INSERT`	DML	02-03

Solució 2

Problemes trobats:

Multes, ID, Soci… amb majúscules barrejades. PostgreSQL les passa a minúscules, així que "funciona", però trenca la convenció de la resta de l'esquema. Noms en minuscules_amb_guio_baix.
ID sense PRIMARY KEY. La taula no tindria clau primària: es violaria la integritat d'entitat i s'hi podrien inserir files duplicades indistingibles.
ID sense autoincrement. Caldria inventar-se el número a mà a cada inserció.
Import FLOAT. Són diners: ha de ser NUMERIC(6,2).
Data VARCHAR(10). És una data: ha de ser DATE. Com a text, no es podria ordenar de manera fiable, ni restar, ni validar.
Pagada VARCHAR(2). És un sí/no: ha de ser BOOLEAN. Amb VARCHAR(2) acabarien convivint 'S', 'si', 'SI', '1' i 'no'.
"Nº Avis" entre cometes dobles, amb espai i amb º. Condemna a posar cometes sempre i no és URL-safe ni portable. num_avis.
Nom de columna Soci poc descriptiu: la convenció de l'esquema és soci_id.
Restriccions sense nom i falta el punt i coma final.

Versió corregida:

CREATE TABLE multes (
    multa_id  INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    soci_id   INTEGER      NOT NULL,
    import    NUMERIC(6,2) NOT NULL,
    data      DATE         NOT NULL,
    motiu     VARCHAR(200),
    pagada    BOOLEAN      NOT NULL,
    num_avis  INTEGER,
    CONSTRAINT pk_multes PRIMARY KEY (multa_id),
    CONSTRAINT fk_multes_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
);

Solució 3

-- 1. Columna opcional: no hi ha risc, les files existents queden a NULL.
ALTER TABLE sucursals ADD COLUMN email_contacte VARCHAR(120);

-- 2. Igual: opcional, sense risc.
ALTER TABLE llibres ADD COLUMN num_pagines INTEGER;

-- 3. Ampliar un VARCHAR és segur: tot el que cabia en 80 cap en 150.
--    (Reduir-lo sí que podria fallar si algun valor excedís el nou límit.)
ALTER TABLE llibres ALTER COLUMN editorial TYPE VARCHAR(150);

-- 4. POT FALLAR: si alguna sucursal té data_obertura a NULL, PostgreSQL
--    avorta. Cal omplir-la primer i després imposar la restricció.
UPDATE sucursals SET data_obertura = '2000-01-01' WHERE data_obertura IS NULL;
ALTER TABLE sucursals ALTER COLUMN data_obertura SET NOT NULL;

-- 5. Reanomenar no toca les dades, però SÍ que trenca les consultes i aplicacions
--    que fessin servir el nom antic. Cal revisar-les.
ALTER TABLE reserves RENAME COLUMN data_expiracio TO data_caducitat;

-- 6. Destructiu i irreversible fora d'una transacció: es perden les dades.
ALTER TABLE llibres DROP COLUMN idioma;

Nota sobre SQLite: els punts 3 i 4 no es poden fer amb ALTER TABLE; caldria recrear la taula, copiar les dades i reanomenar.

Conclusió

Aquesta lliçó ha convertit el plànol en un edifici. Repassem:

SQL és declaratiu: descrius el resultat i l'optimitzador —el de la lliçó 01-04— decideix el camí. Això fa que escriguis menys i que el gestor pugui millorar el rendiment sense que toquis la consulta.
Hi ha un estàndard i hi ha dialectes: un nucli comú molt ampli i una perifèria pròpia de cada motor. PostgreSQL és la nostra referència; SQLite, l'alternativa lleugera.
Els cinc subllenguatges: DDL (estructura), DML (dades), DQL (consultes), DCL (permisos) i TCL (transaccions).
Les regles d'escriptura: paraules clau en majúscules, identificadors en minuscules_amb_guio_baix i sense cometes dobles, cometes simples per als literals de text, punt i coma al final i comentaris amb -- o /* */.
Els tipus que ens calien: INTEGER/SMALLINT per als enters, NUMERIC(p, s) per als diners (mai coma flotant), VARCHAR(n)/TEXT per al text, DATE/TIMESTAMP per a dates en format ISO i BOOLEAN per al sí/no, amb les particularitats de SQLite en cada cas.
CREATE TABLE amb PRIMARY KEY, NOT NULL, UNIQUE i REFERENCES, en forma de columna o de taula amb nom propi; les claus autoincrementals (GENERATED ... AS IDENTITY o SERIAL a PostgreSQL, INTEGER PRIMARY KEY a SQLite); ALTER TABLE per afegir, reanomenar, retipar i eliminar columnes; i DROP TABLE, que exigeix recórrer les dependències en ordre invers.
I, sobretot, l'esquema complet de BiblioRed: sucursals, autors, socis, llibres, exemplars, prestecs i reserves, creat i verificat amb \dt.

Les set taules existeixen i són buides. A la lliçó 02-03, Operacions Bàsiques en SQL, les omplim: veuràs INSERT, SELECT amb WHERE, ORDER BY i LIMIT, UPDATE i DELETE, tot sobre una sola taula. El lliurable d'aquella lliçó serà el joc de dades de prova de BiblioRed —les quatre sucursals, els socis, els llibres, els exemplars i els préstecs ficticis— que farem servir fins al final del mòdul. No esborris les teves taules: a partir d'ara, cada lliçó construeix sobre l'anterior.

Llenguatge SQL