• Article

Contraintes d'intégrité référentielle en cascade

Les contraintes d'intégrité référentielle en cascade définissent les actions exécutées par SQL Server lorsqu'un utilisateur tente de supprimer ou de mettre à jour une clé vers laquelle pointent des clés étrangères existantes.

Les clauses REFERENCES des instructions CREATE TABLE et ALTER TABLE prennent en charge les clauses ON DELETE et ON UPDATE. Les actions en cascade peuvent également être définies à l'aide de la boîte de dialogue Relations de clé étrangère :

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

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

NO ACTION est la valeur par défaut si la clause ON DELETE ou ON UPDATE n'est pas spécifiée.

  • ON DELETE NO ACTION
    Spécifie qu'en cas de tentative de suppression d'une ligne possédant une clé référencée par des clés étrangères dans des lignes d'autres tables, une erreur est générée et l'instruction DELETE est restaurée.

  • ON UPDATE NO ACTION
    Prescrit qu'en cas de tentative de mise à jour d'une valeur de clé dans une ligne dont la clé est référencée par des clés étrangères dans des lignes d'autres tables, une erreur est générée et l'instruction UPDATE est restaurée.

CASCADE, SET NULL et SET DEFAULT permettent la suppression ou la mise à jour de valeurs de clé pour affecter les tables dont les relations de clés étrangères trouvent leur source dans la table faisant l'objet de la modification initiale. Si des actions d'intégrité référentielle en cascade ont également été définies sur les tables cible, ces actions s'appliquent également pour les lignes à supprimer ou à mettre à jour. CASCADE ne peut être spécifié pour aucune clé étrangère ou clé primaire comprenant une colonne timestamp.

  • ON DELETE CASCADE
    Indique qu'en cas de tentative de suppression d'une ligne possédant une clé référencée par des clés étrangères dans des lignes d'autres tables, toutes les lignes contenant ces clés étrangères sont également supprimées.

  • ON UPDATE CASCADE
    Spécifie qu'en cas de tentative de mise à jour, dans une ligne, d'une valeur de clé référencée par des clés étrangères dans des lignes d'autres tables, toutes les valeurs qui composent la clé étrangère sont également mises à jour et remplacées par la nouvelle valeur spécifiée pour la clé.

    Notes

    La valeur CASCADE ne peut pas être spécifiée si une colonne timestamp fait partie de la clé étrangère ou de la clé référencée.

  • ON DELETE SET NULL
    Spécifie qu'en cas de tentative de suppression d'une ligne possédant une clé référencée par des clés étrangères dans des lignes d'autres tables, toutes les valeurs qui composent la clé étrangère des lignes référencées prennent la valeur NULL. Cette contrainte ne peut être exécutée que si toutes les colonnes de clé étrangère de la table cible acceptent des valeurs NULL.

  • ON DELETE SET NULL
    Spécifie qu'en cas de tentative de mise à jour d'une ligne possédant une clé référencée par des clés étrangères dans des lignes d'autres tables, toutes les valeurs qui composent la clé étrangère des lignes référencées prennent la valeur NULL. Cette contrainte ne peut être exécutée que si toutes les colonnes de clé étrangère de la table cible acceptent des valeurs NULL.

  • ON DELETE SET DEFAULT
    Spécifie qu'en cas de tentative de suppression d'une ligne possédant une clé référencée par des clés étrangères dans des lignes d'autres tables, toutes les valeurs qui composent la clé étrangère des lignes référencées prennent leur valeur par défaut. Cette contrainte ne peut être exécutée que si toutes les colonnes de clé étrangère de la table cible possèdent une définition par défaut. Si une colonne accepte des valeurs NULL et qu'aucune valeur par défaut explicite n'est définie, NULL devient la valeur par défaut implicite de la colonne. La validité de la contrainte de clé étrangère ne peut être conservée que s'il existe dans la table primaire des valeurs correspondant aux valeurs non NULL définies en raison de l'action ON DELETE SET DEFAULT.

  • ON DELETE SET DEFAULT
    Spécifie qu'en cas de tentative de mise à jour d'une ligne possédant une clé référencée par des clés étrangères dans des lignes d'autres tables, toutes les valeurs qui composent la clé étrangère des lignes référencées prennent leur valeur par défaut. Cette contrainte ne peut être exécutée que si toutes les colonnes de clé étrangère de la table cible possèdent une définition par défaut. Si une colonne accepte des valeurs NULL et qu'aucune valeur par défaut explicite n'est définie, NULL devient la valeur par défaut implicite de la colonne. La validité de la contrainte de clé étrangère ne peut être conservée que s'il existe dans la table primaire des valeurs correspondant aux valeurs non NULL définies en raison de l'action ON UPDATE SET DEFAULT.

Considérons la contrainte FK_ProductVendor_Vendor_VendorID de la table Purchasing.ProductVendor de AdventureWorks2008R2. Cette contrainte établit une relation de clé étrangère de la colonne VendorID de la table ProductVendor vers la colonne de clé primaire VendorID de la table Purchasing.Vendor. Si l'action ON DELETE CASCADE est définie pour la contrainte, la suppression dans la table Vendor de la ligne dans laquelle VendorID vaut 100 entraîne également la suppression des trois lignes de la table ProductVendor dans lesquelles VendorID est égal à 100. Si l'action ON UPDATE CASCADE est spécifiée pour la contrainte, la mise à jour de la valeur VendorID dans la table Vendor de 100 à 155 entraîne également la mise à jour des valeurs VendorID dans les trois lignes de la table ProductVendor dont la valeur VendorID est égale à 100.

