El Proyecto | Sobre nosotros | Contribuir | Donaciones | Licencia

HOME

En la lección anterior dibujamos el plano de BiblioRed: siete relaciones, sus claves y sus vínculos. Hoy aprendemos el idioma con el que se le explica ese plano a un sistema gestor, y lo usamos para levantarlo de verdad dentro de biblioredb.

SQL es, con diferencia, el lenguaje de propósito específico más longevo y más rentable de aprender de la informática: nació en los años setenta, sobrevivió a todas las modas y hoy se habla no solo con PostgreSQL, MySQL, SQLite u Oracle, sino también con motores analíticos, con almacenes en la nube y hasta con capas de consulta sobre bases NoSQL. Lo que aprendas aquí te servirá en todos ellos.

Esta lección es la bisagra del curso: empieza explicando el lenguaje y termina con un entregable concreto, el script CREATE TABLE completo de las siete tablas de BiblioRed, que debes ejecutar antes de pasar a la lección siguiente. Ten psql o sqlite3 abierto mientras la lees.

Contenido

Qué es SQL y por qué es declarativo
El estándar y por qué existen dialectos
Los cinco sublenguajes: DDL, DML, DQL, DCL y TCL
Reglas de escritura: identificadores, mayúsculas, comillas y comentarios
Tipos de datos que necesitamos ahora
CREATE TABLE y las restricciones básicas
Claves autoincrementales en PostgreSQL y en SQLite
Entregable: el esquema completo de BiblioRed
ALTER TABLE: modificar lo ya creado
DROP TABLE y el orden de destrucción
Errores comunes y consejos
Ejercicios
Conclusión

Qué es SQL y por qué es declarativo

SQL (Structured Query Language) es el lenguaje estándar para definir, manipular y consultar bases de datos relacionales. Su rasgo más característico es que es declarativo: describes qué resultado quieres, no cómo obtenerlo.

Compara. Así se resolvería "los préstamos abiertos de la sucursal Norte" en un lenguaje imperativo como Python, trabajando con ficheros:

abrir el fichero de préstamos
para cada línea:
    si fecha_devolucion está vacía:
        buscar el ejemplar correspondiente en el fichero de ejemplares
        si su sucursal es 2:
            añadir la línea al resultado
cerrar el fichero

Has tenido que decidir el orden de los bucles, qué fichero se recorre primero y cómo se busca. Ahora en SQL:

SELECT p.prestamo_id, p.fecha_prestamo
FROM prestamos p
JOIN ejemplares e ON e.ejemplar_id = p.ejemplar_id
WHERE p.fecha_devolucion IS NULL
  AND e.sucursal_id = 2;

No hay bucles, ni orden de recorrido, ni estructuras de datos. Solo la descripción del resultado. El optimizador de consultas —el componente que estudiamos en la lección 01-04— es quien decide si recorre primero prestamos o ejemplares, si usa un índice o lee la tabla entera, y con qué algoritmo empareja las filas. Y esa decisión la vuelve a tomar cada vez, con las estadísticas del momento: la misma consulta que hoy se resuelve de una manera puede resolverse mañana de otra, más rápida, sin que tú cambies una letra.

Las tres consecuencias prácticas de esto:

Escribes menos y más claro. Una consulta de cinco líneas sustituye a cincuenta de código imperativo.
No optimizas a mano lo que el gestor optimiza mejor. Reordenar las tablas en el FROM para "ir más rápido" es, en general, tiempo perdido: el optimizador reordena por su cuenta.
Cuando algo va lento, se diagnostica mirando el plan, no el SQL. Es lo que haremos con EXPLAIN en la lección 06-03.

SQL no es puramente declarativo ni puramente relacional (ya vimos que admite duplicados), pero esa mezcla pragmática es justamente lo que lo hizo triunfar.

El estándar y por qué existen dialectos

SQL está estandarizado por ISO y ANSI desde 1986, con revisiones sucesivas (SQL-92, SQL:1999, SQL:2003, SQL:2011, SQL:2023) que vimos en la lección 01-03. Sin embargo, ningún gestor implementa el estándar exactamente, y todos añaden cosas propias. Los motivos:

El estándar llega tarde: los fabricantes inventan una funcionalidad útil, se populariza y años después se estandariza con otra sintaxis.
El estándar deja zonas opcionales o sin definir (tipos de fecha, autoincremento, límites de resultados), y cada fabricante rellena el hueco a su manera.
Cada motor tiene capacidades propias que el estándar no contempla.

Resultado: un núcleo común amplio —SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, JOIN, GROUP BY— que funciona en todas partes, y una periferia específica de cada dialecto.

Necesidad	Estándar / PostgreSQL	SQLite	MySQL
Clave autoincremental	`GENERATED ALWAYS AS IDENTITY` / `SERIAL`	`INTEGER PRIMARY KEY AUTOINCREMENT`	`AUTO_INCREMENT`
Concatenar texto	`'a' \|\| 'b'`	`'a' \|\| 'b'`	`CONCAT('a','b')`
Limitar filas	`LIMIT 10` (`FETCH FIRST 10 ROWS ONLY` es el estándar)	`LIMIT 10`	`LIMIT 10`
Fecha actual	`CURRENT_DATE`	`DATE('now')`	`CURDATE()`
Comparación de texto sin distinguir mayúsculas	`ILIKE`	`LIKE` (ya es insensible en ASCII)	`LIKE`

En este curso usamos PostgreSQL como referencia y señalamos las diferencias de SQLite cuando importan. Consejo permanente: escribe SQL lo más estándar que puedas y usa extensiones del dialecto solo cuando aporten algo real; tu SQL será más fácil de portar y, sobre todo, de entender por otra persona.

Los cinco sublenguajes: DDL, DML, DQL, DCL y TCL

Aunque se hable de "SQL" en singular, sus instrucciones se agrupan por función. Conocer los grupos te ayuda a orientarte y a saber en qué lección del curso se trata cada cosa.

