Dev Faq
7
votes

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

Quelle est la manière recommandée de gérer les contraintes d'essayage de l'entreprise à références auto-références dans SQL-Server?

Modèle de table:

Entrez la description de l'image ici

Fidata fait référence à un enregistrement précédent dans Tabdata. Si je supprime un enregistrement qui est référencé par fidata , la base de données jette une exception:

"L'instruction DELETE en conflit avec la même référence de table contrainte "fk_tabdataprev_tabdatanext". Le conflit s'est produit dans Base de données "mydatabase", table "dbo.tabdata", colonne 'Fidata' "

Si appliquer la contrainte d'entreprise d'entreprise est défini sur "Oui".

Je n'ai pas besoin de cascade supprimer des enregistrements référencés mais je devrais définir fidata = null où il est référencé. Mon idée est de définir d'appliquer la contrainte d'entreprise étrangère à "non" et de créer un déclencheur de suppression. Est-ce recommandé ou y a-t-il de meilleurs moyens?

merci.

0 commentaires

3 Réponses :

3
votes

Les déclencheurs ajoutent une complexité implicite. Dans une base de données avec des déclencheurs, vous ne saurez pas ce qu'est une instruction SQL en le regardant. Dans mon expérience, les déclencheurs sont une mauvaise idée sans exception.

Dans votre exemple, définissez la contrainte forcée au "non" signifie que vous pouvez ajouter un identifiant inexistant. Et l'optimiseur de requête sera moins efficace car il ne peut pas supposer que la clé est valide. P>

envisagez de créer une procédure stockée à la place: P> 

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

7 commentaires

Maintenant, votre code d'appel est EXEC NUKETABDATA (ID) , et vous n'avez aucune idée de ce que cela ne fait pas examiner le contenu de la procédure. Pourquoi ça va, quand un déclencheur n'est pas?

@Damien_the_unbeliever: La procédure stockée est explicite: elle me dit où je devrais regarder si je suis intéressé par les détails. Un déclencheur est implicite: rien dans Supprimer de Tabdata où iddata = 42 Conseils en présence de mises à jour supplémentaires qui seront exécutées. Par exemple, après avoir exécuté la suppression, un déclencheur pourrait causer @@ rowcount à 4 au lieu de 1. Il en résulte des bugs difficiles - en particulier pour les développeurs qui sont nouveaux à la base de code.

@@ RowCount est basé sur la portée - elle n'est affectée par aucune activité dans la gâchette. Vous pensez éventuellement à @@ identité, mais la plupart des gens savent éviter que ces jours-ci

@Damien_the_unbeliever: Eh bien, il y a toute une classe d'effets secondaires: @@ identité , possibilité de restauration, des blocages inattendus. Une erreur commune semble être des audits ou une journalisation des erreurs à l'aide d'un déclencheur. Lorsque la transaction est renvoyée, la journalisation est renvoyée également. Ainsi, lorsqu'un nouvel ordre échoue et est renvoyé, il n'apparaît pas dans les journaux d'erreur et on dirait qu'aucune commande n'a été placée du tout.

@Andomar: merci. Mais le tableau est déjà impliqué dans plusieurs applications et services dans lesquels chacun pourrait changer / supprimer des enregistrements. Cela n'aurait pas d'intérêt à changer tout à l'aide de la procédure stockée à la place.

@Andomar: J'ai fini par utiliser ce que j'ai déjà mentionné, "appliquer FK-CONTRAINT = NO" et A après avoir supprimé -Trigger qui définit Fidata à NULL. Pouvez-vous expliquer ce que vous voulez dire avec "l'optimiseur de requête sera moins efficace car il ne peut pas supposer que la clé est valide"? La table a déjà ~ 12 millions de lignes et est interrogée franchement. Fidata ne sera jamais ajouté manuellement mais calculé par un processus stocké tous les matins. Par conséquent, I peut vous assurer que le FK est valide. Comment dire que SQL-Server?

@Tim Schmelter: Une clé étrangère est la seule manière efficace, mais cela ne devrait pas trop comprendre, généralement une micro-optimisation.

7
votes

Contrairement à l'Andomar, je serais heureux d'utiliser un déclencheur - mais je ne supprimerais pas la vérification des contraintes. Si vous l'implémentez comme un au lieu de la gâchette CODE>, vous pouvez réinitialiser les autres lignes à NULL avant d'effectuer 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)

5 commentaires

Merci. Mais je reçois 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 présentant une clé étrangère avec une cascade sur une action de suppression / mise à jour définie".

Je le sais trop tard pour commenter mais pour quelqu'un qui cherche comme moi. Cela ne supprimera qu'une seule entrée. Il ne cassera pas de manière récursive.

@Arif - C'était "sur Suppriming Set Null", pas "Sur Suppr Cascade". Le premier n'a jamais besoin de recueillir. Pour "On Suppr Cascade", je recommanderais un CTE qui calcule la fermeture sur toutes les valeurs id d'abord, puis effectue la suppression.

Je n'ai pas remarqué cela en question. Mais le premier commentaire dit "Cascade sur Supprimer / update" commenté par la personne qui s'est interrogée.

@ Timschmelter j'ai été bloqué au même point. Je pense que la solution pourrait être de retirer les autres actions en cascade de la table, puis d'ajouter la logique équivalente dans la gâchette ... Je n'ai toujours pas trouvé de meilleure façon de le faire. S'il vous plaît dire au cas où vous en trouveriez un

0
votes

cela très tard pour répondre.

Mais pour quelqu'un qui cherche comme moi. p>

et veulent cascade code> p>

ici est très Bonne explication p>

http: / /devio.wordpress.com/2008/05/23/Recursive-Delete-in-sql-server/ P>

Le problème Bien que vous puissiez définir une clé étrangère avec Cascade Supprez dans SQL Server, les suppressions en cascade récursives ne sont pas prises en charge (c.-à-d. Supprimer en cascade sur la même table). P>

Si vous créez un déclencheur, ce déclencheur ne fait que se déclencher. Pour la première instruction DELETE, et ne déclenche pas pour des enregistrements supprimés de ce déclencheur. p>

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

La solution Supposons que vous ayez une table définie comme ceci: p> xxx pré>

puis le déclencheur de suppression ressemble à ceci: p> 

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

1 commentaires

C'est une bonne réponse à la suppression de la cascade, mais la question concerne la touche DELETE SET NULL

Articles qui pourrait vous intéresser :

Ordre des requêtes de syntaxe SQL par
Problème de jointure sql, impossible de récupérer les enregistrements de la table a qui correspondent à une condition de la table b et les enregistrements restants de la table a
Interroger les données de PostgreSQL à partir de deux tables
Comment identifier les lignes par groupe avant un certain écart de valeur?