🗄️ Guía de Aula Invertida

— vistas

DDL y Restricciones en PostgreSQL

Ya normalizaste tu esquema hasta 3FN. Ahora toca escribirlo de verdad: crear las tablas, definir sus restricciones y decidir qué pasa cuando se borra o actualiza un dato relacionado. Todo con la sintaxis y las particularidades de PostgreSQL.

Ing. Roy Carrasco
Facultad de Ingeniería de Sistemas · UAB

Bienvenido

En la guía anterior llegaste a un esquema en 3FN: PEDIDO, CLIENTE, PRODUCTO y DETALLE_PEDIDO, con sus claves primarias y foráneas ya decididas. Esta guía traduce ese diseño a DDL (Data Definition Language) real, usando PostgreSQL como motor de referencia.

Objetivo del módulo

Al finalizar esta guía sabrás elegir tipos de datos apropiados en PostgreSQL, escribir CREATE TABLE con restricciones (PK, FK, CHECK, UNIQUE, DEFAULT), decidir el orden de creación según las dependencias entre tablas, modificar tablas existentes con ALTER TABLE, y eliminarlas de forma segura con DROP TABLE.

📝 Actividades de este módulo

Encontrarás actividades cortas y autocorregibles —selección múltiple, verdadero/falso, emparejamiento, clasificación y completar espacios— repartidas a lo largo de la guía, y una sección de ejercicios de razonamiento donde se te da un escenario y debes predecir el resultado correcto antes de comprobarlo. Cada actividad te da retroalimentación inmediata.

🧱 Construye

El esquema de la guía anterior, ahora como tablas reales de PostgreSQL.

🔒 Restringe

PK, FK, CHECK, UNIQUE y DEFAULT: las reglas que la base de datos hace cumplir por ti.

🐘 Piensa en Postgres

Decisiones específicas del motor: tipos, identidad de filas, y comportamiento ante borrados en cascada.

Tipos de datos en PostgreSQL

PostgreSQL tiene un catálogo de tipos más rico que la mayoría de motores. Estos son los que vas a usar constantemente.

Tipo	Uso
`INTEGER` / `BIGINT`	Números enteros. `BIGINT` cuando esperas más de ~2.100 millones de filas.
`NUMERIC(precision, escala)`	Dinero y cantidades exactas. `NUMERIC(10,2)` = hasta 10 dígitos, 2 decimales. Nunca uses `FLOAT` para dinero: pierde precisión.
`VARCHAR(n)` / `TEXT`	Texto. En PostgreSQL no hay diferencia de rendimiento entre ambos: usa `VARCHAR(n)` solo si de verdad necesitas forzar un límite.
`BOOLEAN`	Verdadero/falso nativo (`true`/`false`), no un entero disfrazado como en otros motores.
`DATE`	Solo fecha, sin hora.
`TIMESTAMPTZ`	Fecha y hora con zona horaria. Es la recomendación oficial de PostgreSQL sobre `TIMESTAMP` a secas, salvo que tengas una razón específica para no usarla.
`UUID`	Identificador único de 128 bits, alternativa a los IDs autoincrementales cuando necesitas generarlos fuera de la base de datos.

SERIAL vs. GENERATED ALWAYS AS IDENTITY

Durante años, la forma de tener un ID autoincremental fue id SERIAL PRIMARY KEY. Desde PostgreSQL 10, la forma recomendada por el estándar SQL es id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY: hace lo mismo, pero evita que alguien inserte manualmente un valor y descuadre la secuencia interna. Vas a ver ambas formas en código real; en esta guía usamos la moderna.

Selección múltiple

Necesitas guardar el precio de un producto, con exactitud hasta el centavo. ¿Qué tipo eliges en PostgreSQL?

CREATE TABLE

Vamos a crear CLIENTE y PRODUCTO primero: no dependen de ninguna otra tabla del esquema.

SQL

CREATE TABLE cliente (
    cliente_id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cliente_nombre  VARCHAR(120) NOT NULL
);

CREATE TABLE producto (
    producto_id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    producto_nombre  VARCHAR(120) NOT NULL,
    precio           NUMERIC(10,2) NOT NULL
);

Cada columna se escribe como nombre tipo restricciones. PRIMARY KEY junto al tipo declara una clave primaria de una sola columna directamente.

IF NOT EXISTS

Si vas a ejecutar el mismo script varias veces (por ejemplo en clase, mientras pruebas), CREATE TABLE IF NOT EXISTS cliente (...) evita el error "la relación ya existe" en las ejecuciones repetidas.

Restricciones de columna

Una restricción (constraint) es una regla que PostgreSQL hace cumplir automáticamente al insertar o modificar filas. Si la fila la rompe, la operación falla.

Restricción	Qué garantiza
`NOT NULL`	La columna no puede quedar vacía (sin valor).
`UNIQUE`	No puede haber dos filas con el mismo valor en esa columna.
`PRIMARY KEY`	Combina `NOT NULL` + `UNIQUE`, y marca la columna (o combinación de columnas) que identifica cada fila.
`CHECK (condición)`	La condición debe cumplirse siempre. Ej: `CHECK (precio > 0)` impide precios negativos o en cero.
`DEFAULT valor`	Si no se especifica un valor al insertar, se usa este por defecto.

