Contraintes FOREIGN KEY SQL vs code client

[Emmanuel Lecoester]

Qui n'a jamais débattu avec un collègue sur l'utilité de l'utilisation ou non de clés étrangères dans le SGBD.

Ce nouvel article de sqlpro nous permet d'aborder ce sujet à partir de cas pratiques.

http://sqlpro.developpez.com/article/fk-sql-vs-appli/

Et vous cet article vous a-t-il convaincu ?

[fsmrel]

Ça me rajeunit de 20 ans...

Dès que DB2 nous permit enfin d’assurer l’intégrité référentielle, je l’ai mise en œuvre d’office (c’était en 1988, et à l’époque aucun SGBD n’avait encore suivi les recommandations de Codd et de Date à ce sujet). Par contre, nombre de ceux qui la réclamaient à grands cris, se sont abstenus, trouvant tout à coup qu’elle pouvait poser a priori des problèmes de performance, mais sans qu'ils effectuent les travaux de prototypage ad-hoc... C’est du passé, mais il reste toujours quelques irréductibles, et ceux-là doivent avoir bien des orphelins dans leurs tables, même s’ils sont persuadés du contraire (j'ai pu m'en assurer, à coups d'audits...)

A noter que le père du concept formel d’intégrité référentielle est — comme par hasard — Ted Codd (voyez Extending the Database Relational Model to Capture More Meaning).

[mikedavem]

Bon au contraire de fsmrel ca ne rajeunit pas de 20 ans ... j'étais encore trop petit à ce moment là

Un très bon article comme d'habitude que devrait lire n'importe quel éditeur d'ERP. J'ai encore vu il y a un an une entreprise qui utilisait un ERP où aucune integrité référentielle au niveau base n'était implémenté et ceci sur DB2 .... C'était l'application qui gérait cela .... La conséquence ? Simple , une mauvaise qualité de données (doublons, enregistrements orphelins) ... et des problèmes de maintenance qui s'en suivent ...

++

[spdev666]

Très bon argumentaire (en même temps, j étais déjà convaincu ).

Par contre, ce serait intéressant qu un "anti - FOREIGN KEY" défende son point de vue.

[ILP]

Un article à distribuer auprès de mes collègues. Là je dois importer des données dans une base de données Firebird (qui accepte les contraintes FOREIGN KEY). Je n'ai aucune clé primaire, encore moins de clé étrangère.
Et cerise sur le gâteau : les index des tables sont enregistrées dans une table MAX_ID, avec un champ pour chaque table. La valeur de ces champs étant incrémentées à chaque INSERT .
Je suis désespéré .

[doc malkovich]

Snif, et le décisionnel alors ?
Un exemple pertinent où les FK sont gérées dans le code ?
En effet, on "nettoie" les données dans l'ODS, et on capture les enregistrements défaillants ...

[SQLpro]

Salut,

Envoyé par john malkovich

Snif, et le décisionnel alors ?
Un exemple pertinent où les FK sont gérées dans le code ?
En effet, on "nettoie" les données dans l'ODS, et on capture les enregistrements défaillants ...

C'est pas une bonne méthode.... Mieux vaut utiliser une base tampon pour faire l'alimentation et que les lignes rejetées restent dans la base tampon.
Dès lors il y a toujours un décisionnel clean et au pire on peut voir les lignes "défaillantes" en prévoyant des vues qui font l'UNION ALL des tables entre les deux bases....

Comme quoi il existe TOUJOURS une solution pour bien faire les choses !

A +

[doc malkovich]

Hello mr sqlpro !

Envoyé par SQLpro

Mieux vaut utiliser une base tampon pour faire l'alimentation et que les lignes rejetées restent dans la base tampon

yaisse, c'est ce qu'on appelle l'ODS dans notre jargon de décisionneux
mais si on garde les rejets dans cette base tampon, tu es d'accord qu'il n'y a pas de FK sgbd sur cette base ? sinon adios les rejetos ...
après au niveau perfs on a aussi tendance à virer les FK du coup chez nous ...
c'est un cas particulier ( il faut dire que la bi c'est un peu particulier ) ...
ou alors j'ai loupé un truc ...

[Jester]

Envoyé par john malkovich

yaisse, c'est ce qu'on appelle l'ODS dans notre jargon de décisionneux
mais si on garde les rejets dans cette base tampon, tu es d'accord qu'il n'y a pas de FK sgbd sur cette base ? sinon adios les rejetos ...
après au niveau perfs on a aussi tendance à virer les FK du coup chez nous ...
c'est un cas particulier ( il faut dire que la bi c'est un peu particulier ) ...
ou alors j'ai loupé un truc ...

