Auto-référencement des contraintes de clé étrangère et de supprimer

quelle est la méthode recommandée pour gérer l'auto-référencement foreignkey contraintes dans SQL-Server?

De Table-Modèle:

fiData fait référence à un précédent record en tabData. Si je supprime un enregistrement référencé par fiData, la base de données déclenche une exception:

"La SUPPRESSION de la déclaration en contradiction avec la MÊME TABLE de RÉFÉRENCE
la contrainte "FK_tabDataPrev_tabDataNext". Le conflit s'est produit dans
base de données "Mabase", table "dbo.tabData", la colonne "fiData'"

si Enforce Foreignkey Constraint est réglé sur "Oui".

Je n'ai pas besoin de les effacer en cascade les enregistrements qui sont référencés, mais j'aurais besoin de définir fiData=NULL où il est référencé. Mon idée est de mettre en Enforce Foreignkey Constraint à "Non" et de créer une suppression de déclenchement. Est-ce recommandable ou il y a de meilleures façons?

Merci.

OriginalL'auteur Rango | 2011-03-24

7

Contrairement à Andomar, je serais heureux à l'aide d'un déclencheur, mais je ne voudrais pas supprimer la contrainte de vérification. Si vous l'appliquer comme un instead of déclencheur, vous pouvez réinitialiser les autres lignes à null avant de procéder à la suppression réelle:
```
CREATE TRIGGER T_tabData_D
on tabData
instead of delete
as
    set nocount on
    update tabData set fiData = null where fiData in (select idData from deleted)
    delete from tabData where idData in (select idData from deleted)
```
C'est court, c'est succinct, il ne serait pas nécessaire si SQL Server peut gérer des clés étrangères des cascades à la même table (en d'autres SGBDR', vous pouvez peut-être juste préciser ON DELETE SET NULL pour la contrainte de clé étrangère, YMMV).

Merci. Mais j'obtiens une exception sur la création de la gâchette: "au LIEU DE SUPPRIMER/mettre à JOUR les déclencheurs ne peuvent pas être définis sur une table qui a une clé étrangère avec une cascade DELETE/UPDATE d'action défini".
Je sais que ce trop tard pour commenter, mais pour quelqu'un qui est à la recherche comme moi. Cela ne fera que supprimer une entrée. il ne sera pas en cascade de manière récursive.
c'était "on DELETE SET NULL", pas "on DELETE CASCADE". L'ancien n'a pas besoin de répéter. Pour "on DELETE CASCADE", je vous recommande un CCE qui calcule la fermeture de tous les ID valeurs d'abord, puis effectue les supprimer.
je n'ai pas remarqué cela en question. mais le premier commentaire dit "en cascade sur SUPPRIMER/mettre à JOUR", a déclaré par la personne qui les a interrogés.
Je suis bloqué au même point. Je pense que la solution pourrait être de supprimer les autres actions CASCADE de la table, puis ajouter l'équivalent logique du Déclencheur... n'a pas Encore trouvé une meilleure façon de le faire. S'il vous plaît dites dans le cas où vous avez trouvé un

OriginalL'auteur Damien_The_Unbeliever
2

Déclenche ajouter implicite de la complexité. Dans une base de données avec les déclencheurs, vous ne savez pas ce qu'est une instruction SQL ne en le regardant. Dans mon expérience, les déclencheurs sont une mauvaise idée, sans exceptions.

Dans votre exemple, la définition de la disparition forcée contraint de "Non" signifie que vous pouvez ajouter un ID inexistant. Et l'optimiseur de requête sera moins efficace car il ne peut pas assumer la clé est valide.

Envisager la création d'une procédure stockée à la place:
```
create procedure dbo.NukeTabData(
    @idData int)
as
begin transaction
update tabData set fiData = null where fiData = @idData
delete from tabData where idData = @idData
commit transaction
go
```
Maintenant, votre code d'appel a exec NukeTabData(ID), et vous n'avez aucune idée de ce que cela donne sans examiner le contenu de la procédure. Pourquoi est-ce que ça va, quand un déclencheur n'est-ce pas?
La procédure stockée est explicite: il me dit où je dois regarder si je suis intéressé par les détails. Un déclencheur est implicite: rien dans delete from tabData where idData = 42 allusion à la présence de mises à jour supplémentaires qui seront exécutés. Par exemple, après l'exécution de la supprimer, un déclencheur pourrait causer @@rowcount à 4 au lieu de 1. Il en résulte difficile de bugs, surtout pour les développeurs qui sont nouveaux dans le code de base.
est portée sur la base - il n'est pas affecté par l'activité au sein de la gâchette. Vous êtes probablement penser à @@IDENTITY, mais la plupart des gens savent à éviter que ces jours-ci
Eh bien il y a tout un tas d'effets secondaires: @@identity, possibilité de restauration, inattendus de blocages. Une erreur commune semble être d'audits ou d'enregistrement des erreurs à l'aide d'un déclencheur. Lorsque la transaction est annulée, l'enregistrement est annulée. Ainsi, lorsqu'une nouvelle commande échoue et est annulée, il n'apparaît pas dans les journaux d'erreurs, et il ne ressemble à aucune commande a été passée.
Schmelter: Une clé étrangère est la seule manière efficace, mais il ne devrait pas trop, généralement juste une micro d'optimisation.

OriginalL'auteur Andomar
0

Ce très en retard pour répondre.

Mais pour quelqu'un qui est à la recherche comme moi.

et souhaitez cascade

ici est très bonne explication

http://devio.wordpress.com/2008/05/23/recursive-delete-in-sql-server/

Le Problème
Bien que vous pouvez définir une clé étrangère avec les SUPPRIMER en CASCADE dans SQL Server, récursive les suppressions en cascade ne sont pas pris en charge (c'est à dire suppression en cascade sur la même table).

Si vous créez un au LIEU DE SUPPRIMER le déclencheur, ce trigger ne se déclenche pour la première instruction DELETE, et ne se déclenche pas pour les dossiers de manière récursive supprimés à partir de ce déclencheur.

Ce comportement est documenté sur MSDN pour SQL Server 2000 et SQL Server 2005.

La Solution
Supposons que vous avez une table est définie comme ceci:
```
CREATE TABLE MyTable (
    OID    INT,        -- primary key
    OID_Parent INT,    -- recursion
    ... other columns
)
```
puis le déclencheur de suppression ressemble à ceci:
```
CREATE TRIGGER del_MyTable ON MyTable INSTEAD OF DELETE
AS
    CREATE TABLE #Table(
        OID    INT
    )
INSERT INTO #Table (OID)
SELECT  OID
FROM    deleted

DECLARE @c INT
SET @c = 0

WHILE @c <> (SELECT COUNT(OID) FROM #Table) BEGIN
    SELECT @c = COUNT(OID) FROM #Table

    INSERT INTO #Table (OID)
    SELECT  MyTable.OID
    FROM    MyTable
    LEFT OUTER JOIN #Table ON MyTable.OID = #Table.OID
    WHERE   MyTable.OID_Parent IN (SELECT OID FROM #Table)
    AND     #Table.OID IS NULL
END

DELETE  MyTable
FROM    MyTable
INNER JOIN #Table ON MyTable.OID = #Table.OID

GO
```
C'est une bonne réponse pour on DELETE CASCADE, mais la question est sur on DELETE SET NULL

OriginalL'auteur Arif

Vous devez vous connecter pour publier un commentaire.