SQL

CREATE TABLE producto (
    producto_id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    producto_nombre  VARCHAR(120) NOT NULL,
    precio           NUMERIC(10,2) NOT NULL CHECK (precio > 0),
    activo           BOOLEAN NOT NULL DEFAULT true
);

Completar

Sobre la tabla producto de arriba:

1. Si intentas insertar un producto con precio = -50, la restricción que lo impide es .

2. Si no especificas el valor de "activo" al insertar, PostgreSQL usará .

Claves foráneas y acciones referenciales

Ahora creamos PEDIDO, que depende de CLIENTE. La clave foránea (REFERENCES) obliga a que todo cliente_id en PEDIDO exista primero en CLIENTE.

SQL

CREATE TABLE pedido (
    pedido_id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    fecha       DATE NOT NULL DEFAULT CURRENT_DATE,
    cliente_id  INTEGER NOT NULL REFERENCES cliente(cliente_id)
);

Y DETALLE_PEDIDO, con clave primaria compuesta y dos claves foráneas — el esquema completo, tal como quedó en 3FN.

SQL

CREATE TABLE detalle_pedido (
    pedido_id    INTEGER REFERENCES pedido(pedido_id) ON DELETE CASCADE,
    producto_id  INTEGER REFERENCES producto(producto_id) ON DELETE RESTRICT,
    cantidad     INTEGER NOT NULL CHECK (cantidad > 0),
    PRIMARY KEY (pedido_id, producto_id)
);

Cuando la clave primaria es compuesta, no se declara junto a una columna: se agrega como una línea aparte, PRIMARY KEY (col1, col2).

¿Qué hace ON DELETE?

Define qué pasa con las filas de DETALLE_PEDIDO cuando se borra la fila de PEDIDO o PRODUCTO que referencian.

Acción	Qué hace al borrar el padre
`CASCADE`	Borra también las filas hijas automáticamente.
`RESTRICT`	Impide el borrado del padre mientras existan hijos que lo referencian.
`SET NULL`	Pone en NULL la columna de clave foránea en las filas hijas (requiere que la columna admita NULL).
`NO ACTION`	Es el valor por defecto si no se especifica nada; en la práctica se comporta como RESTRICT, salvo que la restricción se declare como diferible.

Por qué CASCADE en un caso y RESTRICT en otro

Si borras un PEDIDO, tiene sentido borrar también sus líneas de DETALLE_PEDIDO: no existen por sí solas. Por eso ON DELETE CASCADE. Pero si intentas borrar un PRODUCTO que ya fue vendido, quieres que PostgreSQL te lo impida hasta que decidas qué hacer con el historial: por eso ON DELETE RESTRICT.

Clasificación

Clasifica cada situación según la acción ON DELETE que describe.

Al borrar un PEDIDO, sus filas de DETALLE_PEDIDO desaparecen automáticamente con él.

PostgreSQL rechaza borrar un PRODUCTO mientras tenga ventas registradas en DETALLE_PEDIDO.

Al borrar un EMPLEADO que era el "supervisor" de otros, la columna supervisor_id de esos otros queda vacía en vez de bloquear el borrado.

Orden de creación y dependencias

PostgreSQL no te deja crear una clave foránea que apunte a una tabla que todavía no existe. Por eso el orden de los CREATE TABLE importa: primero las tablas "independientes", luego las que dependen de ellas.

Orden correcto para nuestro esquema

1) CLIENTE y PRODUCTO (no dependen de nadie) → 2) PEDIDO (depende de CLIENTE) → 3) DETALLE_PEDIDO (depende de PEDIDO y PRODUCTO).

Verdadero o falso

Si ejecutas el CREATE TABLE de detalle_pedido antes que el de producto, PostgreSQL lo crea sin problema y simplemente valida la referencia después.

ALTER TABLE

Una tabla ya creada no es definitiva: ALTER TABLE permite agregar, modificar o quitar columnas y restricciones sin recrearla desde cero.

SQL

-- Agregar una columna nueva
ALTER TABLE cliente ADD COLUMN correo VARCHAR(150);

-- Agregar una restricción a una columna existente
ALTER TABLE cliente ADD CONSTRAINT correo_unico UNIQUE (correo);

-- Cambiar el tipo de una columna
ALTER TABLE producto ALTER COLUMN producto_nombre TYPE VARCHAR(200);

-- Renombrar una columna
ALTER TABLE cliente RENAME COLUMN cliente_nombre TO nombre;

-- Quitar una columna
ALTER TABLE cliente DROP COLUMN correo;

Cuidado en producción

Agregar una columna NOT NULL a una tabla que ya tiene filas falla, a menos que le des un DEFAULT: PostgreSQL necesita un valor para llenar las filas existentes. Por eso ADD COLUMN correo VARCHAR(150) NOT NULL DEFAULT '' funciona, pero ADD COLUMN correo VARCHAR(150) NOT NULL sin default falla si la tabla no está vacía.

