Claves primarias, foráneas y relaciones entre tablas en MySQL

Hola, ¿cómo están? Continuando con el curso de MySQL, hoy conectamos las tablas que creamos en la lección anterior. Aquí es donde una base de datos deja de ser un conjunto de listas y se vuelve relacional de verdad.

¡Empecemos!

La clave primaria

Es la columna que identifica de forma única cada fila de una tabla.

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

Tres reglas que MySQL impone:

• No puede repetirse.
• No puede ser NULL.
• Solo hay una por tabla, aunque puede estar formada por varias columnas.

Por qué un id numérico y no la cédula

Es una duda razonable: si la cédula ya identifica a una persona, ¿por qué crear un id aparte?

Por tres motivos prácticos:

1. Los datos del mundo real cambian. Una cédula puede corregirse por un error de digitación, y si es clave primaria hay que actualizarla en todas las tablas que la referencian.
2. Ocupa menos. Un INT son 4 bytes; un VARCHAR(20), hasta 21. Multiplícalo por cada clave foránea de cada tabla.
3. Es más rápido. Comparar enteros es más eficiente que comparar cadenas.

Lo correcto es tener el id como clave primaria y la cédula como UNIQUE:

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
cedula VARCHAR(20) NOT NULL UNIQUE

Así tienes las dos garantías: identificación estable y sin cédulas duplicadas.

La clave foránea

Es una columna que apunta a la clave primaria de otra tabla, y es lo que materializa la relación.

ALTER TABLE doctores
ADD CONSTRAINT fk_doctores_especialidad
FOREIGN KEY (especialidad_id) REFERENCES especialidades(id);

O al crear la tabla:

CREATE TABLE doctores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    especialidad_id INT UNSIGNED NOT NULL,
    CONSTRAINT fk_doctores_especialidad
        FOREIGN KEY (especialidad_id) REFERENCES especialidades(id)
) ENGINE=InnoDB;

Darle nombre a la restricción con CONSTRAINT no es obligatorio, pero hazlo siempre: cuando algo falle, el mensaje de error dirá fk_doctores_especialidad en lugar de un identificador generado que no dice nada.

Requisitos para que funcione

• Ambas tablas deben usar InnoDB.
• Las columnas deben tener exactamente el mismo tipo. Si una es INT UNSIGNED, la otra también. Es la causa número uno del error «Cannot add foreign key constraint».
• La columna referenciada debe estar indexada, cosa que la clave primaria ya cumple.

La integridad referencial

Con la clave foránea declarada, MySQL empieza a protegerte:

-- Falla: no existe la especialidad 99
INSERT INTO doctores (nombre, especialidad_id) VALUES ('Ana', 99);

-- Falla: hay doctores con esa especialidad
DELETE FROM especialidades WHERE id = 1;

Esto es exactamente lo que quieres. El motor impide que los datos queden incoherentes, incluso si tu aplicación tiene un fallo.

ON DELETE y ON UPDATE

Ahora bien, ¿qué debería pasar cuando se borra un registro referenciado? Tú decides:

