Pourquoi les clés étrangères sont-elles plus utilisées en théorie qu'en pratique?
quand vous étudiez la théorie relationnelle, les clés étrangères sont, bien sûr, obligatoires. Mais dans la pratique, à chaque endroit où j'ai travaillé, les produits de table et les jointures sont toujours faits en spécifiant explicitement les clés dans la requête, au lieu de compter sur des clés étrangères dans le SGBD.
de cette façon, vous pourriez bien sûr rejoindre deux tables par des champs qui ne sont pas censés être des clés étrangères, ayant des résultats inattendus.
Pourquoi pensez-vous que c'est? Ne devrait pas DBMSs appliquer que des jointures et des produits soient faits seulement par des clés étrangères?
EDIT: Merci pour toutes les réponses. Il est clair pour moi maintenant que la raison principale de FKs est l'intégrité de référence. Mais si vous concevez une DB, toutes les relations dans le modèle (c'est-à-dire les flèches dans L'ERD) deviennent des clés étrangères, au moins en théorie, que vous les définissiez ou non comme telles dans votre SGBD, elles sont sémantiquement FKs. Je ne peux pas imaginer le besoin de rejoindre les tables par des champs qui ne sont pas des FKs. est-ce que quelqu'un peut donner un exemple qui fait sens?
PS:je suis conscient du fait que les relations N: M deviennent des tables séparées et pas des clés étrangères, je l'ai simplement omise pour des raisons de simplicité.
17 réponses
la raison pour laquelle des contraintes de clé étrangère existent est de garantir que les lignes référencées existent.
"La clé étrangère identifie une colonne ou un ensemble de colonnes dans une table qui fait référence à une colonne ou un ensemble de colonnes d'une autre table. Les valeurs dans une rangée des colonnes de référence doivent apparaître dans une seule rangée du tableau de référence.
ainsi, une ligne dans le tableau de référence ne peut pas contenir des valeurs qui n'existent pas dans le tableau référencé (sauf potentiellement nul). De cette façon, des références peuvent être faites pour relier l'information ensemble et c'est un élément essentiel de la normalisation des bases de données."( Wikipedia )
RE: Votre question: "je ne peux pas imaginer la nécessité de joindre des tables de champs qui ne sont pas FKs":
lors de la définition d'une contrainte de clé étrangère, la ou les colonnes du tableau de référence doivent être les principales la clé de la table référencée, ou au moins une clé candidate.
lorsqu'on fait des jointures, il n'est pas nécessaire de se joindre avec des clés primaires ou des clés candidates.
voici un exemple qui pourrait avoir un sens:
CREATE TABLE clients (
client_id uniqueidentifier NOT NULL,
client_name nvarchar(250) NOT NULL,
client_country char(2) NOT NULL
);
CREATE TABLE suppliers (
supplier_id uniqueidentifier NOT NULL,
supplier_name nvarchar(250) NOT NULL,
supplier_country char(2) NOT NULL
);
puis interrogez comme suit:
SELECT
client_name, supplier_name, client_country
FROM
clients
INNER JOIN
suppliers ON (clients.client_country = suppliers.supplier_country)
ORDER BY
client_country;
un autre cas où ces jointures ont du sens est celui des bases de données qui offrent des fonctionnalités géospatiales, comme SQL Server 2008 ou Postgres avec PostGIS. Vous sera capable de faire des requêtes comme celles-ci:
SELECT
state, electorate
FROM
electorates
INNER JOIN
postcodes on (postcodes.Location.STIntersects(electorates.Location) = 1);
Source: ConceptDev - SQL Server 2008 Géographie: STIntersects, STArea
vous pouvez voir un autre exemple géospatial semblable dans la réponse acceptée au problème de requête POST " Sql 2008 - quel LatLong existe dans un polygone de géographie? ":
SELECT
G.Name, COUNT(CL.Id)
FROM
GeoShapes G
INNER JOIN
CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1
GROUP BY
G.Name;
ce sont tous des jointures SQL valides qui n'ont rien à voir avec des touches et candidat clés, et peut encore être utile dans la pratique.
les clés étrangères ont moins à voir avec les jointures qu'avec le maintien de l'intégrité de la base de données. La preuve de cela est que vous pouvez joindre les tables de n'importe quelle façon que vous voulez, même dans des façons qui ne sont pas nécessairement sensées.
Je ne peux pas imaginer la nécessité de joindre les tables par des champs qui ne sont pas FKs. Quelqu'un peut-il donner un exemple qui fait sens?
FOREIGN KEY s ne peut être utilisé pour renforcer l'intégrité référentielle que si la relation entre les entités du modèle ER se reflète avec un équijoin entre deux relations dans le modèle relationnel.
Ce n'est pas toujours vrai.
voici un exemple tiré de l'article de mon blog que j'ai écrit il y a quelque temps:
Ce modèle décrit des marchandises et des gammes de prix:
et voici la mise en œuvre relationnelle du modèle:
CREATE TABLE Goods (ID, Name, Price)
CREATE TABLE PriceRange (Price, Bonus)
Comme vous pouvez le voir, le PriceRange le tableau n'a qu'un attribut lié au prix, Price , mais le modèle a deux attributs: StartPrice et EndPrice .
c'est parce que le modèle relationnel permet de transformer les ensembles et l'entité PriceRange peut être facilement reconstruit en utilisant des opérations SQL .
Goods
ID Name Price
1 Wormy apple 0.09
2 Bangkok durian 9.99
3 Densuke watermelon 999.99
4 White truffle 99999.99
PriceRange
Price Bonus
0.01 1%
1.00 3%
100.00 10%
10000.00 30%
nous stockons seulement la limite inférieure de chaque gamme. La limite supérieure peut facilement être déduite.
Voici la requête pour trouver le bonus pour chaque bien:
SELECT *
FROM Goods
JOIN PriceRange
ON PriceRange.Price =
(
SELECT MAX(Price)
FROM PriceRange
WHERE PriceRange.Price <= Goods.Price
)
nous voyons que ce modèle relationnel implémente assez bien le Modèle ER, mais aucune clé étrangère ne peut être déclarée entre ces relations, puisque l'opération utilisée pour les lier n'est pas un equijoin.
Non, l'application n'est pas nécessaire; elle interdirait certaines fonctionnalités utiles, telles que la possibilité de surcharger les colonnes. Bien que ce type d'utilisation ne soit pas idéal, elle est utile dans certaines situations réelles.
l'utilisation appropriée pour les contraintes de clé étrangère est tout comme cela; une contrainte sur les valeurs ajoutées à une colonne donnée qui assure que leurs lignes référencées existent.
Il convient de noter qu'un manque important de clé étrangère contraintes sur un schéma donné est une mauvaise "odeur", et peut indiquer certains de graves problèmes de conception.
vous pouvez Vous joindre à n'importe quelle expression. Que vous définissiez des clés étrangères dans votre base de données ou non est sans importance. Les touches étrangères restreignent L'insertion / la mise à jour / la suppression, pas la sélection.
alors pourquoi beaucoup de projets sautent-ils la définition des clés étrangères? Il y a plusieurs raisons:
-
le modèle de données est mal conçu et nécessite des références brisées (exemples: associations polymorphiques, EAV).
-
Les codeurs ont pu entendre que "les clés étrangères sont lentes" alors ils les laissent tomber. En fait, le travail supplémentaire que vous avez à faire pour assurer la cohérence des données lorsque vous ne pouvez pas compter sur des clés étrangères rend votre application beaucoup moins efficace. L'optimisation prématurée sans mesurer réellement le bénéfice est un problème commun.
-
les contraintes gênent certaines tâches de nettoyage des données. Parfois, vous devez briser les références Temporairement que vous refactor données. De nombreux SGBDR permettent contraintes d'être désactivé, mais parfois les programmeurs décident qu'il est plus facile de laisser eux désactivé. S'il y a un besoin fréquent de désactiver les contraintes, cela indique probablement une conception de base de données gravement défectueuse.
clés étrangères utilisées de la manière que vous décrivez n'est pas la façon dont elles sont destinées à être utilisées. Ils sont destinés à s'assurer que si un document dépend logiquement d'un document correspondant exister ailleurs, que ce document correspondant est effectivement là.
je crois que si les développeurs/AD ont le temps de (A) développer de bons noms pour leurs tables et leurs champs, OU (B) définir des contraintes étendues de clé étrangère, l'option A est le choix facile. J'ai travaillé dans les deux situations. Où de nombreuses contraintes ont été invoqués pour maintenir l'ordre et empêcher les gens de vissage des choses peut vraiment devenir un gâchis.
il faut beaucoup d'efforts pour garder toutes vos contraintes de clé étrangère à jour pendant le développement, le temps que vous pourriez passer sur d'autres tâches de grande valeur que vous avez à peine le temps pour. En revanche, dans les situations où vous avez de bonnes conventions de nommage, les clés étrangères sont instantanément claires. Les développeurs n'ont pas pour chercher des clés étrangères, ou essayez une requête pour voir si elle fonctionne; il suffit de voir les relations.
je pense que les contraintes des clés étrangères deviennent rapidement utiles au fur et à mesure que le nombre d'équipes différentes augmente à l'aide d'une base de données. Il devient difficile d'appliquer un système de nommage cohérent; la connaissance de la base de données devient fragmentée; il est facile pour les actions de la base de données d'avoir des conséquences involontaires pour une autre équipe.
Parce que, dans la pratique, la théorie n'est pas assez ;)
Sérieusement, dans mon expérience est principalement parce que la théorie n'est pas assez souple pour envisager toutes les possibilités que vous avez à gérer dans le monde réel. Seulement avec un cas extrêmement bizarre que vous devez stocker dans votre DB (ou quelque chose de plus commun, comme les colonnes de surcharge), vous devez sortir le FK et l'implémenter dans le DAL.
Peut-être vous pouvez développer une solution qui peut être archivée d'une manière totalement normalisée (par exemple), mais dans de nombreux cas, le travail nécessaire et/ou les résultats finaux ne valent pas assez.
mes deux cents.
sont construits pour permettre le plus grand nombre de solutions tout en travaillant selon leurs règles de base.
restreindre les jointures à des clés étrangères définies limiterait énormément la fonctionnalité, d'autant plus que la plupart des développements ne se produisent pas avec un DBA dédié ou un examen des procédures SQL/stockées.
ayant dit que, selon votre couche D'accès aux données, vous pourriez être requis de configurer des clés étrangères, pour utiliser la fonctionnalité. Exemple Linq to SQL.
clés étrangères ne sont pas utilisés aussi souvent que la théorie relationnelle suggère parce que DB/personnes de type relationnel n'écrivent pas beaucoup du code ou même concevoir les tables. Les programmeurs écrivent les tables de code/conception ou ont beaucoup d'influence sur la façon dont les tables sont conçues.
sur quel type d'applications de base de données travaillez-vous? La théorie que vous voyez souvent concerne l'utilisation de la base de données brute, dans ce cas, les contraintes peuvent être très utiles. Dans la pratique, les bases de données sont souvent utilisées comme la fin des applications plus importantes. Dans de nombreux cas, ces applications doivent valider les transactions elles-mêmes, et il serait inutile de le répéter dans la base de données.
envisager une application de vente, par exemple. Quand quelqu'un est saisie d'une pour obtenir l'adresse ou les informations sur la carte de crédit. Quand il ne trouve pas de client, il sera programmé pour faire quelque chose de raisonnable. S'il attendait jusqu'à ce qu'il a essayé d'insérer une rangée dans la table de commande pour découvrir qu'il n'y avait pas de client, il obtiendrait des commentaires plus lents et moins pratiques.
quelque chose doit maintenir l'intégrité de la base de données, mais le faire à l'intérieur du SGBD lui-même n'est pas toujours la meilleure façon.
je programmais depuis quelques décennies, bien avant que les bases de données relationnelles ne deviennent la norme. Quand J'ai commencé à travailler avec MySQL quand je me suis enseigné Le PHP, j'ai vu L'option de la clé étrangère et la toute première pensée était "Wow! C'est retardée."La raison n'être qu'un imbécile croit que le laboratoire dicte la réalité. Il était évident immédiatement qu'à moins de coder une application qui ne serait jamais, jamais , être changée, vous emballez votre application dans une coulée d'acier où la seule option est de construire plus de tables ou de trouver des solutions créatives.
cette première évaluation était née dans chaque application de production réelle que j'ai rencontrée. Non seulement la contrainte ralentit considérablement toutes les modifications, mais elle rend presque impossible la croissance de l'application, ce qui est requis pour une entreprise.
la seule raison que j'ai jamais trouvé pour les contraintes sur la table est paresseux codeurs. Pas prêt à écrire du code propre pour vérifier l'intégrité des données.
Michael
les clés étrangères sont extrêmement importantes, en particulier dans les bases de données où des requêtes manuelles sont exécutées ou pour lesquelles un logiciel est activement développé. Chaque requête non testée qui est exécutée sur la base de données a la possibilité de contenir une erreur. Des contraintes telles que les clés étrangères servent à mettre en évidence ces erreurs avant que l'incohérence ne soit introduite dans les données.
ces contraintes sont appliquées par le concepteur du schéma, et elles assurent que les données reste dans le modèle qu'il a envisagé. Si les contraintes ne sont pas là, alors tôt ou tard une requête introduira une certaine incohérence. L'incohérence conduira à des résultats imprévisibles à partir des requêtes, et est très difficile à inverser.
bonne question. Je me suis toujours demandé pourquoi SQL n'a pas une syntaxe comme
SELECT tbl1.col1, tbl2.col2
FROM tbl1
JOIN tbl2 USING(FK_tbl1_tbl2)
où FK_tbl1_tbl2 est une contrainte étrangère entre les tables. Ce serait incroyablement beaucoup plus utile que la jointure naturelle ou L'utilisation D'Oracle(col1, col2).
la raison principale est qu'il n'y a aucun moyen de les configurer sans une requête dans la plupart des outils GUI MySQL (Navicat, MySQL, etc.)
semble stupide mais je suis coupable de cela aussi bien puisque je n'ai pas la syntaxe mémorisée: /
une partie de cela pour moi est que (et oui, c'est une excuse bidon) L'UI dans le studio de gestion de Serveur SQL DE MS pour ajouter des clés étrangères est terrible .
une clé étrangère est une contrainte que" toute valeur dans la colonne x de la table a doit apparaître dans la colonne y de la table b", mais L'UI pour la spécifier dans le SSMS n'indique pas clairement quelle table vous jouez avec, qui est la table mère, qui est la table enfant, et ainsi de suite.
chaque fois que j'ai dû créer une clé étrangère, c'était par tâtonnements jusqu'à ce qu'elle semble fonctionner.
Je ne suis pas au courant d'un dialecte SQL qui rejoint automatiquement toutes les tables de clés étrangères implicitement. J'ai vu la génération de code et les outils de dictionnaires de données pour les inferter, mais le SQL est toujours explicite.
C'est pourquoi vous voyez que, dans la pratique, en SQL, toutes les jointures sont explicites.
dans la pratique, les bases de données sans contraintes FK ont tendance à avoir des problèmes d'intégrité, puisqu'il n'y a pas de contrainte pour exiger la clé exister. Donc c'est certainement plus pratique d'avoir autant de contraintes que possible - il protège l'intégrité et permet à l'optimiseur et les autres utilisateurs. Comme pour toute pratique exemplaire, il est également important de savoir quand (si jamais) enfreindre la règle.
quant à savoir pourquoi vous pouvez faire une jointure qui ne correspond pas à une contrainte clé étrangère entre ces tables, il y a une myriade d'exemples. En particulier dans le cas de clés en composite avec jointures partielles, je trouve que cela est souvent nécessaire. Nous avons souvent rejoignez sur les tables en utilisant des versions partielles de leurs clés primaires dans le stockage de données.
vous pouvez également être intéressé par cet article sur l'élimination de clé étrangère jointure par l'optimizer.
clé Étrangère est couplage . Dans la programmation, le couplage est rarement bon.