Comment traiter les erreur montées par le conflit avec la contrainte de type "clé étrangère" ? Voici quelques exemples.
Identifiant de message
SQL Server génère une exception dont l'identifiant de message est 547. Renseignez la vue système pour savoir les format de ce message en différentes langages.
SELECT * FROM sys.messages WHERE message_id = 547
En cas d'erreur :
SET LANGUAGE french GO INSERT INTO MyTable (Id, ParentTableId) VALUES (1, 1)
Msg 547, Level 16, State 0, Line 1 L'instruction INSERT est en conflit avec la contrainte FOREIGN KEY "FK1_MyTable". Le conflit s'est produit dans la base de données "MyDB", table "dbo.ParentTable", column 'ParentTableId'. L'instruction a été arrêtée.
Ensuit, dans le code applicatif vous pouvez se baser sur un ou plusieurs numéros de message.
Deuxièmement, il est nécessaire de composer le message compréhensible par l'utilisateur. En supposant que le nom de contraintes sont uniques dans la base de données il vous faut extraire ce nom (regex ou recherche simple par préfixe comme "FK%") et puis générer le message utilisateur en fonction du nom de contrainte en prenant en compte la langue courante.
En sachant le nom de contrainte, cette requête simple vous récupère les noms des tables référencées.
USE MyDB SELECT P.name as ParentTableName, C.name as ChildTableName FROM sys.foreign_keys FK INNER JOIN sysforeignkeys FK2 ON FK.object_id = FK2.constid INNER JOIN sys.objects C ON FK2.fkeyid = C.object_id INNER JOIN sys.objects P ON FK2.rkeyid = P.object_id WHERE FK.name = 'FK1_MyTable'
ParentTableName ChildTableName ----------------- ------------------- ParentTable MyTable (1 row(s) affected)
Triggers
Cette approche est un peu "old school" mais peut être très efficace surtout si vous modélisez votre base de donnée avec l'outil comme PowerAMC (PowerDesigner), ERwin etc. Dans ce cas vous pouvez choisir de ne pas utiliser l'intégrité référentielle déclarative (DRI) sous la forme des contraintes de clé étrangère mais générer les triggers à partir d'un template commun qui monte l'erreur en cas de violation d'intégrité.
D'autre part, c'est SQL Server et Sybase qui ne supportent pas les triggers BEFORE. Mais en cas d'autre SGBD (i.e. Oracle, PostgreSQL, Firebird) vous n'avez pas besoin de supprimer les contraintes mais juste rajouter les triggers de type BEFORE pour le traitement.
C'est une bonne idée d'implémenter ce message au niveau de SQL Server.
EXEC sp_addmessage @msgnum = 60000, @severity = 16, @msgtext = N'The conflict occurred: referenced table "%s" does not contains "%s"', @lang = 'us_english'; EXEC sp_addmessage @msgnum = 60000, @severity = 16, @msgtext = N'La table de référence %1! n''a pas de valuer %2!', @lang = 'French';
Puis dans le trigger :
CREATE TRIGGER MyTable_Create ON MyTable FOR INSERT AS BEGIN ... SELECT TOP 1 @ErrorValue = C.ParentTableId FROM inserted C LEFT OUTER JOIN ParentTable P ON C.ParentTableId = P.ParentTable WHERE P.ParentTable IS NULL IF @@rowcount > 0 RAISERROR(60000, 16, 1, 'ParentTable', @ErrorValue) ...
Commentaire
En effet, il s'agit plutôt de choix "où dois-je implémenter la couche d'accès de données et le service de méta-données" :
- Au niveau de mon application
- Au niveau de la base de données même
- La combinaison de 1 et 2, i.e. la couche de procédures stockées et triggers + la couche des classes NHibernate
En cas 2 une fois implémentée cette logique peut être ré-utilisé par plusieurs applications de différentes technologies (PHP, Perl, Java, C++, Delphi etc.)
En cas 1 c'est la base de données qui peut être changé sans impact significatif pour les applications basée sur cette implémentation.