Sublenguaje	Nombre completo	Para qué sirve	Instrucciones principales	Dónde se ve en el curso
DDL	Data Definition Language	Definir y modificar la estructura	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `RENAME`	Esta lección y 04-04
DML	Data Manipulation Language	Cambiar los datos	`INSERT`, `UPDATE`, `DELETE`, `MERGE`	02-03
DQL	Data Query Language	Consultar los datos	`SELECT`	02-03, 02-04, 02-05
DCL	Data Control Language	Gestionar permisos	`GRANT`, `REVOKE`	06-04
TCL	Transaction Control Language	Delimitar transacciones	`COMMIT`, `ROLLBACK`, `SAVEPOINT`	06-01

Dos matices que conviene tener claros desde el principio:

Mucha gente considera DQL parte de DML, y por eso a veces verás "cuatro sublenguajes". Da igual el recuento; lo que importa es la función.
En PostgreSQL, el DDL es transaccional: puedes crear tablas dentro de una transacción y deshacerlo con ROLLBACK. En muchos otros gestores (MySQL con InnoDB, Oracle) una instrucción DDL confirma implícitamente lo que hubiera pendiente. Es una diferencia con consecuencias reales al escribir scripts de migración.

Hoy trabajamos exclusivamente con DDL.

Reglas de escritura: identificadores, mayúsculas, comillas y comentarios

Palabras clave e identificadores

Las palabras clave (SELECT, FROM, CREATE TABLE) forman parte del lenguaje.
Los identificadores son los nombres que pones tú: tablas, columnas, restricciones, índices.

SQL no distingue mayúsculas de minúsculas en las palabras clave: select, SELECT y SeLeCt son equivalentes. La convención universal es escribir las palabras clave en MAYÚSCULAS y los identificadores en minúsculas, porque hace la consulta legible de un vistazo:

-- Legible
SELECT titulo, isbn FROM libros WHERE anio_publicacion > 2010;

-- Legal, pero cuesta leerlo
select TITULO, ISBN from LIBROS where ANIO_PUBLICACION > 2010;

La trampa de las mayúsculas en identificadores

PostgreSQL, siguiendo el estándar, convierte los identificadores sin comillas a minúsculas. Por tanto FechaAlta, fechaalta y FECHAALTA son la misma columna. Pero si escribes el identificador entre comillas dobles, se respeta literalmente y a partir de ahí siempre habrá que entrecomillarlo:

CREATE TABLE prueba ("FechaAlta" DATE);

SELECT FechaAlta FROM prueba;     -- ERROR: no existe la columna "fechaalta"
SELECT "FechaAlta" FROM prueba;   -- Correcto, pero condena a comillas para siempre

Consejo firme: no uses comillas dobles en los identificadores. Nombra todo en minusculas_con_guion_bajo, sin tildes, sin ñ y sin espacios. Es la convención de PostgreSQL y te ahorra una categoría entera de problemas. En este curso, todos los nombres de BiblioRed siguen esa regla.

Comillas simples frente a comillas dobles

Esta distinción es fuente constante de errores en quien viene de otros lenguajes:

Signo	Significado en SQL	Ejemplo
`'comilla simple'`	Literal de texto (un valor)	`WHERE estado = 'disponible'`
`"comilla doble"`	Identificador (un nombre)	`SELECT "FechaAlta"`

SELECT * FROM ejemplares WHERE estado = "disponible";
-- ERROR en PostgreSQL: no existe la columna «disponible»
-- PostgreSQL busca una COLUMNA llamada disponible, no un texto

SELECT * FROM ejemplares WHERE estado = 'disponible';   -- Correcto

SQLite es más permisivo y en algunos casos acepta comillas dobles como texto; no te acostumbres, porque ese SQL no funcionará en PostgreSQL.

Para incluir un apóstrofo dentro de un literal, se duplica:

SELECT 'L''Hospitalet';   -- devuelve: L'Hospitalet

Punto y coma

El ; termina una instrucción. En psql y en sqlite3 es obligatorio: sin él, el cliente entiende que la instrucción continúa en la línea siguiente y se queda esperando. Si ves un indicador como biblioredb-# en lugar de biblioredb=#, es exactamente eso: te falta el punto y coma.

Comentarios

-- Comentario de una línea: desde los dos guiones hasta el final

/* Comentario
   de varias líneas.
   Útil para documentar un script completo. */

SELECT titulo   -- también se puede comentar al final de una línea
FROM libros;

Estilo recomendado

Escribe consultas multilínea con una cláusula por línea. Cuesta lo mismo y se lee infinitamente mejor:

SELECT s.nombre,
       s.apellidos,
       s.email
FROM socios s
WHERE s.activo = TRUE
  AND s.sucursal_id = 2
ORDER BY s.apellidos;

Tipos de datos que necesitamos ahora

Cada columna tiene un tipo, y ese tipo es la implementación de la regla de integridad de dominio de la lección anterior. Aquí vemos solo lo necesario para crear las tablas de BiblioRed; el catálogo completo y los criterios finos de elección son la lección 04-04.

Números enteros

Tipo	Rango aproximado	Uso típico
`SMALLINT`	±32.000	Años, contadores pequeños
`INTEGER` (`INT`)	±2.100 millones	La opción por defecto para claves e identificadores
`BIGINT`	±9,2·10¹⁸	Tablas enormes, identificadores globales

En SQLite existe un único tipo entero, INTEGER, que admite hasta 8 bytes; los nombres SMALLINT o BIGINT se aceptan pero acaban siendo lo mismo.

Números decimales: la regla del dinero

Aquí hay una decisión que separa a los profesionales de los aficionados.

Tipo	Cómo almacena	Exactitud
`NUMERIC(p, s)` / `DECIMAL(p, s)`	Decimal, con `p` dígitos totales y `s` decimales	Exacta
`REAL`, `DOUBLE PRECISION`, `FLOAT`	Coma flotante binaria (IEEE 754)	Aproximada

