Proteger las reglas de inscripción a talleres

Introducción

REPAIR CIRCLE organiza talleres prácticos de reparación. Su puesto de inscripciones ya permite registrar una reserva y mostrar la lista real de inscritos. Sin embargo, la base de datos aún no rechaza las inscripciones duplicadas ni las referencias rotas. El objetivo es mantener operativo este puesto y hacer que la base de datos imponga las relaciones de las inscripciones.

Necesitas conocimientos básicos para moverte por la terminal y conocimientos básicos de SELECT, INSERT y UPDATE en MySQL. Aquí se explican las restricciones, las transacciones usadas para pruebas reversibles y ALTER TABLE. MySQL y los datos sintéticos de los talleres están preparados. Usa Terminal durante todo el laboratorio; los clientes proporcionados se conectan a la base de datos MySQL de este curso.

Puedes añadir la inscripción solicitada y modificar la estructura necesaria de registrations, guardando tu SQL en rules.sql. Conserva los ID originales, las personas, los títulos de las sesiones, los tamaños de grupo, las notas y los clientes proporcionados. Una persona puede asistir a distintas sesiones; distintas personas pueden compartir nombre. Este proyecto no limita el aforo ni gestiona la asignación concurrente de plazas.

Usar el puesto de inscripciones y detectar un duplicado

En este paso, registrarás una reserva normal, consultarás sus relaciones reales y demostrarás la regla que falta sin dejar un registro incorrecto. Empieza en Terminal. cd cambia de directorio; vuelve a usar esta ruta absoluta si entras en el directorio equivocado:

cd /home/labex/project/sign-up-desk

./desk es el cliente de inscripciones proporcionado en este directorio. ./ significa el directorio actual. roster lee las tablas reales de MySQL; no muestra una respuesta calculada de antemano:

./desk roster

Deberías ver dos inscripciones originales. La inscripción 501 relaciona a la persona 101, Mara Lin, con la sesión 11, Lamp Repair, con una plaza y la nota Window seat. La inscripción 502 relaciona a la persona 102, Alex Kim, con la sesión 12, Bike Patch, con dos plazas y Bring partner. Conserva estas relaciones y valores originales.

Una nueva solicitud pide inscribir a la persona 104, Sam Reed, en la sesión 11 con una plaza. book recibe el ID de persona, el ID de evento y el tamaño del grupo, en ese orden. Guarda esta reserva legítima:

./desk book 104 11 1

./desk roster

La tercera inscripción pertenece a Sam, no a Mara, aunque ambos asisten a Lamp Repair. El cliente devuelve el ID guardado por la base de datos. No necesitas elegir ni memorizar ese nuevo ID.

./db abre la conexión del cliente MySQL de este curso. Úsalo en lugar del comando mysql de la imagen, que no corresponde a este curso. --table solicita una salida tabular legible; -e ejecuta el SQL entre comillas y cierra la conexión. Las comillas mantienen juntos los espacios y los puntos y coma en un único argumento. Consulta las dos tablas padre:

./db --table -e "SELECT id,name FROM participants ORDER BY id; SELECT id,title FROM sessions ORDER BY id;"

Las personas 102 y 103 se llaman Alex Kim. Son personas distintas porque sus ID son diferentes. Un nombre no debe convertirse en una identidad única de inscripción. Las tres tablas tienen estas relaciones:

El negocio permite solo una inscripción por par (participant_id,session_id), con una cantidad positiva de plazas e ID padre existentes. Una reserva existente debe impedir la eliminación o el cambio del ID de la persona o sesión que referencia. Las sesiones sin uso pueden seguir eliminándose cuando sea necesario.

Antes de proteger el esquema, prueba la regla de duplicados que falta actualmente. Una transacción agrupa cambios de datos en una conexión. START TRANSACTION inicia la prueba; ROLLBACK deshace su INSERT en lugar de guardarlo. El siguiente comando único intenta deliberadamente una segunda reserva para Mara en Lamp Repair, cuenta ese par y después revierte el cambio. Las comillas que abarcan varias líneas siguen formando un único argumento del intérprete:

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,11,1);
SELECT COUNT(*) AS matching_registrations FROM registrations WHERE participant_id=101 AND session_id=11;
ROLLBACK;"

