Contraintes de clés étrangères dans les relations plusieurs-à-plusieurs
Contexte
Nous construisons un blog pour une intro. les bases de données en cours de projet.
Dans notre blog, nous voulons être en mesure de définir Labels
sur Posts
. Le Labels
ne peut exister par eux-mêmes, ils ne le faire que s'ils sont liés à un Posts
. De cette façon, Labels
qui ne sont pas utilisés par tout Posts
ne faut pas rester dans la base de données.
Plus d'un Label
peut appartenir à un seul Post
et plus qu'un seul Post
pouvez utiliser un Label
.
Nous utilisons à la fois SQLite3 (local/test) et PostgreSQL (déploiement).
Mise en œuvre
Voici le SQL (SQLite3 saveur) que nous utilisons pour la création de ces deux tables, avec la table de relation:
Postes
CREATE TABLE IF NOT EXISTS Posts(
id INTEGER PRIMARY KEY AUTOINCREMENT,
authorId INTEGER,
title VARCHAR(255),
content TEXT,
imageURL VARCHAR(255),
date DATETIME,
FOREIGN KEY (authorId) REFERENCES Authors(id) ON DELETE SET NULL
)
Étiquettes
CREATE TABLE IF NOT EXISTS Labels(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) UNIQUE,
-- This is not working:
FOREIGN KEY (id) REFERENCES LabelPosts(labelId) ON DELETE CASCADE
)
LabelPosts (relation entre Post
[1..*] -- * Label
)
CREATE TABLE IF NOT EXISTS LabelPosts(
postId INTEGER,
labelId INTEGER,
PRIMARY KEY (postId, labelId),
FOREIGN KEY (postId) REFERENCES Posts(id) ON DELETE CASCADE
)
Problème
- À l'aide de SQLite3,
Labels
ne sont pas supprimés de la base de données lorsque j'ai supprimer toutes les références à partir de laLabelPosts
table. Je pense que pour la raison donnée par Postgres, malgré SQLite acceptant la table sans avertissement. - PostgreSQL se plaint que
labelId
n'est pas unique dansLabelPosts
ce qui est vrai et aussi nécessaire, car il est plusieurs-à-plusieurs:
pq: S:"ERREUR" R:"transformFkeyCheckAttrs" L:"6511" C:"42830" F:"tablecmds.c"
M:"il n'y a pas de contrainte unique correspondant donné les clés pour table référencée \"labelposts\""
Donc, je comprends que je suis en train de faire ma contrainte de mal. Cependant, je ne sais pas comment le faire correctement.
source d'informationauteur AntoineG
Vous devez vous connecter pour publier un commentaire.
C'est la mendicité pour les problèmes. Vous allez continuer à courir dans incompatibilités. Ou même ne pas remarquer que beaucoup plus tard, lorsque le dommage est fait. Ne pas le faire. L'utilisation de PostgreSQL localement, trop. Il est librement disponible pour la plupart des tous les OS. Pour une personne impliquée dans un "bases de données, cours de projet" c'est surprenant de la folie.
Dans PostgreSQL utiliser un
série
de la colonne au lieu de SQLiteAUTOINCREMENT
.Utilisation
timestamp
(outimestamptz
) au lieu dedatetime
.N'utilisez pas de cas mixtes identifiants.
N'utilisez pas non descriptif colonne des noms comme
id
. Jamais. C'est un anti-modèle introduit par demi-wit middleware et de l'Orm. Lorsque vous vous joignez à un couple de tables, vous vous retrouvez avec plusieurs colonnes de ce nomid
. C'est activement blessants.Il y a beaucoup de naming styles, mais la plupart conviennent qu'il est préférable d'avoir les termes au singulier comme les noms de table. C'est plus court et au moins aussi intuitive /logique.
label
paslabels
.Comme @Platz mentionné dans les commentairesvos contraintes de clés étrangères sont à l'envers. Ce n'est pas pour le débat, ils sont tout simplement mauvais.
Tout mis ensemble, il pourrait ressembler à ceci:
Déclencher
la fonction de déclenchement doit être créé avant le déclencheur.
Un simple
DELETE
de commande peuvent faire le travail. Pas de deuxième requête nécessaires - en particulier, aucunecount(*)
.EXISTS
est moins cher.Pas de guillemets simples autour de
plpgsql
. C'est un identifiant, n'est pas une valeur!Il n'y a pas de
CREATE OR REPLACE TRIGGER
dans PostgreSQL, encore. SimplementCREATE TRIGGER
.Une façon de réaliser le comportement que vous cherchez (supprimer les étiquettes inutilisées à partir de la base de données) serait d'utiliser des déclencheurs.
Vous pourriez essayer d'écrire quelque chose comme:
Fondamentalement, ce qui se passe ici est:
Posts
.LabelPosts
(grâce à votre contrainte de clé étrangère).LabelPosts
le déclencheur est activé, ce qui à son tour appelle la PostgreSQL fonction.labelId
en question. Si oui, puis il se termine sans aucune modification. Cependant, si il n'y a pas d'autres lignes dans la table de relation, l'étiquette n'est pas utilisé ailleurs et peut donc être supprimé.Labels
table, supprimant ainsi le (maintenant) inutilisé étiquette.Évidemment le nommage n'est pas le meilleur et il doit y avoir une tonne d'erreurs de syntaxe dans les il y, afin de voir ici et ici pour plus d'informations. Il peut y avoir de meilleures façons de prendre cette chose vers le bas, cependant pour le moment je ne peux pas penser à une méthode rapide qui ne serait pas détruire la belle générique-table à la recherche de la structure.
Bien que bare à l'esprit - il n'est généralement pas une bonne pratique pour surcharger votre base de données avec des déclencheurs. Il permet à chaque associé de la requête ou de la déclaration d'exécuter un tat plus lent & aussi, rend l'administration considérablement plus difficile. (Parfois, vous devez désactiver les déclencheurs d'effectuer certaines opérations DML, etc. selon la nature de vos déclencheurs).