Compartir a través de

  • Artículo

Restricciones de integridad referencial en cascada

Las restricciones de integridad referencial en cascada permiten definir las acciones que SQL Server lleva a cabo cuando un usuario intenta eliminar o actualizar una clave a la que apuntan las claves externas existentes.

Las cláusulas REFERENCES de las instrucciones CREATE TABLE y ALTER TABLE admiten las cláusulas ON DELETE y ON UPDATE. Las acciones en cascada también se puede definir mediante el cuadro de diálogo Relaciones de clave externa.

  • [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

  • [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

NO ACTION es el valor predeterminado si no se especifica ON DELETE u ON UPDATE.

  • ON DELETE NO ACTION
    Especifica que si se intenta eliminar una fila con una clave a la que hacen referencia las claves externas de las filas existentes en otras tablas, se produce un error y se revierte la instrucción DELETE.

  • ON UPDATE NO ACTION
    Especifica que si se intenta actualizar un valor de clave en una fila a cuya clave hacen referencia las claves externas de filas existentes en otras tablas, se produce un error y se revierte la instrucción UPDATE.

CASCADE, SET NULL y SET DEFAULT permiten la eliminación o actualización de valores de clave de modo que se pueda realizar un seguimiento de las tablas definidas para tener relaciones de clave externa en la tabla en la que se realizan las modificaciones. Si las acciones referenciales en cascada se han definido también en las tablas de destino, las acciones en cascada especificadas se aplican para las filas eliminadas o actualizadas. No se puede especificar CASCADE para ninguna de las claves externas o principales que tengan una columna timestamp.

  • ON DELETE CASCADE
    Especifica que si se intenta eliminar una fila con una clave a la que hacen referencia claves externas de filas existentes en otras tablas, todas las filas que contienen dichas claves externas también se eliminan.

  • ON UPDATE CASCADE
    Especifica que si se intenta actualizar un valor de clave de una fila a cuyo valor de clave hacen referencia claves externas de filas existentes en otras tablas, también se actualizan todos los valores que conforman la clave externa al nuevo valor especificado para la clave.

    [!NOTA]

    CASCADE no se puede especificar si una columna timestamp es parte de una clave externa o de la clave con referencia.

  • ON DELETE SET NULL
    Especifica que si se intenta eliminar una fila con una clave a la que hacen referencia las claves externas de las filas existentes de otras tablas, todos los valores que conforman la clave externa de las filas a las que se hace referencia se establecen en NULL. Todas las columnas de clave externa de la tabla de destino deben aceptar valores NULL para que esta restricción se ejecute.

  • ON UPDATE SET NULL
    Especifica que si se intenta actualizar una fila con una clave a la que hacen referencia las claves externas de las filas existentes de otras tablas, todos los valores que conforman la clave externa de las filas a las que se hace referencia se establecen en NULL. Todas las columnas de clave externa de la tabla de destino deben aceptar valores NULL para que esta restricción se ejecute.

  • ON DELETE SET DEFAULT
    Especifica que si se intenta eliminar una fila con una clave a la que hacen referencia las claves externas de las filas existentes de otras tablas, todos los valores que conforman la clave externa de las filas a las que se hace referencia se establecen como predeterminados. Todas las columnas de clave externa de la tabla de destino deben tener una definición predeterminada para que esta restricción se ejecute. Si una columna acepta valores NULL y no se ha establecido ningún valor predeterminado explícito, NULL se convierte en el valor predeterminado implícito de la columna. Todos los valores distintos de NULL que se establecen debido a ON DELETE SET DEFAULT deben tener unos valores correspondientes en la tabla principal para mantener la validez de la restricción de la clave externa.

  • ON UPDATE SET DEFAULT
    Especifica que si se intenta actualizar una fila con una clave a la que hacen referencia las claves externas de las filas existentes de otras tablas, todos los valores que conforman la clave externa de la fila a los que se hace referencia se establecen en sus valores predeterminados. Todas las columnas externas de la tabla de destino deben tener una definición predeterminada para que esta restricción se ejecute. Si una columna se convierte en NULL, y no hay establecido ningún valor predeterminado explícito, NULL deviene el valor predeterminado implícito de la columna. Todos los valores no NULL que se establecen debido a ON UPDATE SET DEFAULT deben tener unos valores correspondientes en la tabla principal para mantener la validez de la restricción de clave externa.

Tome como ejemplo la restricción FK_ProductVendor_Vendor_VendorID de la tabla Purchasing.ProductVendor en AdventureWorks. Esta restricción establece una relación de clave externa desde la columna VendorID de la tabla ProductVendor hasta la columna de clave principal VendorID de la tabla Purchasing.Vendor. Si se especifica ON DELETE CASCADE para la restricción, la eliminación de la fila en Vendor, donde VendorID equivale a 100, también elimina las tres filas en ProductVendor, donde VendorID equivale a 100. Si se especifica ON UPDATE CASCADE para la restricción, la actualización del valor VendorID de la tabla Vendor de 100 a 155 también actualiza los valores VendorID en las tres filas de ProductVendor cuyos valores VendorID actuales sean 100.

ON DELETE CASCADE no se puede especificar en una tabla que tenga un desencadenador INSTEAD OF DELETE. En las tablas que tienen desencadenadores INSTEAD OF UPDATE no es posible especificar lo siguiente: ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL y ON UDATE SET DEFAULT.

Múltiples acciones en cascada

Las instrucciones individuales DELETE o UPDATE pueden iniciar una serie de acciones referenciales en cascada. Por ejemplo, una base de datos contiene tres tablas: TableA, TableB y TableC.Se define una clave externa en TableB con ON DELETE CASCADE en relación con la clave principal de TableA. Se define una clave externa en TableC con ON DELETE CASCADE en relación con la clave principal de TableB. Si una instrucción DELETE elimina filas de TableA, la operación también elimina cualquier fila de TableB que contenga claves externas coincidentes con las claves principales eliminadas de TableA y, a continuación, elimina cualquier fila de TableC con claves externas que coincidan con las claves principales eliminadas de TableB.

Las series de acciones referenciales en cascada desencadenadas por DELETE o UPDATE deben formar un árbol que no contenga referencias circulares. Ninguna tabla puede aparecer más de una vez en la lista de todas las acciones referenciales en cascada que se obtienen de DELETE o UPDATE. Además, el árbol de acciones referenciales en cascada no puede tener más de una ruta de acceso a una tabla determinada. Las ramas del árbol se terminan cuando encuentran una tabla para la que se haya especificado NO ACTION o cuando éste sea su valor predeterminado.

Desencadenadores y acciones referenciales en cascada

Las acciones referenciales en cascada activan los desencadenadores AFTER UPDATE o AFTER DELETE de la siguiente manera:

Primero se realizan todas las acciones referenciales en cascada generadas directamente por las instrucciones DELETE o UPDATE originales.

Si se ha definido algún desencadenador AFTER en las tablas afectadas, estos desencadenadores se activan una vez realizadas todas las acciones en cascada. Estos desencadenadores se activan en el orden contrario a la acción en cascada. Si hay varios desencadenadores en una sola tabla, se activan en un orden aleatorio a no ser que el primer o último desencadenador de la tabla sea un desencadenador dedicado. Este orden se especifica mediante sp_settriggerorder.

Si varias cadenas en cascada se originan desde la tabla que era el objetivo directo de una acción UPDATE o DELETE, el orden en que estas cadenas activan sus respectivos desencadenadores no está especificado. Sin embargo, una cadena siempre activa todos sus desencadenadores antes que otra cadena inicie la activación.

Un desencadenador AFTER en la tabla que es el objetivo directo de una acción UPDATE o DELETE se activa independientemente de si afecta a alguna fila. En este caso, ninguna otra tabla se ve afectada por la cascada.

Si alguno de los desencadenadores anteriores realiza operaciones UPDATE o DELETE en otras tablas, estas acciones pueden iniciar cadenas en cascada secundarias. Estas cadenas secundarias se procesan a la vez para cada operación UPDATE o DELETE una vez activados todos los desencadenadores de todas las cadenas principales. Este proceso puede repetirse recursivamente para las operaciones UPDATE o DELETE posteriores.

Realizar CREATE, ALTER, DELETE u otras operaciones de lenguaje de definición de datos (DDL) dentro de los desencadenadores puede causar la activación de los desencadenadores DDL. Esto puede hacer que se lleven a cabo operaciones DELETE o UPDATE que inician cadenas y desencadenadores en cascada adicionales.

Si se genera un error en una cadena de acción referencial en cascada determinada, se produce un error, no se activa ningún desencadenador AFTER en esa cadena y la operación DELETE o UPDATE que ha creado la cadena se revierte.

Una tabla con un desencadenador INSTEAD OF no puede tener también una cláusula REFERENCES que especifique un acción en cascada. Sin embargo, un desencadenador AFTER de la tabla de destino de una acción en cascada puede ejecutar una instrucción INSERT, UPDATE o DELETE en otra tabla o vista que active un desencadenador INSTEAD OF definido para dicho objeto.

Información de catálogo de restricciones referenciales en cascada

La consulta de la vista de catálogo sys.foreign_keys devuelve los siguientes valores que indican la restricción referencial en cascada especificada para una clave externa.

Valor

Descripción

0

NO ACTION

1

CASCADE

2

SET NULL

3

SET DEFAULT

Las columnas UPDATE_RULE y DELETE_RULE devueltas por sp_fkeys y sp_foreignkeys devuelven 0 cuando se especifica CASCADE, SET NULL o SET DEFAULT y devuelven 1 cuando se especifica NO ACTION o es el valor predeterminado.

Cuando se especifica una clave externa como el objeto de sp_help, el conjunto de resultados de salida contiene las siguientes columnas.

Nombre de columna

Tipo de datos

Descripción

delete_action

nvarchar(9)

Indica si la acción de eliminación es CASCADE, SET NULL, SET DEFAULT, NO ACTION o N/A (no aplicable).

update_action

nvarchar(9)

Indica si la acción de actualización es CASCADE, SET NULL, SET DEFAULT, NO ACTION o N/A (no aplicable).