INF: En cascade supprime et mises à jour de clés primaires

Traductions disponibles Traductions disponibles
Numéro d'article: 142480 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Microsoft SQL Server fournit l'intégrité référentielle déclarative (DRI, Declarative Referential Integrity) qui vous permet de définir des restrictions de l'intégrité des données pour une table ainsi que les relations entre tables, qui sont appliquées par SQL Server automatiquement au niveau du système.

SQL Server est conforme à ANSI saisie SQL par rapport à l'intégrité référentielle entre les colonnes PrimaryKey et ForeignKey qui nécessite l'insertion, mise à jour et suppression des données dans les tables liées afin d'être limité aux valeurs de préservent l'intégrité.

ANSI SQL intermédiaire ajoute 'actions d'intégrité référentielle,' qui décrivent ce qui doit être fait pour valeurs ForeignKey dépendantes lorsque leurs valeurs PrimaryKey correspondantes sont mis à jour ou supprimés. Cet article décrit comment ces mises à jour et suppressions en cascade peuvent être implémentés avec SQL Server.

Plus d'informations

DRI préserve les relations définies entre les tables lorsque des enregistrements sont insérés, mis à jour ou supprimés. DRI est basée sur FOREIGN KEY, PRIMARY KEY et relations de la contrainte UNIQUE, et elle garantit que les valeurs de clé connexes sont cohérentes. SQL Server utilise les contraintes PRIMARY KEY, FOREIGN KEY/REFERENCES et UNIQUE pour mettre en ?uvre la DRI ; un ForeignKey peut faire référence aux colonnes déclarées, soit comme un PrimaryKey ou avec une contrainte unique.

Le niveau de restreindre uniquement de l'intégrité référentielle nécessite que quatre actions suivantes être détectées et empêchées de terminer avec succès :
  1. Insertion d'une ligne dans une table de référence où la valeur d'un ForeignKey ne correspond pas à une valeur PrimaryKey dans la table référencée.
  2. Mise à jour une valeur ForeignKey dans une ligne dans une table de références afin qu'il y ait aucune valeur PrimaryKey correspondante dans la table référencée.
  3. Mise à jour une valeur PrimaryKey dans une ligne dans une table référencée afin un ForeignKey dans une ligne dans la table de référence n'a plus une valeur PrimaryKey correspondante.
  4. Suppression d'une ligne dans une table référencée afin un ForeignKey dans une ligne dans la table de référence n'a plus un PrimaryKey correspondante.
Mises à jour et suppressions en cascade offrent une alternative à restriction simplement l'occurrence des deux dernières actions ci-dessus. Lorsqu'un PrimaryKey est mis à jour, comme dans nombre trois ci-dessus, une mise à jour en cascade provoquerait ForeignKeys référençant tous être mis à jour pour la nouvelle valeur PrimaryKey. Lorsqu'un PrimaryKey est supprimé, comme dans nombre quatre, une suppression en cascade serait effectuez l'une des trois actions :
  1. Supprimez les lignes référencé PrimaryKey supprimée (CASCADE).
  2. Les valeurs ForeignKey références la valeur NULL (SET NULL).
  3. Définir les valeurs ForeignKey références à la valeur de la colonne par défaut (SET DEFAULT).
Mises à jour (de clés primaires) et suppressions en cascade peuvent être implémentées à l'aide de déclencheurs ou des procédures stockées. Descriptions de chacun sont indiquées ci-dessous. L'utilisation de procédures stockées est préférable car la fonctionnalité en cascade peut coexister avec ForeignKeys déclaré. Utiliser des déclencheurs pour fournir les mêmes fonctionnalités, ForeignKeys ne peut pas être déclaré.

Notez que, étant donné la définition d'une clé primaire, la modification d'une valeur de clé primaire doit être relativement rare ; la suppression d'une clé primaire doit être une opération moins rare.

Mises à jour en cascade supprime/À l'aide de procédures stockées

En cascade les suppressions et mises à jour peut coexister avec les contraintes DRI ForeignKey et références tant que les opérations en cascade sont effectuées avant les vérifications de contrainte correspondantes sont effectuées. Les instructions de manipulation de données (insertions, mises à jour et suppressions) doivent être gérées par le biais de procédures stockées plutôt que de permettre aux utilisateurs de manipuler directement les données de la table ; les instructions peuvent être effectuées directement, mais leur encapsulation dans les procédures stockées offre une sécurité supplémentaire et la facilité de maintenance. Les utilisateurs appellent la procédure stockée appropriée pour l'opération requise ; autorisation est accordée sur les procédures stockées et révoquée à partir des tables afin de garantir l'utilisation appropriée.

