Comprendiendo las claves ajenas (Foreign keys) en MySQL

Obtenido de WikiMedia Commons

Las claves ajenas son un concepto fundamental dentro del modelo relacional de base de datos. Definidas de manera muy básica una clave ajena es uno o varios campos  de una tabla que hacen referencia a campos de otras tablas y que nos permiten relacionarlas.

Si buscamos una definición formal podemos usar la de Wikipedia pero vamos a intentar explicar el concepto de una manera más práctica con un ejemplo.

Model Relacional - Relación 1:N

Model Relacional – Relación 1:N

En este caso tenemos un relación 1:N entre “clients” y “vehicles”. Un cliente puede tener uno o varios vehículos y un vehículo puede ser de 0 o 1 clientes (supón que estamos en un taller y el coche no es de ningún cliente si no que procede de la chatarra).

La clave ajena será “clientCod” en la tabla “vehicles” y hace referencia al campo “clientCod” de la tabla “clients” que, por otro lado, es clave primaria en esa tabla. Estos dos campos, la clave ajena y la clave primaria, nos sirven para establecer la relación entre las dos tablas y debemos de tener en cuenta que en la tabla “vehicles” no va a haber ningún valor de “clientCod” que no esté en la tabla de clientes. Es lo que se llama integridad referencial.

Pero, ¿qué pasa si borramos o actualizamos los datos de un cliente de la tabla “clients”? ¿cómo afecta esto a la tabla “vehicles”?. Tenemos varias opciones y MySQL nos da 5 posibilidades:

  • RESTRICT : Es la opción por defecto y no permitirá la actualización del “clientCod” de un cliente o el borrado de ese cliente si existe un vehículo que pertenezca a ese cliente.
  • NO ACTION: Es equivalente a RESTRICT y se mantiene por respeto al estándar SQL.
  • CASCADE : Si borro un cliente se borrarán los vehículos de ese cliente y si actualizo el “clientCod” de un cliente se propagará esta actualización al “clientCod” de los vehículos de ese cliente.
  • SET NULL: Si borro un cliente  se actualizará el “clientCod” de los vehículos de ese cliente y este tomará un valor de NULL (debe ser posible que ese campo tome valores nulos) y si lo actualizo sucederá lo mismo, se pondrá un valor NULL.
  • SET DEFAULT: Pondría un valor por defecto en los campos “clientCod” de la tabla “vehicles” al actualizar o borrar clientes. No es soportado por los tipos de tablas InnoDB y NDB.

Para verlo mejor vamos ver RESTRICT, NO ACTION , CASCADE y SET NULL con un ejemplo concreto.

EL SQL para crear las tablas sería el siguiente:

Vemos que en la líneas donde está ON DELETE / ON UPDATE podemos elegir una de las opciones con las que vamos a trabajar.

Las consultas con las que trabajar para la demostración serán las siguientes:

El contenido inicial para las tablas va a ser el siguiente en todos los casos:

Contenido de la tabla clientes

Contenido de la tabla clientes

Contenido de la tabla vehículos

Contenido de la tabla vehículos

Si intento las operaciones y he usado en la creación de la tabla ON DELETE RESTRICT y ON UPDATE RESTRICT (son las opciones por defecto) o ON DELETE NO ACTION y ON UPDATE NO ACTION  obtendré en ambos casos (delete y update) el siguiente mensaje de error:

Si intento las operaciones y he usado en la creación de la tabla ON DELETE CASCADE y ON UPDATE CASCADE se borrarán todos los vehículos del cliente “00007” y en aquellos dónde el código del cliente sea “00008” se cambiará por “11111”. Podemos ver como quedan las tablas:

Tabla clientes con operaciones en cascada

Tabla clientes con operaciones CASCADE

Tabla vehículos con operaciones en cascada

Tabla vehículos con operaciones CASCADE

Si intento las operaciones y he usado en la creación de la tabla ON DELETE SET NULL y ON UPDATE SET NULL la tabla clientes quedará igual que en el caso anterior pero en la tabla vehículos el campo “codClient” será NULL para aquellos coche que eran del cliente “00007” o “00008”. Podemos ver como quedaría en la siguiente imagen:

Tabla vehículos con la opción SET NULL

Tabla vehículos con la opción SET NULL

Elegir una u otra opción es una decisión de diseño que debe de ponderarse antes de la creación de la base de datos.

En el próximo tutorial hablaremos de otro concepto básico pero que a veces resulta difícil para los estudiantes: “los diferentes tipos de Joins”