FOREIGN KEY (especialidad_id) REFERENCES especialidades(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE

Las opciones:

Acción	Qué hace al borrar el registro padre
RESTRICT	Impide el borrado. Es el valor por defecto
CASCADE	Borra también los registros hijos
SET NULL	Pone la clave foránea en NULL
NO ACTION	Igual que RESTRICT en MySQL

Cuál elegir

Esta decisión importa mucho más de lo que parece.

RESTRICT por defecto. Es el más seguro: obliga a resolver la situación conscientemente. Si intentas borrar una especialidad que tiene doctores, MySQL te frena.

CASCADE solo cuando el hijo no tiene sentido sin el padre. El caso claro es una tabla intermedia: si borras un pedido, sus líneas de detalle no significan nada por sí solas.

Cuidado con CASCADE en cadena. Si clientes cascadea a pedidos, y pedidos cascadea a lineas, borrar un cliente elimina silenciosamente cientos de filas. Puede ser lo correcto, pero tiene que ser una decisión, no un descubrimiento.

SET NULL requiere que la columna admita NULL, y suele usarse cuando la relación es opcional: si se borra el doctor, la cita queda sin doctor asignado en lugar de desaparecer.

Una alternativa que se usa mucho en aplicaciones reales es el borrado lógico: en vez de eliminar el registro, una columna activo o eliminado_en lo marca. Así nunca pierdes historial.

Las relaciones del sistema

Apliquémoslo a nuestra clínica:

ALTER TABLE doctores
ADD CONSTRAINT fk_doctores_especialidad
FOREIGN KEY (especialidad_id) REFERENCES especialidades(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

ALTER TABLE citas
ADD CONSTRAINT fk_citas_paciente
FOREIGN KEY (paciente_id) REFERENCES pacientes(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

ALTER TABLE citas
ADD CONSTRAINT fk_citas_doctor
FOREIGN KEY (doctor_id) REFERENCES doctores(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

Con RESTRICT en las tres, nadie puede borrar un paciente que tiene historial de citas. Que es justo lo que debe pasar en un sistema médico.

Relaciones muchos a muchos

Como vimos, este tipo de relación necesita una tabla intermedia. Supongamos que una cita puede incluir varios estudios médicos:

CREATE TABLE estudios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE cita_estudio (
    cita_id INT UNSIGNED NOT NULL,
    estudio_id INT UNSIGNED NOT NULL,
    resultado TEXT NULL,
    realizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (cita_id, estudio_id),

    CONSTRAINT fk_ce_cita FOREIGN KEY (cita_id)
        REFERENCES citas(id) ON DELETE CASCADE,
    CONSTRAINT fk_ce_estudio FOREIGN KEY (estudio_id)
        REFERENCES estudios(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

Tres cosas que fijarse aquí:

La clave primaria compuesta PRIMARY KEY (cita_id, estudio_id) impide que se registre dos veces el mismo estudio en la misma cita. La combinación es la que debe ser única.

La tabla intermedia lleva datos propios: el resultado y la fecha. No pertenecen ni a la cita ni al estudio, sino a la combinación de ambos.

El CASCADE hacia citas es correcto aquí. Si se borra la cita, sus estudios asociados no tienen sentido. Hacia estudios en cambio va RESTRICT: no queremos que borrar un tipo de estudio del catálogo elimine el historial.

Consultar las relaciones existentes

-- Ver las claves foráneas de una tabla
SHOW CREATE TABLE citas;

-- Listar todas las relaciones de la base
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'clinica'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Esa segunda consulta es muy útil al llegar a un proyecto heredado: te da el mapa completo de relaciones en una sola vista.

Eliminar una clave foránea

ALTER TABLE doctores DROP FOREIGN KEY fk_doctores_especialidad;

Aquí se nota la ventaja de haberle puesto nombre: sin él, tendrías que buscarlo primero en SHOW CREATE TABLE.

Errores comunes

• Tipos que no coinciden entre la clave foránea y la primaria. Revisa el UNSIGNED.
• Usar MyISAM, que ignora las claves foráneas sin avisar.
• No nombrar las restricciones, y quedarte con errores indescifrables.
• CASCADE por comodidad, y borrar más de lo que esperabas.
• Usar un dato del mundo real como clave primaria.
• Crear las tablas en orden incorrecto: la referenciada debe existir antes.

Para cerrar

Las claves foráneas son la diferencia entre una base de datos y un conjunto de tablas sueltas. El motor se convierte en tu aliado: impide que existan pedidos sin cliente o citas sin paciente, pase lo que pase en la aplicación.

Y la decisión que más conviene pensar es ON DELETE. RESTRICT como norma, CASCADE solo cuando el hijo no tenga sentido sin el padre.

En la siguiente lección empezamos a llenar estas tablas con datos.

Saludos y éxitos.