La procédure stockée de mise à jour en cascade aurait d'abord insérer une nouvelle ligne dans la table primaire, duplication de toutes les valeurs de la ligne existante, mais y compris la nouvelle valeur de clé primaire. Il serait puis mettre à jour les clés étrangères dans les tables dépendantes, puis supprimez la ligne d'origine dans la table primaire.

La procédure stockée de suppression en cascade aurait tout d'abord supprimez les lignes de clé étrangères ou les mettre à jour à NULL ou leur valeur par défaut. Il serait puis supprimez la ligne dans la table primaire.

Si davantage en cascade était nécessaire, la suppression ou une mise à jour des clés étrangères pourrait être effectuée à l'aide d'un autre suppression ou une mise à jour la procédure stockée pour la table dépendante.

L'instruction d'insertion ne nécessite pas de tout traitement spécial au-delà de ce que DRI fournit automatiquement, afin que l'insertion a pu être effectuée directement ou encapsulée dans une procédure stockée pour des raisons de cohérence.

Pour autoriser les mises à jour multilignes et les suppressions, il peut s'avérer nécessaire de créer des tables temporaires contenant les clés primaires des lignes qui doivent être traités, puis créez un curseur sur la table temporaire pour appeler la procédure pour chaque ligne, un par un. Cela est dû au fait qu'une plage de lignes ne peuvent pas être passées à une mise à jour ultérieure ou supprimer une procédure stockée à exécuter l'opération en cascade.

Mises à jour en cascade supprime/À l'aide de déclencheurs

Les déclencheurs ne peut pas être utilisés pour effectuer des mises à jour en cascade et des suppressions si ForeignKey pour PrimaryKey relations (ou ForeignKey-unique relations) ont été établies à l'aide de DRI de SQL Server. La DRI contraintes sont testés en premier ; le déclencheur s'exécute uniquement si la mise à jour ou de suppression passe toutes les restrictions de contrainte. Par conséquent, puisque toute mise à jour ou de suppression qui devra être disposées en cascade, échouera à la vérification des contraintes, les contraintes DRI ForeignKey ne doivent pas exister sur ces relations qui doivent être disposées en cascade.

En déclarant ne pas les contraintes ForeignKey (ou références), les mises à jour en cascade et des suppressions peuvent être implémentées à l'aide de déclencheurs. Les contraintes PrimaryKey et unique doivent toujours être utilisés, cependant.

Un déclencheur de suppression sur la table primaire entraîne la suppression des lignes dans les tables dépendantes ou définit tous les ForeignKeys correspondants à null (ou leur valeur par défaut). La suppression en cascade est facilement effectuée avec des déclencheurs imbriqués, chaque suppression de toutes les lignes des tables dépendantes. Les SetNull et SetDefault en cascade peuvent être plus problématique en raison de Considérations multilignes avec des déclencheurs Si ces mises à jour doivent être disposées en cascade à des niveaux supplémentaires de tables dépendantes. Toutefois, si les ForeignKeys ne sont pas également partie PrimaryKey de la table dépendante, ils peuvent simplement être mis à jour à partir du déclencheur.

Pour implémenter les mises à jour en cascade, un déclencheur de mise à jour sur la table primaire doit effectuer les modifications de données requises sur l'ou les tables secondaire. Là encore, dans la mesure où le ForeignKey en cours de mise à jour ne fait pas partie de PrimaryKey dépendant de la table, il peut simplement être mis à jour à partir du déclencheur.