L'action ON DELETE CASCADE ne peut pas être spécifiée pour une table possédant un déclencheur INSTEAD OF DELETE. Les actions suivantes ne peuvent pas être spécifiées pour les tables possédant des déclencheurs INSTEAD OF UPDATE : ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL et ON UDATE SET DEFAULT.

Actions en cascade multiples

Une instruction DELETE ou UPDATE unique peut déclencher une série d'actions d'intégrité référentielle en cascade. Soit une base de données contenant les trois tables suivantes : TableA, TableB et TableC. Une clé étrangère dans TableB est définie avec ON DELETE CASCADE sur la clé primaire de TableA. Une clé étrangère dans TableC est définie avec ON DELETE CASCADE sur la clé primaire de TableB. Si une instruction DELETE supprime des lignes dans TableA, l'opération entraîne également la suppression de toutes les lignes de TableB dont des clés étrangères correspondent aux clés primaires supprimées dans TableA, et puis de toutes les lignes de TableC dont des clés étrangères correspondent aux clés primaires supprimées dans TableB.

La série d'actions d'intégrité référentielle en cascade déclenchée par une seule instruction DELETE ou UPDATE doit former une arborescence dépourvue de références circulaires. Aucune table ne peut apparaître plus d'une fois dans la liste de toutes les actions d'intégrité référentielle en cascade générées par l'instruction DELETE ou UPDATE. En outre, l'arborescence des actions d'intégrité référentielle en cascade ne doit mener qu'une fois à une table spécifique. Toute branche de l'arborescence s'achève par une table pour laquelle la valeur NO ACTION a été spécifiée ou est le paramètre par défaut.

Déclencheurs et actions d'intégrité référentielle en cascade

Les actions d'intégrité référentielle en cascade activent les déclencheurs AFTER UPDATE ou AFTER DELETE de la manière suivante :

Toutes les actions d'intégrité référentielle en cascade générées directement par l'instruction DELETE ou UPDATE initiale sont exécutées en premier.

Si des déclencheurs AFTER sont définis sur les tables affectées, ils sont activés une fois réalisées toutes les actions en cascade. Ces déclencheurs sont activés dans l'ordre inverse de l'action en cascade. Si une table possède plusieurs déclencheurs dont aucun n'est un premier ni un dernier déclencheur dédié, ils sont activés dans un ordre aléatoire. Cet ordre est spécifié à l'aide de sp_settriggerorder.

Si plusieurs chaînes d'actions en cascade proviennent de la table qui était la cible directe d'une action UPDATE ou DELETE, l'ordre dans lequel ces chaînes activent leurs déclencheurs respectifs n'est pas spécifié. Toutefois, une chaîne active toujours tous ses déclencheurs avant qu'une autre chaîne ne soit activée.

Si la table qui est la cible directe d'une action UPDATE ou DELETE possède un déclencheur AFTER, celui-ci est activé, que des lignes soient ou non affectées. Dans ce cas, aucune autre table n'est affectée par les actions en cascade.

Si l'un des déclencheurs précédents effectue des opérations UPDATE ou DELETE sur d'autres tables, ces actions peuvent démarrer des chaînes d'actions en cascade secondaires. Ces chaînes secondaires sont traitées pour chaque opération UPDATE ou DELETE une fois activés tous les déclencheurs de toutes les chaînes primaires. Ce processus peut être répété de façon récursive pour des opérations UPDATE ou DELETE ultérieures.

La réalisation d'opérations CREATE, ALTER, DELETE ou d'autres opérations DDL (Data Definition Language) dans les déclencheurs peut activer les déclencheurs DDL. Cela peut ensuite activer des opérations DELETE ou UPDATE qui démarrent des chaînes d'actions en cascade et des déclencheurs supplémentaires.

Si une erreur est générée dans une chaîne spécifique d'actions d'intégrité référentielle en cascade, une erreur se produit, aucun déclencheur AFTER n'est activé dans cette chaîne et l'opération DELETE ou UPDATE qui a créé la chaîne est restaurée.

En outre, une table possédant un déclencheur INSTEAD OF ne peut pas détenir de clause REFERENCES spécifiant une action en cascade. Cependant, un déclencheur AFTER d'une table impliquée dans une action en cascade peut exécuter une instruction INSERT, UPDATE ou DELETE sur une autre table ou vue, qui active un déclencheur INSTEAD OF défini sur cet objet.

Informations de catalogue sur les contraintes d'intégrité référentielle en cascade

L'interrogation de l'affichage catalogue sys.foreign_keys retourne les valeurs ci-après, qui indiquent la contrainte d'intégrité référentielle en cascade spécifiée pour une clé étrangère.

Valeur

Description

0

NO ACTION

1

CASCADE

2

SET NULL

3

SET DEFAULT

Les colonnes UPDATE_RULE et DELETE_RULE retournées par sp_fkeys et sp_foreignkeys retournent la valeur 0 lorsque CASCADE, SET NULL ou SET DEFAULT est spécifiée, et 1 lorsque NO ACTION est spécifiée ou est le paramètre par défaut.

Lorsqu'une clé étrangère est définie comme un objet de sp_help, l'ensemble de résultats obtenu contient les colonnes suivantes.

Nom de colonne

Type de données

Description

delete_action

nvarchar(9)

Indique si l'action de suppression est CASCADE, SET NULL, SET DEFAULT, NO ACTION ou N/A (non applicable).

update_action

nvarchar(9)

Indique si l'action de mise à jour est CASCADE, SET NULL, SET DEFAULT, NO ACTION ou N/A (non applicable).