La coma flotante no puede representar exactamente valores como 0,10 en binario, igual que en decimal no podemos escribir 1/3 con un número finito de cifras. Eso produce resultados como este:

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

En BiblioRed la columna recargo guarda euros. Con coma flotante, sumar diez mil recargos de 0,10 € puede dar 999,9998 en lugar de 1000,00, y la cuenta anual de la biblioteca no cuadrará jamás.

Regla: para dinero, siempre NUMERIC(p, s). NUMERIC(6, 2) admite hasta 9999.99, más que suficiente para un recargo de biblioteca. Reserva la coma flotante para magnitudes científicas donde la precisión aproximada es aceptable (temperaturas, coordenadas, medidas físicas).

Aviso de SQLite: no tiene tipo decimal exacto. NUMERIC(6,2) se acepta, pero internamente puede almacenarse como coma flotante. Para practicar da igual; en producción con dinero, es un argumento más a favor de PostgreSQL.

Texto

Tipo	Descripción
`VARCHAR(n)`	Cadena de longitud variable con máximo `n` caracteres
`CHAR(n)`	Longitud fija; rellena con espacios. Casi nunca es lo que quieres
`TEXT`	Cadena sin límite declarado

En PostgreSQL, VARCHAR(n) y TEXT tienen el mismo rendimiento: VARCHAR(n) no es más rápido, solo añade una comprobación de longitud. Por eso el criterio es semántico: usa VARCHAR(n) cuando el límite sea una regla real (un ISBN tiene 13 caracteres, ni uno más) y TEXT cuando no haya límite natural (una reseña, unas observaciones).

Fechas y horas

Tipo	Guarda	Ejemplo
`DATE`	Solo fecha	`2026-07-14`
`TIME`	Solo hora	`18:30:00`
`TIMESTAMP`	Fecha y hora	`2026-07-14 18:30:00`
`TIMESTAMP WITH TIME ZONE`	Fecha, hora y zona horaria	`2026-07-14 18:30:00+02`

El formato universal es ISO 8601: AAAA-MM-DD. Úsalo siempre y evitarás la eterna ambigüedad entre 03/04/2026 (¿3 de abril o 4 de marzo?).

En BiblioRed, las fechas de préstamo y devolución son DATE: a la biblioteca le importa el día, no la hora exacta. Si en el futuro se quisiera calcular recargos por horas, habría que migrar a TIMESTAMP.

SQLite no tiene tipo de fecha: guarda las fechas como texto '2026-07-14', como número de días julianos o como entero Unix. Declarar DATE es legal y sirve como documentación, pero SQLite no validará que el contenido sea una fecha real. Es una diferencia de fondo: PostgreSQL es de tipado estricto y SQLite, de afinidad de tipos.

Booleanos

BOOLEAN admite TRUE, FALSE y NULL. En BiblioRed, socios.activo indica si el carné sigue vigente.

SQLite no tiene BOOLEAN: usa enteros 0 y 1. Acepta la palabra BOOLEAN en el CREATE TABLE y reconoce TRUE/FALSE desde la versión 3.23, almacenándolos como 1 y 0.

`CREATE TABLE` y las restricciones básicas

La instrucción que crea una relación:

CREATE TABLE nombre_tabla (
    columna1  TIPO  [restricciones de columna],
    columna2  TIPO  [restricciones de columna],
    ...
    [restricciones de tabla]
);

Las cuatro restricciones que usaremos hoy:

Restricción	Qué garantiza	Regla de integridad implicada
`PRIMARY KEY`	Valor único y no nulo; identifica la fila	Integridad de entidad
`NOT NULL`	La columna nunca queda vacía	Integridad de dominio
`UNIQUE`	No hay dos filas con el mismo valor	Clave alternativa
`REFERENCES otra_tabla(col)`	El valor existe en la tabla referenciada	Integridad referencial

Existen dos más, CHECK y DEFAULT, que se estudian con detenimiento en 04-04; hoy no las usamos para no adelantar decisiones de diseño.

Ejemplo comentado con la tabla más sencilla de BiblioRed:

CREATE TABLE sucursales (
    sucursal_id     INTEGER      PRIMARY KEY,  -- clave primaria: única y NOT NULL implícito
    nombre          VARCHAR(60)  NOT NULL UNIQUE, -- obligatorio y sin repetir
    direccion       VARCHAR(120) NOT NULL,
    telefono        VARCHAR(20),               -- admite NULL: puede no conocerse
    fecha_apertura  DATE
);

Punto importante: telefono es VARCHAR, no un número. Los teléfonos no se suman ni se promedian, pueden empezar por cero y llevan espacios o el prefijo +34. La regla general es: si no vas a hacer aritmética con ello, no es un número. Lo mismo se aplica al ISBN y a los códigos postales.

Restricciones de columna frente a restricciones de tabla

Las restricciones se pueden escribir junto a la columna o al final, como elemento independiente. Las dos formas siguientes son equivalentes:

-- Forma de columna (compacta)
CREATE TABLE ejemplo_a (
    libro_id INTEGER NOT NULL REFERENCES libros(libro_id)
);

-- Forma de tabla, con nombre propio para la restricción
CREATE TABLE ejemplo_b (
    libro_id INTEGER NOT NULL,
    CONSTRAINT fk_ejemplo_libro FOREIGN KEY (libro_id) REFERENCES libros(libro_id)
);

La forma de tabla es obligatoria cuando la restricción afecta a varias columnas (una clave primaria compuesta, por ejemplo) y recomendable cuando quieres darle un nombre legible. ¿Por qué importa el nombre? Porque cuando la restricción se viole, el mensaje de error lo mencionará:

ERROR:  inserción o actualización en la tabla «ejemplares» viola la llave foránea «fk_ejemplares_libro»