Selección múltiple

La tabla cliente ya tiene 500 filas. Ejecutas: ALTER TABLE cliente ADD COLUMN puntos INTEGER NOT NULL; ¿Qué ocurre?

DROP TABLE

Elimina una tabla por completo, junto con sus datos, índices y restricciones. Es irreversible salvo que estés dentro de una transacción sin confirmar todavía.

SQL

DROP TABLE IF EXISTS detalle_pedido;
DROP TABLE IF EXISTS pedido;
DROP TABLE IF EXISTS producto, cliente;

El orden es el inverso al de creación: primero las tablas que tienen claves foráneas hacia otras, al final las que son referenciadas. Si intentas borrar CLIENTE mientras PEDIDO todavía la referencia (y la relación no tiene ON DELETE CASCADE a nivel de esa restricción específica), PostgreSQL lo rechaza.

DROP TABLE ... CASCADE

Si quieres forzar el borrado de una tabla y de todo lo que dependa de ella (claves foráneas de otras tablas, vistas que la usan, etc.), PostgreSQL ofrece DROP TABLE cliente CASCADE;. Es potente y peligroso: borra en cadena sin volver a preguntar. Úsalo solo cuando de verdad entiendes qué depende de esa tabla.

Emparejamiento

Une cada instrucción con lo que hace.

Ejercicios de razonamiento

En estas preguntas no se te pide recordar sintaxis: se te da un escenario, y debes razonar cuál sería la respuesta correcta antes de comprobarla.

Escenario

Quieres agregar la tabla PAGO(pago_id, pedido_id, monto, fecha_pago), donde cada pedido puede tener varios pagos (pago parcial), y monto nunca debe ser negativo ni cero.

Razonamiento

¿Cuál CREATE TABLE es el correcto?

Otro escenario

Tienes EMPLEADO(empleado_id, nombre, supervisor_id) donde supervisor_id referencia a otro empleado_id de la misma tabla (un empleado puede supervisar a otros). Un supervisor puede ser despedido, y en ese caso sus subordinados deben quedar temporalmente sin supervisor, no bloquear el despido.

Razonamiento

¿Qué cláusula ON DELETE usarías en la clave foránea de supervisor_id?

Un último escenario

Necesitas ejecutar un script de instalación varias veces durante el desarrollo, sin que falle si las tablas ya existen de una corrida anterior.

Completar

Para lograrlo, cada CREATE TABLE del script debería escribirse como:

CREATE TABLE nombre_tabla (...);

Actividades de repaso

Un repaso integrador de todo el módulo. Cada actividad se corrige al instante; tu progreso se guarda automáticamente en este navegador.

0 / 0

actividades correctas en toda la guía

Selección múltiple

¿Cuál es la diferencia práctica entre VARCHAR(n) y TEXT en PostgreSQL?

Verdadero o falso

GENERATED ALWAYS AS IDENTITY y SERIAL logran, en la práctica, el mismo objetivo: un valor autoincremental para la clave primaria.

Selección múltiple

¿En qué orden debes ejecutar los CREATE TABLE de un esquema con dependencias por clave foránea?

Selección múltiple

¿Qué hace específicamente ON DELETE RESTRICT?

Selección múltiple

Quieres agregar una columna NOT NULL a una tabla que ya tiene 10.000 filas, sin que la instrucción falle. ¿Qué debes incluir?

Cheat Sheet

Instrucción	Para qué sirve
`CREATE TABLE IF NOT EXISTS`	Crea una tabla, sin fallar si ya existe.
`GENERATED ALWAYS AS IDENTITY`	ID autoincremental, forma moderna recomendada.
`NOT NULL / UNIQUE / CHECK / DEFAULT`	Restricciones de columna que PostgreSQL hace cumplir siempre.
`REFERENCES tabla(columna)`	Declara una clave foránea.
`ON DELETE CASCADE/RESTRICT/SET NULL`	Qué pasa con las filas hijas al borrar el padre.
`ALTER TABLE ... ADD/DROP COLUMN`	Agrega o quita columnas de una tabla existente.
`DROP TABLE IF EXISTS ... CASCADE`	Elimina una tabla (y opcionalmente todo lo que depende de ella).

¿Sabías que...?

🐘 De dónde viene el nombre

PostgreSQL nació como "POSTGRES" en la Universidad de Berkeley en 1986, liderado por Michael Stonebraker (el mismo investigador detrás de Ingres). En 1996 el proyecto adoptó SQL como lenguaje principal y pasó a llamarse PostgreSQL.

🧩 Extensible por diseño

PostgreSQL permite crear tipos de datos, funciones e incluso índices propios. Por eso existen extensiones como PostGIS (datos geoespaciales) que convierten a Postgres en una base de datos especializada sin cambiar de motor.

🔁 Las transacciones DDL son "reales"

A diferencia de otros motores populares, en PostgreSQL puedes envolver un CREATE TABLE o ALTER TABLE dentro de una transacción y hacer ROLLBACK: si algo sale mal a mitad de un script de migración, deshacer los cambios de estructura es tan simple como deshacer un INSERT.