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:
- Creación de la regla ELIMINAR y ACTUALIZAR CASCADA en una clave externa usando el estudio de administración de SQL Server
- Crear la regla ELIMINAR y ACTUALIZAR CASCADA en una clave foránea utilizando el script T-SQL
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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.
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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.
|
1 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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.
|
1 2 3 |
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.
|
1 |
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“.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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 externa de SQL Server para poder obtener más detalles sobre las reglas de eliminación y actualización en la clave externa de SQL Server.
- ELIMINAR EN CASCADA y ACTUALIZAR CASCADA en la clave externa de SQL Server - December 6, 2019