MS SQL Server
  1. MS SQL Server
  3. Deux foreign keys referencent une table – ON UPDATE SET NULL ne fonctionne pas
Intereting Posts
SQL Azure: comment exécuter un script DDL commun sur tous les schémas? Lire un file XML avec des namespaces à l'aide de xmlnamespaces dans SQL Server 2008 La requête SQL MFC renvoie DB_E_NOTABLE Résultat de la requête SQL Server dans le file Utilisez OVER (PARTITION BY) au lieu de Group By Impossible d'identifier la raison des erreurs BULK INSERT Comment puis-je sélectionner une correspondance regex à partir d'un text / varchar dans MS SQL? SQL – Sélectionnez toutes les lignes entre deux rangées de strings ouvrir un file dans asp.net-mvc Chaîne de connection .Net SQL Server – cache le mot de passe d'autres développeurs SQL Server: convertir la string 'n', 'hh', 'd' en variables globales n, hh et d Comment get le weekno de six semaines du dimanche dans SQL server 2008 r2? Entity Framework impossible de supprimer la database, database en cours d'utilisation Délai d'attente de connection à MSSQL Server 2017 lorsque l'application s'exécute sous Linux Comment save des données historiques en SQL lorsque datatables de la source changent dans le time

Deux foreign keys referencent une table – ON UPDATE SET NULL ne fonctionne pas

J'ai deux foreign keys dans une table. Supposons que cette table s'appelle News et possède des foreign keys updatedById et createdById , qui pointent vers userId dans la table Users .

Maintenant, je veux mettre à NULL les foreign keys lorsque l'user est supprimé, mais quand j'essaie de définir ON DELETE SET NULL dans ces relations, je reçois:

L'introduction de la contrainte FOREIGN KEY 'FK_News_Users' sur la table 'News' peut provoquer des cycles ou plusieurs paths en cascade. Spécifiez ON DELETE NO ACTION ou ON UPDATE NO ACTION ou modifiez d'autres contraintes FOREIGN KEY.

Je ne comprends pas pourquoi les deux foreign keys ne peuvent pas mettre à null?

Actions en cascade multiples

La série d'actions référentielles en cascade déclenchée par un seul DELETE ou UPDATE doit former un tree ne contenant pas de references circulaires. Aucune table ne peut apparaître plus d'une fois dans la list de toutes les actions référentielles en cascade qui résultent de DELETE ou UPDATE. En outre, l'tree des actions référentielles en cascade ne doit pas avoir plus d'un path vers une table spécifiée. Toute twig de l'tree est terminée lorsqu'il rencontre une table pour laquelle aucune action n'a été spécifiée ou est la valeur par défaut.

Peut-être dans des situations comme celle-ci, vous pouvez envisager d'implémenter des fonctionnalités pour supprimer l'user plutôt que physiquement (par exemple en introduisant un champ d'indicateur Active ou Supprimé dans le tableau des Users ). De cette façon, toutes les relations restnt intactes et peuvent être analysées rétrospectivement.

Mais si vous avez encore besoin d'implémenter ON DELETE SET NULL pour les deux FK, vous pouvez utiliser un triggersur FOR DELETE sur la table User comme ceci: 

 CREATE TRIGGER Users_News_Delete_Trigger ON Users FOR DELETE AS BEGIN UPDATE News SET createdById = NULL WHERE createdById = DELETED.id; UPDATE News SET updatedById = NULL WHERE updatedById = DELETED.id; END

Une alternative est de créer une table de reference croisée entre la table A et la table B où chaque input est A.ID et B.ID et B.ID a une key étrangère à B. Ensuite, vous pouvez simplement supprimer CASCADE à la reference croisée. Vous devrez mettre un troisième champ dans votre reference croisée pour indiquer le but unique de reference tel que 

 [NewsID] INT NOT NULL DEFAULT 0, [UsersID] INT NOT NULL DEFAULT 0, [IsCreatedBy] bit NOT NULL DEFAULT 0

Naturellement, vous devez alors retirer ces champs de la table A. La jointure à gauche vous donnera alors la valeur null pour ces champs s'ils sont manquants.

Je ne pense pas qu'il soit possible (dans SQL Server) de le faire sur 2 contraintes FK ou plus sur la même table, pointant vers le même FK.

Normalement, dans des situations comme celle-ci, vous préférez supprimer l'user logiquement puis physiquement en introduisant un champ indicateur (par exemple, Actif ou Supprimé). De cette façon, toutes les relations restnt intactes et peuvent être analysées rétrospectivement. — peterm

Si vous voulez conserver l'idée originale de définir NULL, un moyen de contourner le problème consisterait à gérer la suppression des users dans une procédure stockée et à effectuer les mises à jour immédiatement après. 

 CREATE PROCEDURE sp_DeleteUser @UserId INT AS BEGIN SET NOCOUNT ON; DELETE FROM Users WHERE Id = @UserId; UPDATE News SET created_byId = NULL WHERE created_byId = @UserId; UPDATE News SET updated_byId = NULL WHERE created_byId = @UserId; END GO