eliminar registro de cascada

 eliminar registro de cascada 

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) 

aqui un video de como eliminar 

 eliminar y actulizar cascada 

En este artículo, vamos a repasar las reglas ELIMINAR EN CASCADA y ACTUALIZAR EN CASCADA en la clave externa de SQL Server con diferentes ejemplos.

ELIMINAR EN CASCADA: Cuando creamos una clave foránea utilizando esta opción, elimina las filas de referencia en la tabla secundaria cuando la fila referenciada se elimina en la tabla primaria que tiene una clave primaria.

ACTUALIZAR CASCADA: cuando creamos una clave externa utilizando ACTUALIZAR CASCADA, las filas de referencia se van a actualizar en la tabla secundaria cuando la fila referenciada se actualiza en la tabla principal que tiene una clave primaria.

Discutiremos los siguientes temas en este artículo:

1. Creación de la regla ELIMINAR y ACTUALIZAR CASCADA en una clave externa usando el estudio de administración de SQL Server
2. Crear la regla ELIMINAR y ACTUALIZAR CASCADA en una clave foránea utilizando el script T-SQL
3. Se lanzará en una tabla con una clave externa en cascada ELIMINAR o ACTUALIZAR

Veamos cómo poder crear una clave foránea con las reglas ELIMINAR y ACTUALIZAR CASCADA junto con algunos ejemplos.

Crear una clave foránea con las reglas ELIMINAR y ACTUALIZAR CASCADA

Usando la Guía de SQL Server Management Studio:

Inicie sesión en SQL Server con SQL Server Management Studio, diríjase a la carpeta Claves (Keys) en la tabla secundaria. Haga clic derecho en la carpeta Claves (Keys) y seleccione Nueva clave externa.

Edite la especificación de tablas y columnas haciendo clic en… como se muestra en la siguiente imagen.

Seleccione la tabla primaria y la columna de clave principal en la tabla primaria. Seleccione la columna de clave externa en la tabla secundaria. Haga clic en OK y consulte la imagen que se muestra a continuación.

En las especificaciones INSERTAR y ACTUALIZAR, seleccione Cascada para la regla de eliminación.

Haga clic en Cerrar y guarde la tabla en el diseñador. Haga clic en Sí en la ventana del mensaje de advertencia.

Una vez que se haga clic en Sí, se va a crear una clave externa con la regla de eliminación. De manera similar, podemos crear una clave foránea con la regla ACTUALIZAR CASCADA, seleccionando CASCADA como una acción para la regla de actualización en las especificaciones INSERTAR y ACTUALIZAR.

Usando T-SQL:

Consulte el siguiente script T-SQL, la cual crea una tabla principal, secundaria y una clave externa en la tabla secundaria con la regla ELIMINAR EN CASCADA.

CREATE TABLE Countries   (CountryID INT PRIMARY KEY, CountryName VARCHAR(50), CountryCode VARCHAR(3))     CREATE TABLE States   (StateID INT PRIMARY KEY, StateName VARCHAR(50), StateCode VARCHAR(3), CountryID INT)       ALTER TABLE [dbo].[States]  WITH CHECK ADD  CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryID]) REFERENCES [dbo].[Countries] ([CountryID]) ON DELETE CASCADE GO   ALTER TABLE [dbo].[States] CHECK CONSTRAINT [FK_States_Countries] GO

Inserte algunos datos de muestra utilizando el script T-SQL a continuación.

INSERT INTO Countries VALUES (1,'United States','USA')   INSERT INTO Countries VALUES (2,'United Kingdom','UK')   INSERT INTO States VALUES (1,'Texas','TX',1) INSERT INTO States VALUES (2,'Arizona','AZ',1)

Ahora eliminé una fila en la tabla primaria con CountryID = 1 que a su vez también elimina las filas en la tabla secundaria que tiene CountryID = 1.

Ahora consulte el siguiente script T-SQL, para poder crear una clave foránea con la regla ACTUALIZAR CASCADA.

CREATE TABLE Countries   (CountryID INT PRIMARY KEY, CountryName VARCHAR(50), CountryCode VARCHAR(3))     CREATE TABLE States   (StateID INT PRIMARY KEY, StateName VARCHAR(50), StateCode VARCHAR(3), CountryID INT)   GO   INSERT INTO Countries VALUES (1,'United States','USA')   INSERT INTO Countries VALUES (2,'United Kingdom','UK')   INSERT INTO States VALUES (1,'Texas','TX',1) INSERT INTO States VALUES (2,'Arizona','AZ',1)   GO     ALTER TABLE [dbo].[States]  WITH CHECK ADD  CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryID]) REFERENCES [dbo].[Countries] ([CountryID]) ON UPDATE CASCADE GO   ALTER TABLE [dbo].[States] CHECK CONSTRAINT [FK_States_Countries] GO

Ahora actualice CountryID en los países para una fila que también actualice las filas de referencia en los estados de la tabla secundaria.

UPDATE Countries SET CountryID =3 where CountryID=1

El siguiente es el script T-SQL que va a crear una clave foránea en cascada, como reglas ACTUALIZAR y ELIMINAR.

ALTER TABLE [dbo].[States]  WITH CHECK ADD  CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryID]) REFERENCES [dbo].[Countries] ([CountryID]) ON UPDATE CASCADE ON DELETE CASCADE GO   ALTER TABLE [dbo].[States] CHECK CONSTRAINT [FK_States_Countries] GO

