Je suis un débutant en SQL / PostgreSQL, et j'avais une question conceptuelle sur les clés étrangères et les clés en général:
Disons que j'ai deux tableaux: le tableau A et le tableau B
A a un tas de colonnes, dont deux sont A.id, A.seq . La clé primaire est btree(A.id, A.seq) et elle a une contrainte de clé étrangère que A.id référence B.id Notez que A.seq est une colonne séquentielle (la première ligne a la valeur 1, la seconde a la valeur 2, etc.).
Maintenant, disons que B a un tas de colonnes, dont l'une est le B.id mentionné ci- B.id . La clé primaire est btree(B.id) .
J'ai les questions suivantes:
btree ? Quelle est la signification d'avoir deux noms de colonne dans l' btree(B.id) plutôt qu'un seul (comme dans btree(B.id) ).A référence B au lieu de B référençant A ? Pourquoi l'ordre est-il important en ce qui concerne les clés étrangères?Merci beaucoup! Veuillez me corriger si j'ai utilisé une terminologie incorrecte pour quoi que ce soit.
EDIT: J'utilise postgres
3 Réponses :
Votre structure de données n'a aucun sens. Pourquoi la clé primaire de A aurait-elle à la fois l' id et le name ? Normalement, ce serait juste id . Dans certains modèles de données, une version ou un horodatage peut être ajouté. Je ne peux pas penser à un modèle de données raisonnable où le name serait également inclus.
De plus, la clé étrangère de B devrait être à la fois id et name .
Mais, votre question est à quoi sert btree ? La plupart des bases de données n'ont pas une telle option. Une clé primaire serait généralement exprimée comme suit:
id int primary key; constraint unq_t_id primary key (id);
btree est un type d'index - en fait le type d'index par défaut dans toutes les bases de données que je connais. Les bases de données qui ont une pléthore de types d'index disponibles - tels que Postgres - vous pouvez spécifier le type d'index associé à la clé primaire.
Oui, j'utilise postgres (psql) et je ne suis pas sûr à 100% pourquoi btree est inclus: j'essaie de comprendre cela moi-même haha. En termes de clés - le nom de la première table est exactement ce que je l'ai appelé: en réalité, A.name est une colonne séquentielle (la première ligne est 1, la seconde est 2, etc.). La deuxième table n'a pas une telle colonne - c'est pkey est seulement B.id
J'ai changé la description maintenant pour faire référence à cette colonne comme A.seq place - j'espère que cela aide.
À vos questions:
Il existe plusieurs stratégies pour implémenter des clés uniques. La plus courante consiste à utiliser un index "unique" en utilisant une stratégie "b-tree". C'est ce que signifie "btree" dans PostgreSQL.
Avoir deux colonnes dans une clé dépend simplement de la façon dont vous souhaitez concevoir votre table. Lorsque vous avez une clé avec plus d'une colonne appelée "clé composite".
Quand A référence à B , les colonnes de B doivent représenter une "clé". Les colonnes de A ne représentent pas une clé, mais simplement une référence à une. En fait, les valeurs de A pour cette colonne peuvent être répétées; autrement dit, plusieurs lignes de A peuvent pointer vers la même ligne de B
Votre deuxième point était vraiment utile - il est un peu contre-intuitif que la soi-disant «clé primaire» d'une table soit en fait juste une référence à une clé réelle dans une deuxième table. Cela a du sens cependant. Si je pouvais poser une question complémentaire, alors: cela signifie-t-il que A est alors une entité faible? Parce qu'il ne contient en fait que des informations relatives à B ? Cela signifierait que la raison pour laquelle B ne peut pas référencer A est parce qu'il serait inutile pour une entité forte de référencer une entité faible?
Un index btree stocke les valeurs dans un ordre trié, ce qui signifie que vous pouvez non seulement rechercher une seule valeur de clé primaire, mais également rechercher efficacement une plage de valeurs:
SELECT ... WHERE id between 6060842 AND 8675309
PostgreSQL prend également en charge d'autres types d'index , mais seul btree est pris en charge pour un index unique (par exemple, la clé primaire).
Dans votre table B , la clé primaire étant un id colonne unique signifie qu'une seule ligne peut exister pour chaque valeur de id . En d'autres termes, il est unique, et si vous recherchez une valeur par clé primaire, il trouvera au plus une ligne (il peut également trouver zéro ligne si vous n'avez pas de ligne avec cette valeur).
La clé primaire de votre table A est pour (id, seq) . Cela signifie que vous pouvez avoir plusieurs lignes pour chaque valeur de id . Vous pouvez également avoir plusieurs lignes pour chaque valeur de seq tant qu'elles correspondent à des valeurs d' id différentes. La combinaison doit cependant être unique. Vous ne pouvez pas avoir plus d'une ligne avec la même paire de valeurs.
Lorsqu'une clé étrangère dans A référence à B , cela signifie que la ligne doit exister dans B avant que vous ne soyez autorisé à stocker la ligne dans A avec la même valeur d' id . Mais l'inverse n'est pas nécessaire.
Exemple:
Supposons que B est pour les utilisateurs et A pour les téléphones. Vous devez stocker un utilisateur avant de pouvoir stocker un enregistrement téléphonique pour cet utilisateur. Vous pouvez stocker un ou plusieurs téléphones pour cet utilisateur, un par ligne dans le tableau A Nous disons qu'une ligne dans A donc référence à la ligne utilisateur dans B , ce qui signifie «ce téléphone appartient à l'utilisateur # 1234».
Mais l'inverse n'est pas limité. Un utilisateur peut ne pas avoir de téléphone (du moins inconnu de cette base de données), il n'est donc pas nécessaire que B fasse référence à A En d'autres termes, il n'est pas nécessaire pour un utilisateur d'avoir un téléphone. Vous pouvez stocker une ligne dans B (l'utilisateur) même s'il n'y a pas de ligne dans A (les téléphones) pour cet utilisateur.
La référence signifie également que vous n'êtes pas autorisé à DELETE FROM B WHERE id = ? s'il y a des lignes dans une autre table qui font référence à cette ligne donnée dans B La suppression de cet utilisateur rendrait ces autres lignes orphelines. Personne ne serait en mesure de savoir à qui appartenaient ces téléphones, si la ligne d'utilisateurs auxquels ils font référence est supprimée.
Merci pour l'exemple concret! La belle analogie téléphone / utilisateurs m'a vraiment conduit à la maison.