MySQL ON DELETE CASCADE
Resumen: en este tutorial, aprenderá a utilizar la acción referencial de MySQL ON DELETE CASCADE para una clave foránea para eliminar datos de múltiples tablas relacionadas.
En el tutorial anterior, aprendió a eliminar datos de múltiples tablas relacionadas utilizando una única sentencia DELETE. Sin embargo, MySQL proporciona una forma más efectiva llamada ON DELETE CASCADE acción referencial para una clave foránea que le permite eliminar los datos de las tablas hijas automáticamente cuando se eliminan los datos de la tabla padre.
Ejemplo de MySQL ON DELETE CASCADE
Veamos un ejemplo de uso de MySQL ON DELETE CASCADE .
Supongamos que tenemos dos tablas:buildings y rooms . En este modelo de base de datos, cada edificio tiene una o varias habitaciones. Sin embargo, cada habitación pertenece a un solo edificio. Una habitación no existiría sin un edificio.
La relación entre las tablas buildings y rooms es de uno a muchos (1:N) como se ilustra en el siguiente diagrama de la base de datos:
Cuando se elimina una fila de la tabla buildings, también se quieren eliminar todas las filas de la tabla rooms que hacen referencia a la fila de la tabla buildings. Por ejemplo, cuando se borra una fila con el edificio no. 2 en la tabla buildings como la siguiente consulta:
Code language: SQL (Structured Query Language) (sql)
También quiere que las filas en la tabla rooms que hace referencia al edificio número 2 también se eliminen.
Los siguientes son pasos que demuestran cómo funciona la acción referencial ON DELETE CASCADE.
Paso 1. Crear la tabla buildings:
Code language: SQL (Structured Query Language) (sql)
Paso 2. Crear la tabla rooms:
Code language: SQL (Structured Query Language) (sql)
Notar que la cláusula ON DELETE CASCADE al final de la definición de la restricción de clave foránea.
Paso 3. Insertar filas en la tabla buildings:
Code language: SQL (Structured Query Language) (sql)
Paso 4. Consultar los datos de la tabla buildings:
Code language: SQL (Structured Query Language) (sql)
Tenemos dos filas en la tabla buildings.
Paso 5. Insertar filas en la tabla rooms:
Code language: SQL (Structured Query Language) (sql)
Paso 6. Consultar los datos de la tabla rooms:
Code language: SQL (Structured Query Language) (sql)
Tenemos tres habitaciones que pertenecen al edificio nº 1 y dos habitaciones que pertenecen al edificio nº 2.
Paso 7. Borrar el edificio con el edificio no. 2:
Code language: SQL (Structured Query Language) (sql)
Paso 8. Consultar los datos de la tabla rooms:
Code language: SQL (Structured Query Language) (sql)
Como se puede ver, todas las filas que hacen referencia a building_no 2 se eliminaron automáticamente.
Nótese que ON DELETE CASCADE funciona sólo con las tablas con los motores de almacenamiento que soportan claves extranjeras por ejemplo, InnoDB.
Algunos tipos de tablas no soportan claves foráneas como MyISAM por lo que debe elegir los motores de almacenamiento apropiados para las tablas que planea utilizar la acción referencial MySQL ON DELETE CASCADE.
Consejos para encontrar las tablas afectadas por la acción MySQL ON DELETE CASCADE
A veces, es útil saber qué tabla está afectada por la acción referencial ON DELETE CASCADE cuando se eliminan datos de una tabla. Puede consultar estos datos desde el referential_constraints en la base de datos information_schema de la siguiente manera:
Code language: SQL (Structured Query Language) (sql)
Por ejemplo, para encontrar las tablas que se asocian a la tabla buildings con la regla de borrado CASCADE en la base de datos classicmodels, se utiliza la siguiente consulta:
Code language: SQL (Structured Query Language) (sql)