Un nombre autoexplicativo convierte un error críptico en un diagnóstico inmediato. La convención que seguiremos: fk_<tabla>_<referencia>, uq_<tabla>_<columna>, pk_<tabla>.

Claves autoincrementales en PostgreSQL y en SQLite

En la lección anterior decidimos usar claves primarias subrogadas. Alguien tiene que generar esos números, y no queremos hacerlo a mano. Cada gestor lo resuelve a su manera.

PostgreSQL

-- Forma moderna, estándar SQL:2003. Es la recomendada.
CREATE TABLE sucursales (
    sucursal_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nombre      VARCHAR(60) NOT NULL UNIQUE
);

-- Forma clásica de PostgreSQL, equivalente y aún muy vista.
CREATE TABLE sucursales (
    sucursal_id SERIAL PRIMARY KEY,
    nombre      VARCHAR(60) NOT NULL UNIQUE
);

Diferencias entre ambas:

Aspecto	`SERIAL`	`GENERATED ... AS IDENTITY`
Origen	Extensión propia de PostgreSQL	Estándar SQL
Mecanismo	Crea una secuencia y le pone un `DEFAULT`	Secuencia gestionada internamente
¿Permite insertar un valor manual?	Sí, siempre	`ALWAYS`: no, salvo `OVERRIDING SYSTEM VALUE`. `BY DEFAULT`: sí
Al borrar la tabla	La secuencia queda ligada y se borra	Se borra con la tabla

Para BiblioRed usaremos GENERATED BY DEFAULT AS IDENTITY: es estándar y, además, nos deja insertar identificadores explícitos, que es justo lo que necesitaremos en la lección siguiente para cargar el juego de datos con los socio_id y libro_id que ya tenemos fijados (14, 15, 16, 331…).

SQLite

CREATE TABLE sucursales (
    sucursal_id INTEGER PRIMARY KEY,   -- se autoincrementa por sí solo
    nombre      TEXT NOT NULL UNIQUE
);

En SQLite, una columna declarada exactamente como INTEGER PRIMARY KEY es un alias del rowid interno y se autoincrementa automáticamente si no le das valor. La palabra AUTOINCREMENT es opcional y solo añade la garantía de que un identificador borrado nunca se reutilice, a cambio de una tabla interna adicional. La documentación oficial de SQLite recomienda no usarla salvo que esa garantía haga falta.

Ojo al detalle: tiene que ser INTEGER, en mayúsculas o minúsculas pero esa palabra exacta. INT PRIMARY KEY no activa el comportamiento.

Tabla resumen

	PostgreSQL	SQLite
Recomendado	`INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY`	`INTEGER PRIMARY KEY`
Alternativa	`SERIAL PRIMARY KEY`	`INTEGER PRIMARY KEY AUTOINCREMENT`
Empieza en	1	1 (o el máximo existente + 1)

Entregable: el esquema completo de BiblioRed

Llegó el momento. Este es el script que convierte el plano de la lección 02-01 en tablas reales.

El orden importa

Una tabla no puede referenciar a otra que aún no existe. Hay que crearlas siguiendo las dependencias:

flowchart TD
    A["1. sucursales<br/><i>sin dependencias</i>"] --> B["2. socios<br/><i>→ sucursales</i>"]
    C["1. autores<br/><i>sin dependencias</i>"] --> D["3. libros<br/><i>→ autores</i>"]
    A --> E["4. ejemplares<br/><i>→ libros, sucursales</i>"]
    D --> E
    B --> F["5. prestamos<br/><i>→ socios, ejemplares</i>"]
    E --> F
    B --> G["6. reservas<br/><i>→ socios, libros</i>"]
    D --> G

Un orden válido: sucursales, autores, socios, libros, ejemplares, prestamos, reservas.

El script (PostgreSQL)

Conéctate primero a la base de datos que creaste en la lección 01-04:

$ psql -U alumno -d biblioredb
biblioredb=>

Y ejecuta:

-- ============================================================
--  BiblioRed - Esquema relacional
--  Módulo 2, lección 02-02. Dialecto: PostgreSQL
-- ============================================================

-- 1. SUCURSALES: las cuatro bibliotecas de la red.
--    Sin claves ajenas: es la raíz del esquema.
CREATE TABLE sucursales (
    sucursal_id     INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    nombre          VARCHAR(60)  NOT NULL,
    direccion       VARCHAR(120) NOT NULL,
    telefono        VARCHAR(20),
    fecha_apertura  DATE,
    CONSTRAINT pk_sucursales PRIMARY KEY (sucursal_id),
    CONSTRAINT uq_sucursales_nombre UNIQUE (nombre)
);

-- 2. AUTORES: catálogo de autores. Tampoco depende de nadie.
--    anio_nacimiento es SMALLINT: un año cabe de sobra.
CREATE TABLE autores (
    autor_id        INTEGER     GENERATED BY DEFAULT AS IDENTITY,
    nombre          VARCHAR(60) NOT NULL,
    apellidos       VARCHAR(80) NOT NULL,
    nacionalidad    VARCHAR(40),
    anio_nacimiento SMALLINT,
    CONSTRAINT pk_autores PRIMARY KEY (autor_id)
);

-- 3. SOCIOS: las personas con carné.
--    email es UNIQUE pero admite NULL: no todos dan correo,
--    y en SQL varios NULL no se consideran duplicados entre sí.
--    sucursal_id es NOT NULL: todo socio se da de alta en una sucursal.
CREATE TABLE socios (
    socio_id     INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    nombre       VARCHAR(60)  NOT NULL,
    apellidos    VARCHAR(80)  NOT NULL,
    email        VARCHAR(120),
    fecha_alta   DATE         NOT NULL,
    sucursal_id  INTEGER      NOT NULL,
    activo       BOOLEAN      NOT NULL,
    CONSTRAINT pk_socios PRIMARY KEY (socio_id),
    CONSTRAINT uq_socios_email UNIQUE (email),
    CONSTRAINT fk_socios_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursales (sucursal_id)
);