Para poder conocer las acciones de actualización y eliminación en la clave externa, consulte la vista sys.foreign_keys. Reemplace el nombre de restricción en el script.

SELECT name,delete_referential_action,delete_referential_action_desc,update_referential_action,update_referential_action_desc FROM sys.foreign_keys where name ='FK_States_Countries'

La siguiente imagen nos muestra que una acción de ELIMINAR EN CASCADA y ninguna acción de ACTUALIZAR están definidas en la clave externa.

Avancemos y verifiquemos el comportamiento de las reglas de eliminación y actualización de las claves externas en una tabla secundaria que actúa como tabla primaria para otra tabla secundaria. El siguiente ejemplo demuestra este escenario.

En este caso, “Países” es la tabla principal de la tabla “Estados” y la tabla “Estados” es la tabla principal de la tabla Ciudades.

Ahora vamos a crear una clave externa en cascada como regla de eliminación en la tabla de Estados que hace referencia a CountryID en la tabla principal Países.

CREATE TABLE Countries   (CountryID INT PRIMARY KEY, CountryName VARCHAR(50), CountryCode VARCHAR(3))     CREATE TABLE States   (StateID INT PRIMARY KEY, StateName VARCHAR(50), StateCode VARCHAR(3), CountryID INT)   GO     CREATE TABLE Cities (CityID INT, CityName varchar(50), StateID INT) GO   INSERT INTO Countries VALUES (1,'United States','USA')   INSERT INTO Countries VALUES (2,'United Kingdom','UK')   INSERT INTO States VALUES (1,'Texas','TX',1) INSERT INTO States VALUES (2,'Arizona','AZ',1)   INSERT INTO Cities VALUES(1,'Texas City',1) INSERT INTO Cities values (1,'Phoenix',2)   GO     ALTER TABLE [dbo].[States]  WITH CHECK ADD  CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryID]) REFERENCES [dbo].[Countries] ([CountryID]) ON DELETE CASCADE GO

Ahora en la tabla Ciudades vamos a crear una clave foránea sin una regla de ELIMINAR EN CASCADA.

ALTER TABLE [dbo].[Cities]  WITH CHECK ADD  CONSTRAINT [FK_Cities_States] FOREIGN KEY([StateID]) REFERENCES [dbo].[States] ([StateID]) GO

Si intentamos eliminar un registro con CountryID = 1, arrojará un error ya que al eliminar en la tabla principal “Países” intenta eliminar las filas de referencia en los de la tabla secundaria Estados. Pero en la tabla Ciudades”, tenemos una restricción de clave externa sin acción para eliminar y el valor referenciado todavía existe en la tabla.

DELETE FROM Countries where CountryID =1

La eliminación falla en la segunda clave externa.

Cuando creamos la segunda clave externa en cascada como regla de eliminación, el comando de eliminación anterior se ejecuta con éxito al eliminar registros en la tabla secundaria “Estados” que a su vez elimina registros en la segunda tabla secundaria “Ciudades“.

CREATE TABLE Countries   (CountryID INT PRIMARY KEY, CountryName VARCHAR(50), CountryCode VARCHAR(3))     CREATE TABLE States   (StateID INT PRIMARY KEY, StateName VARCHAR(50), StateCode VARCHAR(3), CountryID INT)   GO     CREATE TABLE Cities (CityID INT, CityName varchar(50), StateID INT) GO   INSERT INTO Countries VALUES (1,'United States','USA')   INSERT INTO Countries VALUES (2,'United Kingdom','UK')   INSERT INTO States VALUES (1,'Texas','TX',1) INSERT INTO States VALUES (2,'Arizona','AZ',1)   INSERT INTO Cities VALUES(1,'Texas City',1) INSERT INTO Cities values (1,'Phoenix',2)   GO     ALTER TABLE [dbo].[States]  WITH CHECK ADD  CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryID]) REFERENCES [dbo].[Countries] ([CountryID]) ON DELETE CASCADE GO     ALTER TABLE [dbo].[Cities]  WITH CHECK ADD  CONSTRAINT [FK_Cities_States] FOREIGN KEY([StateID]) REFERENCES [dbo].[States] ([StateID]) ON DELETE CASCADE GO   DELETE FROM Countries where CountryID =1

Se desencadena en una tabla con la eliminación en cascada o la actualización de la clave externa en cascada

No se puede crear un desencadenador o trigger en lugar de actualizar en la tabla, si ya existe una clave externa activada con ACTUALIZAR CASCADA en la tabla. Nos lanza un error: “No se puede crear EN VEZ DE ELIMINAR o EN VEZ DE ACTUALIZAR “nombre de lanzamiento” en la tabla “nombre de tabla”. Esto se debe a que la tabla tiene una CLAVE EXTRERNA con ELIMINAR o ACTUALIZAR en cascada”.

Del mismo modo, no podemos crear un desencadenador EN VEZ DE ELIMINAR en la tabla cuando ya existe una regla de CASCADE DELETE de clave externa en la tabla.

Conclusión

En este artículo, exploramos algunos ejemplos sobre las reglas ELIMINAR EN CASCADA y ACTUALIZAR CASCADA en la clave externa de SQL Server. En caso de que tenga alguna duda o consulta, no dude en preguntar en la sección de comentarios a continuación.

Consulte este artículo,la clave extrema del SQL server para poder obtener más detalles sobre las reglas de eliminación y actualización en la clave externa de SQL Server.