En BI on peut aussi faire en sorte de faire pointer les rejetons sur une ligne bidon et du coup on peut garder les FK.

Ne serais-ce que pour comprendre les schéma, ça me semble essentiel.

[doc malkovich]

Envoyé par Jester

En BI on peut aussi faire en sorte de faire pointer les rejetons sur une ligne bidon et du coup on peut garder les FK.

Oui mais justement pour créer cette ligne bidon il ne suffit pas d'avoir une FK ... Il faut simuler la FK dans le code pour la créer, c'est ce qui est fait dans la plupart des ETL ...

[fsmrel]

Envoyé par Jester

En BI on peut aussi faire en sorte de faire pointer les rejetons sur une ligne bidon et du coup on peut garder les FK.

Une clé étrangère est un moyen de garantir l’intégrité référentielle. Si j’ai perdu les liens entre 20000 contrats (vente d’automobiles) et leurs titulaires, vais-je bricoler un lien vers un titulaire bidon ? Ça serait pervertir l’intégrité référentielle, qui en fait n’existerait plus. Rien de tel pour mettre la clé sous la porte.

Envoyé par Jester

Ne serais-ce que pour comprendre les schéma, ça me semble essentiel.

Pour comprendre les schémas (MCD) on représente les liens entre entités-types. Concernant les schémas (MLD), si on tire un lien c’est qu’on établit l’intégrité référentielle, et il n’y a pas lieu d’établir des clés primaires « bidons » servant de référence pour des clés étrangères « bidons » elles aussi.

[Jester]

Envoyé par fsmrel

Une clé étrangère est un moyen de garantir l’intégrité référentielle. Si j’ai perdu les liens entre 20000 contrats (vente d’automobiles) et leurs titulaires, vais-je bricoler un lien vers un titulaire bidon ? Ça serait pervertir l’intégrité référentielle, qui en fait n’existerait plus. Rien de tel pour mettre la clé sous la porte.

Dans un monde parfait on a toutes les données, elles sont propres et on a une doc qui va bien avec un beau modèle.

Si une filiale intégrée ne stockait pas les titulaires, c'est la vie. Mais si on doit calculer l'augmentation des ventes, ça ne joue pas donc j'aurais tord d'avoir virer ces données car les chiffres seraient faux.

On doit pouvoir choisir au cas par cas si l'impureté de ces données pose problème ou non.

[fsmrel]

Envoyé par Jester

Dans un monde parfait on a toutes les données, elles sont propres et on a une doc qui va bien avec un beau modèle.

Si une filiale intégrée ne stockait pas les titulaires, c'est la vie. Mais si on doit calculer l'augmentation des ventes, ça ne joue pas donc j'aurais tord d'avoir virer ces données car les chiffres seraient faux.

La modélisation est faite pour prendre en compte les problèmes a priori et pas a posteriori, à coups de solutions plus ou moins vaseuses, façon lignes « bidons ». Si l’on sait que l’on aura à prendre en compte des contrats dont on ne connaît pas les titulaires, on modélise la chose, mais certainement pas avec des pseudo PK/FK. C’est l’ABC du métier de concepteur.

L’objectif reste bien d’avoir une base de données propre, décrite par un modèle pertinent. Quant à la doc, il n'est pas interdit de la maintenir à jour.

[Waldar]

Je pense que Jester parlait d'insérer dans une table client une ligne CLIENT INCONNU avec un code reconnaissable ('.' ou -1 par exemple) sur laquelle faire pointer du CA.
On reste alors dans une relation PK/FK classique, le CA est bien représenté dans les tableaux de bord des directeurs, et rien n'empêche une alimentation ultérieure de corriger la donnée.

Personnellement au niveau du DWH, comme il s'agit d'un aggrégateurs de différentes sources les liens PK/FK sont primordiaux et même si je gère les rejets et recyclages dans une structure dédiée, je laisse les contraintes d'intégrité pour les raisons évoquées dans ce post.

Dans les datamarts, alimentés exclusivement à partir de ce DWH, "forcément" les contraintes sont validées. Utilisant Oracle, j'ai suivi une de leur préconisation : déclarer les FK en DISABLE NOVALIDATE RELY. Je demande à Oracle de me croire sur parole et de faire comme si les FK étaient validées. Je gagne ainsi du temps à l'alimentation et je profite des optimisations en restitutions.