-- 4. LIBROS: la OBRA, no el objeto físico.
--    isbn: clave alternativa (UNIQUE) y no clave primaria, porque
--    no todo el fondo tiene ISBN. VARCHAR, no número: no se opera con él.
--    autor_id admite NULL: hay obras anónimas o de autoría no catalogada.
CREATE TABLE libros (
    libro_id         INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    isbn             VARCHAR(13),
    titulo           VARCHAR(200) NOT NULL,
    autor_id         INTEGER,
    editorial        VARCHAR(80),
    anio_publicacion SMALLINT,
    idioma           VARCHAR(20),
    CONSTRAINT pk_libros PRIMARY KEY (libro_id),
    CONSTRAINT uq_libros_isbn UNIQUE (isbn),
    CONSTRAINT fk_libros_autor
        FOREIGN KEY (autor_id) REFERENCES autores (autor_id)
);

-- 5. EJEMPLARES: el objeto físico que se presta.
--    codigo es la etiqueta pegada al lomo ('EJ-3081'): clave alternativa.
--    estado: 'disponible', 'prestado', 'reparacion', 'baja'.
--    (La restricción CHECK que lo garantiza se añade en la lección 04-04.)
CREATE TABLE ejemplares (
    ejemplar_id       INTEGER     GENERATED BY DEFAULT AS IDENTITY,
    codigo            VARCHAR(10) NOT NULL,
    libro_id          INTEGER     NOT NULL,
    sucursal_id       INTEGER     NOT NULL,
    estado            VARCHAR(20) NOT NULL,
    fecha_adquisicion DATE,
    CONSTRAINT pk_ejemplares PRIMARY KEY (ejemplar_id),
    CONSTRAINT uq_ejemplares_codigo UNIQUE (codigo),
    CONSTRAINT fk_ejemplares_libro
        FOREIGN KEY (libro_id) REFERENCES libros (libro_id),
    CONSTRAINT fk_ejemplares_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursales (sucursal_id)
);

-- 6. PRESTAMOS: une un socio con un EJEMPLAR concreto.
--    fecha_devolucion NULL = préstamo todavía abierto.
--    recargo NUMERIC(6,2): es dinero, nunca coma flotante.
CREATE TABLE prestamos (
    prestamo_id               INTEGER GENERATED BY DEFAULT AS IDENTITY,
    socio_id                  INTEGER NOT NULL,
    ejemplar_id               INTEGER NOT NULL,
    fecha_prestamo            DATE    NOT NULL,
    fecha_devolucion_prevista DATE    NOT NULL,
    fecha_devolucion          DATE,
    recargo                   NUMERIC(6,2),
    CONSTRAINT pk_prestamos PRIMARY KEY (prestamo_id),
    CONSTRAINT fk_prestamos_socio
        FOREIGN KEY (socio_id) REFERENCES socios (socio_id),
    CONSTRAINT fk_prestamos_ejemplar
        FOREIGN KEY (ejemplar_id) REFERENCES ejemplares (ejemplar_id)
);

-- 7. RESERVAS: une un socio con un LIBRO (la obra), no con un ejemplar:
--    el socio reserva el título y se le asigna la primera copia que se libere.
--    estado: 'activa', 'atendida', 'cancelada'.
CREATE TABLE reservas (
    reserva_id       INTEGER     GENERATED BY DEFAULT AS IDENTITY,
    socio_id         INTEGER     NOT NULL,
    libro_id         INTEGER     NOT NULL,
    fecha_reserva    DATE        NOT NULL,
    fecha_expiracion DATE,
    estado           VARCHAR(20) NOT NULL,
    CONSTRAINT pk_reservas PRIMARY KEY (reserva_id),
    CONSTRAINT fk_reservas_socio
        FOREIGN KEY (socio_id) REFERENCES socios (socio_id),
    CONSTRAINT fk_reservas_libro
        FOREIGN KEY (libro_id) REFERENCES libros (libro_id)
);

Salida esperada, una línea por instrucción:

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

Comprobación

biblioredb=> \dt

             Listado de relaciones
 Esquema |   Nombre    | Tipo  |  Dueño
---------+-------------+-------+---------
 public  | autores     | tabla | alumno
 public  | ejemplares  | tabla | alumno
 public  | libros      | tabla | alumno
 public  | prestamos   | tabla | alumno
 public  | reservas    | tabla | alumno
 public  | socios      | tabla | alumno
 public  | sucursales  | tabla | alumno
(7 filas)

Y el detalle de una tabla concreta:

biblioredb=> \d ejemplares

                              Tabla «public.ejemplares»
      Columna      |         Tipo          | Nulable |            Por omisión
-------------------+-----------------------+---------+------------------------------------
 ejemplar_id       | integer               | not null| generated by default as identity
 codigo            | character varying(10) | not null|
 libro_id          | integer               | not null|
 sucursal_id       | integer               | not null|
 estado            | character varying(20) | not null|
 fecha_adquisicion | date                  |         |
Índices:
    "pk_ejemplares" PRIMARY KEY, btree (ejemplar_id)
    "uq_ejemplares_codigo" UNIQUE CONSTRAINT, btree (codigo)
Restricciones de llave foránea:
    "fk_ejemplares_libro" FOREIGN KEY (libro_id) REFERENCES libros(libro_id)
    "fk_ejemplares_sucursal" FOREIGN KEY (sucursal_id) REFERENCES sucursales(sucursal_id)

Si ves esto, el esquema está en pie.

La versión SQLite

El mismo script, con tres cambios: la clave autoincremental, TEXT en lugar de VARCHAR (SQLite lo trata igual, pero es su convención) y una línea imprescindible al principio.

