SQL
Eliminar en cascada

En la eliminación de cascadas

Supongamos que tiene una aplicación que administra salas.
Supongamos además que su aplicación funciona por cliente (inquilino).
Tienes varios clientes.
Por lo tanto, su base de datos contendrá una tabla para clientes y una para salas.

Ahora, cada cliente tiene N habitaciones.

Esto debería significar que tiene una clave externa en la tabla de su sala, que hace referencia a la tabla del cliente.

ALTER TABLE dbo.T_Room  WITH CHECK ADD  CONSTRAINT FK_T_Room_T_Client FOREIGN KEY(RM_CLI_ID)
REFERENCES dbo.T_Client (CLI_ID)
GO

Suponiendo que un cliente pasa a algún otro software, tendrá que eliminar sus datos en su software. Pero si lo haces

DELETE FROM T_Client WHERE CLI_ID = x 

Entonces obtendrás una violación de clave externa, porque no puedes eliminar al cliente cuando todavía tiene habitaciones.

Ahora tendría un código de escritura en su aplicación que elimina las habitaciones del cliente antes de que elimine al cliente. Supongamos además que, en el futuro, se agregarán muchas más dependencias de clave externa en su base de datos, ya que la funcionalidad de su aplicación se expande. Horrible. Para cada modificación en su base de datos, tendrá que adaptar el código de su aplicación en N lugares. Es posible que también tenga que adaptar el código en otras aplicaciones (por ejemplo, interfaces a otros sistemas).

Hay una mejor solución que hacerlo en tu código.
Solo puede agregar ON DELETE CASCADE a su clave externa.

ALTER TABLE dbo.T_Room  -- WITH CHECK -- SQL-Server can specify WITH CHECK/WITH NOCHECK
ADD  CONSTRAINT FK_T_Room_T_Client FOREIGN KEY(RM_CLI_ID)
REFERENCES dbo.T_Client (CLI_ID) 
ON DELETE CASCADE 

Ahora puedes decir

DELETE FROM T_Client WHERE CLI_ID = x 

y las habitaciones se eliminan automáticamente cuando se elimina el cliente.
Problema resuelto - sin cambios en el código de la aplicación.

Una palabra de advertencia: en Microsoft SQL-Server, esto no funcionará si tiene una tabla que hace referencia a sí misma. Entonces, si intentas definir una cascada de eliminación en una estructura de árbol recursiva, como esto:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation]  WITH CHECK ADD  CONSTRAINT [FK_T_FMS_Navigation_T_FMS_Navigation] FOREIGN KEY([NA_NA_UID])
REFERENCES [dbo].[T_FMS_Navigation] ([NA_UID]) 
ON DELETE CASCADE 
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation] CHECK CONSTRAINT [FK_T_FMS_Navigation_T_FMS_Navigation]
GO

no funcionará, porque el servidor Microsoft-SQL no le permite establecer una clave externa con ON DELETE CASCADE en una estructura de árbol recursiva. Una de las razones de esto es que el árbol es posiblemente cíclico, y eso podría conducir a un punto muerto.

PostgreSQL por otro lado puede hacer esto;
El requisito es que el árbol no sea cíclico.
Si el árbol es cíclico, obtendrá un error de tiempo de ejecución.
En ese caso, solo tendrá que implementar la función de eliminación usted mismo.

Una palabra de precaución:
Esto significa que ya no puede simplemente eliminar y volver a insertar la tabla de clientes, porque si lo hace, eliminará todas las entradas en "T_Room" ... (ya no hay actualizaciones que no sean delta)