[hatifnatte]

A propos de l'article, je trouve qu'on ne gagne rien à dire que celui qui n'utilise pas les contraintes référentielles est stupide. Ce n'est pas un argument sérieux.
Sans cela l'article aurait été parfait.

[_skip]

A propos de l'article, je trouve qu'on ne gagne rien à dire que celui qui n'utilise pas les contraintes référentielles est stupide. Ce n'est pas un argument sérieux.
Sans cela l'article aurait été parfait.

Oui c'est un peu la marque de fabrique de l'auteur j'ai l'impression. C'est vrai que ce n'est pas vraiment nécessaire...

Sinon je suis convaincu de la nécessité des FK, j'ai d'ailleurs insisté pour que mon ancien employeur renonce à SQLite, même pour une petite application.

Sur ce genre de base, il suffit généralement d'une ou deux requêtes SELECT avec un NOT IN(...) sur les tables les plus fréquemment affectées pour repérer une poignée d'enregistrements fautifs.

Je suis également contre (et là je suis sûr que des gens seront pas d'accord) le fait de faire des vérifications dans le code client EN PLUS de celles faites par la base de donnée. Par exemple si je veux supprimer un Produit, j'envoie directement un DELETE et je réagis sur l'exception, les vérifications préalables sont lourdes à écrire, couteuses en performance et au final peu rassurantes et difficiles à maintenir. Enfin c'est mon avis.

Autre chose : quelqu'un ici a déjà jeté un oeil au modèle de donnée utilisé par SAP?

[daredare]

Envoyé par _skip

j'ai d'ailleurs insisté pour que mon ancien employeur renonce à SQLite, même pour une petite application.

Il me semble (cf. http://www.libre-a-vous.fr/sqlite-co...cle-etrangere/) qu'il est possible de simuler ces contraintes de clé étrangère avec SQLite...

Ce n'est peut être pas parfait, mais pour une petite appli. qui ne nécessite pas d'accès concurrent, cela peut du coup convenir.

[_skip]

Juste mais je trouve que ça ressemble à un gros hack.
De plus, les mini SGBD embarqués qui tiennent la route sont assez nombreux, firebird embedded, mssql CE, VistaDb...

La migration vers une architecture client-serveur au besoin aurait été simplifiée aussi. Ce n'était pas seule raison, il y a aussi les bizarreries au niveau des types et la tant vantée vitesse qui devenait toute relative sur les grosses tables...

[Soutou]

Arrivées en 1992 avec la version 7 d'Oracle les FOREIGN KEYs ont fait faire un bond considérable à propos de la qualité des données et ont permis de réduire considérablement les programmes d'application. Ne pas les utiliser c'est retourner en arrière d'au moins 20 ans donc.

En version 6, Oracle autorisait la syntaxe FOREIGN KEY ... REFERENCES conscient qu'il fallait vraiment implanter le concept.

MySQL fait de même avec les contraintes CHECK....... en 2009....

[fsmrel]

Envoyé par Waldar

Je pense que Jester parlait d'insérer dans une table client une ligne CLIENT INCONNU avec un code reconnaissable ('.' ou -1 par exemple) sur laquelle faire pointer du CA.
On reste alors dans une relation PK/FK classique, le CA est bien représenté dans les tableaux de bord des directeurs, et rien n'empêche une alimentation ultérieure de corriger la donnée.

Avez-vous tenu compte du sens de ce que j’ai écrit ? Quand pour votre part vous écrivez : « On reste alors dans une relation PK/FK classique », certes, à la lettre cela marche, mais ne convient pas dans l’esprit, car en l’occurrence on passe dans une toute autre dimension. Je répète qu’on ne doit pas détourner la finalité de la relation PK/FK pour un bricolage technique de circonstance réalisé a posteriori, à la va-vite. En bon logicien, Ted Codd a défini l’intégrité référentielle en tenant compte fondamentalement de la dimension ontologique et sémantique des choses, et il est bon qu’à notre tour nous ne perdions pas de vue cette dimension. En conséquence, nous devons prendre en compte les situations du type « Contrat sans titulaire » et les exprimer dans le MCD (Modèle Conceptuel de Données) ou le DC (Diagramme de classes), et ne pas nous contenter systématiquement de cache-misère, corriger les données après remise des tableaux de bord (à moins que cette façon de procéder fasse l’objet de recettes ad-hoc, finissant par être érigées en une fort médiocre « théorie »...)