-- ¡OBLIGATORIO! SQLite ignora las claves ajenas si no se activan,
-- y hay que hacerlo en CADA sesión. Sin esto, el esquema se crea igual
-- pero no protege nada: podrás insertar préstamos de socios inexistentes.
PRAGMA foreign_keys = ON;

CREATE TABLE sucursales (
    sucursal_id    INTEGER PRIMARY KEY,
    nombre         TEXT    NOT NULL UNIQUE,
    direccion      TEXT    NOT NULL,
    telefono       TEXT,
    fecha_apertura TEXT            -- SQLite no tiene tipo DATE
);

CREATE TABLE autores (
    autor_id        INTEGER PRIMARY KEY,
    nombre          TEXT    NOT NULL,
    apellidos       TEXT    NOT NULL,
    nacionalidad    TEXT,
    anio_nacimiento INTEGER
);

CREATE TABLE socios (
    socio_id    INTEGER PRIMARY KEY,
    nombre      TEXT    NOT NULL,
    apellidos   TEXT    NOT NULL,
    email       TEXT    UNIQUE,
    fecha_alta  TEXT    NOT NULL,
    sucursal_id INTEGER NOT NULL REFERENCES sucursales(sucursal_id),
    activo      INTEGER NOT NULL          -- 0 / 1: SQLite no tiene BOOLEAN
);

CREATE TABLE libros (
    libro_id         INTEGER PRIMARY KEY,
    isbn             TEXT UNIQUE,
    titulo           TEXT NOT NULL,
    autor_id         INTEGER REFERENCES autores(autor_id),
    editorial        TEXT,
    anio_publicacion INTEGER,
    idioma           TEXT
);

CREATE TABLE ejemplares (
    ejemplar_id       INTEGER PRIMARY KEY,
    codigo            TEXT    NOT NULL UNIQUE,
    libro_id          INTEGER NOT NULL REFERENCES libros(libro_id),
    sucursal_id       INTEGER NOT NULL REFERENCES sucursales(sucursal_id),
    estado            TEXT    NOT NULL,
    fecha_adquisicion TEXT
);

CREATE TABLE prestamos (
    prestamo_id               INTEGER PRIMARY KEY,
    socio_id                  INTEGER NOT NULL REFERENCES socios(socio_id),
    ejemplar_id               INTEGER NOT NULL REFERENCES ejemplares(ejemplar_id),
    fecha_prestamo            TEXT    NOT NULL,
    fecha_devolucion_prevista TEXT    NOT NULL,
    fecha_devolucion          TEXT,
    recargo                   NUMERIC
);

CREATE TABLE reservas (
    reserva_id       INTEGER PRIMARY KEY,
    socio_id         INTEGER NOT NULL REFERENCES socios(socio_id),
    libro_id         INTEGER NOT NULL REFERENCES libros(libro_id),
    fecha_reserva    TEXT    NOT NULL,
    fecha_expiracion TEXT,
    estado           TEXT    NOT NULL
);

Comprobación:

sqlite> .tables
autores     ejemplares  libros      prestamos   reservas    socios      sucursales
sqlite> PRAGMA foreign_keys;
1

El PRAGMA foreign_keys = ON es tan importante que la lección 02-06 vuelve sobre él.

`ALTER TABLE`: modificar lo ya creado

Los esquemas cambian. ALTER TABLE modifica la estructura sin perder los datos.

Añadir una columna

BiblioRed quiere registrar el teléfono móvil de los socios:

ALTER TABLE socios ADD COLUMN telefono VARCHAR(20);

ALTER TABLE

La columna se añade al final y todas las filas existentes quedan con NULL. Por eso, añadir una columna NOT NULL a una tabla con datos falla: no hay valor que poner en las filas que ya están.

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

ERROR:  la columna «dni» de la relación «socios» contiene valores nulos

La solución habitual es en tres pasos: añadirla nullable, rellenarla con un UPDATE (lección 02-03) y después imponer el NOT NULL.

Renombrar una columna o una tabla

ALTER TABLE socios RENAME COLUMN telefono TO telefono_movil;
ALTER TABLE socios RENAME TO usuarios;   -- renombrar la tabla entera
ALTER TABLE usuarios RENAME TO socios;   -- lo dejamos como estaba

Cambiar el tipo de una columna

-- PostgreSQL: la sintaxis es ALTER COLUMN ... TYPE
ALTER TABLE libros ALTER COLUMN editorial TYPE VARCHAR(120);

Ampliar el tamaño es seguro. Reducirlo o cambiar de familia de tipos puede fallar si los datos existentes no caben o no se pueden convertir; en ese caso PostgreSQL aborta y no toca nada.

Añadir o quitar restricciones

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

ALTER TABLE libros ADD CONSTRAINT uq_libros_isbn UNIQUE (isbn);
ALTER TABLE libros DROP CONSTRAINT uq_libros_isbn;

Aquí se ve el rendimiento práctico de haber puesto nombre a las restricciones: para eliminar una hay que nombrarla, y uq_libros_isbn es bastante más manejable que el nombre automático libros_isbn_key.

Eliminar una columna

ALTER TABLE socios DROP COLUMN telefono_movil;

Esto borra los datos de esa columna y no hay deshacer (fuera de una transacción). Piénsalo dos veces.

La limitación de SQLite

SQLite solo admite un subconjunto de ALTER TABLE:

Operación	PostgreSQL	SQLite
`ADD COLUMN`	Sí	Sí
`RENAME TO` (tabla)	Sí	Sí
`RENAME COLUMN`	Sí	Sí (desde 3.25)
`DROP COLUMN`	Sí	Sí (desde 3.35, con restricciones)
`ALTER COLUMN ... TYPE`	Sí	No
`ADD CONSTRAINT`	Sí	No

El procedimiento oficial en SQLite para lo que no admite es: crear una tabla nueva con la estructura correcta, copiar los datos con INSERT INTO ... SELECT, borrar la vieja y renombrar la nueva. Es tedioso, y es otro argumento a favor de PostgreSQL para un esquema que va a evolucionar.