El recuento es 2: la base de datos aceptó un duplicado. Es una debilidad real, no un fallo de conexión. La prueba usa INSERT, no ALTER. Su reversión deja intactas la reserva original de Mara y la nueva solicitud de Sam:

./desk roster

Todavía debería haber tres inscripciones. Si un comando normal tiene una ruta incorrecta, vuelve con el cd absoluto. Usa ls para enumerar los archivos de este directorio. No elimines una persona, vacíes la tabla de inscripciones ni reinicies la base de datos para recuperarte de un error de escritura.

Imponer las relaciones y conservar las reservas legítimas

En este paso, guardarás un cambio de esquema, lo aplicarás a la tabla existente y demostrarás que las reservas útiles siguen funcionando mientras se rechazan las solicitudes no válidas. Empieza en el mismo directorio de Terminal.

Una restricción es una regla de la base de datos aplicada a las escrituras, incluidas las realizadas desde un cliente distinto de ./desk. Una restricción UNIQUE compuesta rechaza un par repetido sin prohibir que la misma persona asista a otra sesión. Una FOREIGN KEY conecta una columna de referencia con la clave primaria de una tabla padre. RESTRICT rechaza la eliminación o el cambio de un ID mientras una reserva referencia ese registro padre; no elimina la reserva automáticamente.

CHECK (seats > 0) rechaza el cero y los valores negativos. En SQL, NULL significa un valor ausente; un CHECK cuyo resultado es desconocido no rechaza NULL por sí solo. Por ello, NOT NULL también es necesario para las plazas y ambas referencias. MODIFY vuelve a indicar el tipo de la columna y añade ese requisito. Las tres tablas proporcionadas ya usan InnoDB, el motor de almacenamiento que impone estas relaciones.

ALTER TABLE modifica una tabla existente. MySQL confirma implícitamente los cambios de esquema: no incluyas este ALTER en una transacción de prueba esperando que ROLLBACK lo deshaga. Usamos una única instrucción ALTER sobre registros que ya son válidos, conservando sus ID y notas.

Guarda en rules.sql únicamente instrucciones ALTER TABLE ordinarias para registrations, sin calificar el nombre de la tabla con el nombre de la base de datos; USE, los comandos del intérprete y los cambios en otros objetos quedan fuera del alcance de este archivo. No añadas disparadores ni cambies las definiciones de las tablas padre. En el intérprete de Linux, > reemplaza el archivo de destino y <<'SQL' inicia un here-document literal. Pega el bloque completo, incluido el SQL final solo en una línea. No introduzcas este comando cat en un indicador de MySQL:

cat > rules.sql <<'SQL'
ALTER TABLE registrations
  MODIFY participant_id INT NOT NULL,
  MODIFY session_id INT NOT NULL,
  MODIFY seats INT NOT NULL,
  ADD CONSTRAINT one_person_event UNIQUE (participant_id, session_id),
  ADD CONSTRAINT known_person FOREIGN KEY (participant_id)
    REFERENCES participants(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT known_event FOREIGN KEY (session_id)
    REFERENCES sessions(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT positive_group CHECK (seats > 0);
SQL

Si el intérprete sigue esperando, introduce la línea final SQL que falta. Si escribiste mal el archivo, repite el bloque entero para reemplazarlo. Los nombres de las restricciones identifican estas reglas en los mensajes de error de MySQL; sus nombres exactos no son el resultado de negocio.

< proporciona el archivo guardado como entrada SQL del cliente. Aplícalo una vez:

./db < rules.sql

Si todo funciona, no se espera ninguna salida. Si el nombre de archivo era incorrecto, usa ls, corrige la ruta e inténtalo de nuevo. Si este único ALTER tiene un error de sintaxis, MySQL lo rechaza; corrige el bloque completo guardado antes de reintentar. Cuando el ALTER se ejecute correctamente, no lo apliques de nuevo: sus restricciones ya existen. Un error de restricción duplicada en una segunda ejecución no significa que se hayan perdido tus datos.

Consulta la definición real. SHOW CREATE TABLE informa de la tabla actual; \G muestra su resultado verticalmente para facilitar la lectura de la definición completa:

./db -e "SHOW CREATE TABLE registrations\G"

Busca las tres columnas de referencia y cantidad con NOT NULL, la clave UNIQUE de dos columnas, ambas relaciones FOREIGN KEY y el CHECK de plazas positivas. Los nombres o el formato pueden variar; las relaciones deben coincidir.

Ahora repite la solicitud duplicada usando el cliente normal. Se espera que este comando falle con un rechazo de la base de datos que contenga ERROR 1062. No debe crear otra fila:

./desk book 101 11 1

El fallo es un rechazo por clave duplicada, no una desconexión del servidor. La lista de inscritos sigue disponible:

./desk roster

Demuestra ambos casos de identidad permitidos en una transacción de datos temporal en el puesto real. Mara puede asistir a Bike Patch y el otro Alex Kim (103) puede asistir a la misma sesión que Alex 102. Un tamaño de grupo positivo mayor que uno también es legítimo:

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,12,3),(103,12,5);
SELECT participant_id,session_id,seats FROM registrations ORDER BY participant_id,session_id;
ROLLBACK;"

La prueba muestra cinco filas con identidades separadas. La reversión elimina solo sus dos filas nuevas de prueba. No deshace el cambio de esquema anterior ni la solicitud de Sam ya confirmada.

Comprueba directamente tres escrituras rechazadas representativas. Se espera que falle cada comando siguiente; estos comandos no contienen una transacción que reparar ni un cambio realizado con éxito que deshacer. MySQL rechaza la instrucción individual no válida. 9999 no está en la tabla padre proporcionada:

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(9999,11,1);"

Deberías recibir ERROR 1452, una referencia a un registro padre desconocido.

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(103,11,NULL);"

Deberías recibir ERROR 1048, una cantidad de plazas ausente. Reemplazar NULL por 0 infringiría el CHECK en lugar de producir una reserva válida.

./db -e "DELETE FROM sessions WHERE id=11;"

Deberías recibir ERROR 1451: Lamp Repair todavía tiene reservas, por lo que la sesión se conserva. Esto demuestra por qué eliminar el registro padre o eliminar las inscripciones en cascada sería un atajo incorrecto.

Por último, demuestra que una sesión sin reservas sigue pudiendo eliminarse en una prueba reversible:

./db --table -e "
START TRANSACTION;
DELETE FROM sessions WHERE id=13;
SELECT id,title FROM sessions ORDER BY id;
ROLLBACK;"

Solo aparecen las sesiones 11 y 12 dentro de esa transacción. La reversión restaura la sesión sin uso. Inspecciona el estado final que entregarás:

./db --table -e "SELECT id,title FROM sessions ORDER BY id;"

./desk roster

Se conservan las tres sesiones originales y quedan exactamente tres inscripciones reales: las dos originales y la nueva solicitud de Sam. Conserva rules.sql para realizar el mismo cambio estructural en otro puesto compatible. Es una migración de esquema de una sola aplicación, no una importación idempotente. Las conexiones predeterminadas imponen las claves foráneas; desactivar sus comprobaciones queda fuera de esta tarea. Las restricciones protegen las escrituras ordinarias de las aplicaciones, no frente a un administrador que desactive deliberadamente su cumplimiento.

Resumen

Mantuviste operativo el puesto de inscripciones y trasladaste sus reglas de relación a restricciones reales de MySQL. Conservaste las reservas y notas originales, aceptaste combinaciones legítimas de personas con el mismo nombre y de asistencia a distintas sesiones, rechazaste duplicados y referencias no válidas, e impediste que se eliminaran registros referenciados.