La documentation fournie avec SQL Server 4.2 x exemples présentés de mettre à jour et supprimer des déclencheurs qui a appliqué l'intégrité référentielle. La déclencheur la documentation fournie avec SQL Server décrit déclencheurs utilisés pour la mise en ?uvre de règle métier plutôt que l'intégrité référentielle, mais les informations sur «Comment fonctionnent les déclencheurs» et 'Considérations Multirow' sont informatives (consultez SQL Server "base de données le Guide du développeur, «chapitre 6).

Voici un exemple d'une cascade delete déclencheur sur la table titles qui supprime toutes les lignes dans la table titleauthor avec ForeignKey valeurs correspondantes. Étant donné que title_id fait partie de PrimaryKey de titleauthor, ce déclencheur suppose il n'y a aucun niveau ultérieure des tables avec ForeignKeys faisant référence à la table titleauthor. Notez que cela fonctionnera correctement même pour les suppressions multilignes.
CREATE TRIGGER DelCascadeTrig
  ON titles
  FOR DELETE
AS
  DELETE titleauthor
    FROM titleauthor, deleted
    WHERE titleauthor.title_id = deleted.title_id
				

Voici un exemple d'un SetNull supprimer le déclencheur sur la table titleauthor qui met à jour de toutes les lignes de la table titleauthor correspondant aux valeurs de clé étrangère. Là encore, parce que title_id fait partie de PrimaryKey de titleauthor, ce déclencheur suppose il n'y a aucun niveau ultérieure des tables avec ForeignKeys faisant référence à la table titleauthor. Cela fonctionnera correctement même pour les suppressions multilignes.
CREATE TRIGGER DelSetNullTrig
  ON titles
  FOR DELETE
AS
    UPDATE titleauthor
      SET titleauthor.title_id = NULL
        FROM titleauthor, deleted
        WHERE titleauthor.title_id = deleted.title_id
				

Voici un exemple d'un déclencheur de mise à jour en cascade sur la table titles qui met à jour de toutes les lignes de la table titleauthor correspondant aux valeurs de clé étrangère. Là encore, parce que title_id fait partie de PrimaryKey de titleauthor, ce déclencheur suppose il n'y a aucun niveau ultérieure des tables avec ForeignKeys faisant référence à la table titleauthor.
CREATE TRIGGER UpdCascadeTrigBad
  ON titles
  FOR UPDATE
AS
  IF UPDATE(title_id)
  BEGIN
    UPDATE titleauthor
      SET titleauthor.title_id = inserted.title_id
        FROM titleauthor, deleted, inserted
        WHERE titleauthor.title_id = deleted.title_id
    END
  END
				

Cela NOT fonctionnera correctement pour les mises à jour multilignes, car il n'existe aucun moyen de faire correspondre une ligne donnée dans la table deleted avec sa ligne correspondante dans la table inserted sans ajouter un deuxième identificateur unique qui ne change jamais sa valeur. C'est le même problème survient lorsque la cascade doit être prises pour les niveaux lorsque le ForeignKey fait partie de PrimaryKey dans la table dépendante et PrimaryKey dans la table dépendante est référencée par autres ForeignKeys.

Pour empêcher les mises à jour multilignes, le déclencheur précédent doit être réécrit pour éviter que la mise à jour affecte plusieurs lignes de la table d'origine (titres, dans ce cas). Notez que la mise à jour dans le déclencheur peut bien mettre à jour plusieurs lignes dans titleauthor ; cette solution au problème multilignes peut engendrer simplement le problème de réapparaître au niveau suivant de la cascade.
CREATE TRIGGER UpdCascadeTrig
  ON titles
  FOR UPDATE
AS
  IF UPDATE(title_id)
  BEGIN
    IF @@ROWCOUNT = 1
      UPDATE titleauthor
        SET titleauthor.title_id = inserted.title_id
          FROM titleauthor, deleted, inserted
          WHERE titleauthor.title_id = deleted.title_id
    ELSE
      ROLLBACK TRANSACTION
      RAISERROR ('Multi-row update on table "titles" not allowed.')
    END
  END
				

Notez que pour plusieurs niveaux de cascade déclenche pour fonctionner du tout, les «sp_config paramètre déclencheurs imbriqués doit être ' 1' et qui déclenche uniquement peut être imbriquée à 16 niveaux.

Propriétés

Numéro d'article: 142480 - Dernière mise à jour: mardi 16 septembre 2003 - Version: 3.1
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 6.0 Standard
  • Microsoft SQL Server 6.5 Édition Standard
  • Microsoft SQL Server 7.0 Standard
Mots-clés : 
kbmt kbinfo kbusage KB142480 KbMtfr
Traduction automatique
IMPORTANT : Cet article est issu du système de traduction automatique mis au point par Microsoft (http://support.microsoft.com/gp/mtdetails). Un certain nombre d?articles obtenus par traduction automatique sont en effet mis à votre disposition en complément des articles traduits en langue française par des traducteurs professionnels. Cela vous permet d?avoir accès, dans votre propre langue, à l?ensemble des articles de la base de connaissances rédigés originellement en langue anglaise. Les articles traduits automatiquement ne sont pas toujours parfaits et peuvent comporter des erreurs de vocabulaire, de syntaxe ou de grammaire (probablement semblables aux erreurs que ferait une personne étrangère s?exprimant dans votre langue !). Néanmoins, mis à part ces imperfections, ces articles devraient suffire à vous orienter et à vous aider à résoudre votre problème. Microsoft s?efforce aussi continuellement de faire évoluer son système de traduction automatique.
La version anglaise de cet article est la suivante: 142480
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com