`DROP TABLE` y el orden de destrucción

DROP TABLE reservas;

Elimina la tabla y todos sus datos, de forma inmediata y sin confirmación. En PostgreSQL, si lo ejecutas dentro de una transacción todavía puedes hacer ROLLBACK (lección 06-01); en SQLite y en la mayoría de los gestores, no.

Dos variantes útiles:

-- No falla si la tabla no existe: imprescindible en scripts reejecutables
DROP TABLE IF EXISTS reservas;

-- Elimina también los objetos que dependen de ella (¡peligroso!)
DROP TABLE libros CASCADE;

El orden inverso al de creación

Si intentas borrar una tabla a la que otras apuntan, el gestor te lo impide:

DROP TABLE libros;

ERROR:  no se puede eliminar tabla libros porque otros objetos dependen de ella
DETALLE:  restricción fk_ejemplares_libro en tabla ejemplares depende de tabla libros

Esto es integridad referencial funcionando exactamente como debe. Para vaciar el esquema y volver a empezar, hay que ir en orden inverso al de creación: primero las hijas, después las padres.

DROP TABLE IF EXISTS reservas;
DROP TABLE IF EXISTS prestamos;
DROP TABLE IF EXISTS ejemplares;
DROP TABLE IF EXISTS libros;
DROP TABLE IF EXISTS socios;
DROP TABLE IF EXISTS autores;
DROP TABLE IF EXISTS sucursales;

Guarda estas siete líneas al principio de tu script de creación, comentadas. Cuando quieras rehacer el esquema desde cero —y lo querrás varias veces durante el curso— las descomentas y ejecutas el fichero entero.

Errores Comunes y Consejos

Usar comillas dobles para valores de texto. WHERE estado = "disponible" hace que PostgreSQL busque una columna llamada disponible. Los valores van entre comillas simples, siempre.
Olvidar el punto y coma. Si psql muestra biblioredb-# en vez de biblioredb=#, la instrucción sigue abierta. Escribe ; y pulsa Intro.
Nombrar columnas con mayúsculas o tildes. "FechaAlta" o "año" te condenan a entrecomillar en cada consulta que escribas durante el resto de la vida de la base de datos. fecha_alta, anio.
Crear las tablas en orden equivocado. REFERENCES libros falla si libros aún no existe. Sigue el árbol de dependencias.
Usar FLOAT o REAL para dinero. Sumas que no cuadran, céntimos que se evaporan, cierres contables imposibles. NUMERIC(p, s).
Guardar teléfonos, ISBN o códigos postales como números. Pierdes los ceros iniciales, los prefijos y los guiones. Si no se opera aritméticamente con ello, es texto.
Olvidar PRAGMA foreign_keys = ON en SQLite. El esquema se crea con aspecto correcto pero no valida nada, y descubres las filas huérfanas meses después. Hay que ejecutarlo en cada sesión.
Añadir una columna NOT NULL a una tabla con datos. Falla siempre. Añádela nullable, rellénala y luego impón la restricción.
Consejo: guarda el script del esquema en un fichero (esquema_biblioredb.sql) y ejecútalo con psql -U alumno -d biblioredb -f esquema_biblioredb.sql o con .read esquema_biblioredb.sql en SQLite. Un esquema que solo existe en el historial de la consola es un esquema perdido.
Consejo: pon nombre a todas las restricciones. El día que salte un error, el nombre será la mitad del diagnóstico.

Ejercicios

Ejercicio 1: Clasificar instrucciones por sublenguaje

Indica a qué sublenguaje pertenece cada instrucción y en qué lección del curso se trata:

SELECT titulo FROM libros;
ALTER TABLE socios ADD COLUMN telefono VARCHAR(20);
UPDATE ejemplares SET estado = 'disponible' WHERE ejemplar_id = 1;
GRANT SELECT ON libros TO mostrador;
ROLLBACK;
DROP TABLE reservas;
INSERT INTO autores (nombre, apellidos) VALUES ('Marina', 'Escolá');

Ejercicio 2: Detectar y corregir errores en un `CREATE TABLE`

BiblioRed quiere una tabla nueva, multas, para las sanciones acumuladas de cada socio. Un compañero ha escrito esto:

CREATE TABLE Multas (
    ID           INT,
    Socio        INT NOT NULL REFERENCES socios(socio_id),
    Importe      FLOAT NOT NULL,
    Fecha        VARCHAR(10) NOT NULL,
    Motivo       VARCHAR(200),
    Pagada       VARCHAR(2) NOT NULL,
    "Nº Aviso"   INT
)

Encuentra al menos seis problemas y reescribe la tabla correctamente para PostgreSQL, siguiendo las convenciones del curso.

Ejercicio 3: Evolucionar el esquema

Escribe las instrucciones ALTER TABLE necesarias para cada cambio que pide la dirección de BiblioRed. Indica también, cuando corresponda, si la operación puede fallar sobre una tabla que ya tiene datos y cómo evitarlo.

Aviso: este ejercicio es de redacción, no de ejecución. Si decides probarlo, hazlo sobre una base de datos aparte, o deshaz después los cambios: el resto del módulo asume el esquema tal como quedó en el apartado 8, con la columna libros.idioma incluida.

Añadir a sucursales una columna email_contacto de hasta 120 caracteres, opcional.
Añadir a libros una columna num_paginas entera, opcional.
La columna libros.editorial se queda corta: ampliarla a 150 caracteres.
sucursales.fecha_apertura debe pasar a ser obligatoria.
Renombrar reservas.fecha_expiracion a fecha_caducidad.
Eliminar de libros la columna idioma, que nadie usa.

Soluciones

Solución 1

#	Instrucción	Sublenguaje	Lección
1	`SELECT`	DQL (o DML en la clasificación de cuatro grupos)	02-03
2	`ALTER TABLE`	DDL	02-02 (esta)
3	`UPDATE`	DML	02-03
4	`GRANT`	DCL	06-04
5	`ROLLBACK`	TCL	06-01
6	`DROP TABLE`	DDL	02-02 (esta)
7	`INSERT`	DML	02-03

Solución 2

Problemas encontrados:

Multas, ID, Socio… en mayúsculas mezcladas. PostgreSQL las pasa a minúsculas, así que "funciona", pero rompe la convención del resto del esquema. Nombres en minusculas_con_guion_bajo.
ID sin PRIMARY KEY. La tabla no tendría clave primaria: se violaría la integridad de entidad y se podrían insertar filas duplicadas indistinguibles.
ID sin autoincremento. Habría que inventar el número a mano en cada inserción.
Importe FLOAT. Es dinero: debe ser NUMERIC(6,2).
Fecha VARCHAR(10). Es una fecha: debe ser DATE. Como texto, no se podría ordenar de forma fiable, ni restar, ni validar.
Pagada VARCHAR(2). Es un sí/no: debe ser BOOLEAN. Con VARCHAR(2) acabarían conviviendo 'S', 'si', 'SI', '1' y 'no'.
"Nº Aviso" entre comillas dobles, con espacio y con º. Condena a entrecomillar siempre y no es URL-safe ni portable. num_aviso.
Nombre de columna Socio poco descriptivo: la convención del esquema es socio_id.
Restricciones sin nombre y falta el punto y coma final.

Versión corregida:

CREATE TABLE multas (
    multa_id   INTEGER      GENERATED BY DEFAULT AS IDENTITY,
    socio_id   INTEGER      NOT NULL,
    importe    NUMERIC(6,2) NOT NULL,
    fecha      DATE         NOT NULL,
    motivo     VARCHAR(200),
    pagada     BOOLEAN      NOT NULL,
    num_aviso  INTEGER,
    CONSTRAINT pk_multas PRIMARY KEY (multa_id),
    CONSTRAINT fk_multas_socio
        FOREIGN KEY (socio_id) REFERENCES socios (socio_id)
);

Solución 3

-- 1. Columna opcional: no hay riesgo, las filas existentes quedan a NULL.
ALTER TABLE sucursales ADD COLUMN email_contacto VARCHAR(120);

-- 2. Igual: opcional, sin riesgo.
ALTER TABLE libros ADD COLUMN num_paginas INTEGER;

-- 3. Ampliar un VARCHAR es seguro: todo lo que cabía en 80 cabe en 150.
--    (Reducirlo sí podría fallar si algún valor excediera el nuevo límite.)
ALTER TABLE libros ALTER COLUMN editorial TYPE VARCHAR(150);

-- 4. PUEDE FALLAR: si alguna sucursal tiene fecha_apertura a NULL, PostgreSQL
--    aborta. Hay que rellenar primero y después imponer la restricción.
UPDATE sucursales SET fecha_apertura = '2000-01-01' WHERE fecha_apertura IS NULL;
ALTER TABLE sucursales ALTER COLUMN fecha_apertura SET NOT NULL;

-- 5. Renombrar no toca los datos, pero SÍ rompe las consultas y aplicaciones
--    que usaran el nombre antiguo. Hay que revisarlas.
ALTER TABLE reservas RENAME COLUMN fecha_expiracion TO fecha_caducidad;

-- 6. Destructivo e irreversible fuera de una transacción: se pierden los datos.
ALTER TABLE libros DROP COLUMN idioma;

Nota sobre SQLite: los puntos 3 y 4 no se pueden hacer con ALTER TABLE; habría que recrear la tabla, copiar los datos y renombrar.

Conclusión

Esta lección ha convertido el plano en un edificio. Repasemos:

SQL es declarativo: describes el resultado y el optimizador —el de la lección 01-04— decide el camino. Eso hace que escribas menos y que el gestor pueda mejorar el rendimiento sin que toques la consulta.
Hay un estándar y hay dialectos: un núcleo común muy amplio y una periferia propia de cada motor. PostgreSQL es nuestra referencia; SQLite, la alternativa ligera.
Los cinco sublenguajes: DDL (estructura), DML (datos), DQL (consultas), DCL (permisos) y TCL (transacciones).
Las reglas de escritura: palabras clave en mayúsculas, identificadores en minusculas_con_guion_bajo y sin comillas dobles, comillas simples para los literales de texto, punto y coma al final y comentarios con -- o /* */.
Los tipos que necesitábamos: INTEGER/SMALLINT para enteros, NUMERIC(p, s) para dinero (nunca coma flotante), VARCHAR(n)/TEXT para texto, DATE/TIMESTAMP para fechas en formato ISO y BOOLEAN para sí/no, con las particularidades de SQLite en cada caso.
CREATE TABLE con PRIMARY KEY, NOT NULL, UNIQUE y REFERENCES, en forma de columna o de tabla con nombre propio; las claves autoincrementales (GENERATED ... AS IDENTITY o SERIAL en PostgreSQL, INTEGER PRIMARY KEY en SQLite); ALTER TABLE para añadir, renombrar, retipar y eliminar columnas; y DROP TABLE, que exige recorrer las dependencias en orden inverso.
Y, sobre todo, el esquema completo de BiblioRed: sucursales, autores, socios, libros, ejemplares, prestamos y reservas, creado y verificado con \dt.

Las siete tablas existen y están vacías. En la lección 02-03, Operaciones Básicas en SQL, las llenamos: verás INSERT, SELECT con WHERE, ORDER BY y LIMIT, UPDATE y DELETE, todo sobre una sola tabla. El entregable de esa lección será el juego de datos de prueba de BiblioRed —los cuatro sucursales, los socios, los libros, los ejemplares y los préstamos ficticios— que usaremos hasta el final del módulo. No borres tus tablas: a partir de ahora, cada lección construye sobre la anterior.

Lenguaje SQL