INT[] - FKs

Hallo.

Hat jemand eine Idee, wie man eine Liste von IDs INT[] zu einem Fremdschlüssel definiert? Ich hatte bereits recherchiert und von mehreren Patches gelesen. Ich bin vermutlich auch nicht der einzige, der sich so etwas in seinen Projekten wünscht, da sich so eine Referenztabelle einsparen ließe. Aber egal, wie ich es versuche, bekomme ich Fehlermeldungen.

ALTER TABLE public."USER"
  ADD CONSTRAINT user_fkey
    FOREIGN KEY (EACH ELEMENT OF role_id)
    REFERENCES public."ROLES"(id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

Gibt es denn kein “EACH ELEMENT OF” in PostgreSQL?

Nein, geht nicht. Dafür sind arrays nicht gedacht. Wenn Du Deine Tabellen sauber normalisierst, kannst Du auch problemlos Fremdschlüssel definieren.

Schade. :confused: Also doch eine Relationstabelle.
Wäre halt bei multipler Option ziemlich cool. :slight_smile:
Im oberen Beispiel könnte ein Nutzer ja mehreren Rollen zugehörig sein.

Oder beispielsweise eine Tabelle USER und eine Tabelle POSTINGS, man könnte dann in der Spalte LIKES alle Nutzer-IDs als Array hinterlegen, anstatt eine Relationstabelle LIKES dazwischen zu schieben. Wobei die Abfrage alle LIKES eine bestimmten USERs etwas schwieriger wäre.

Das ginge natürlich wenn ich auf die definierten Beziehungen/Schlüssel verzichte und es von meiner Backend-Logik in .NET Core prüfen lasse.
Aber ich will die klassischen DB-Schemas nutzen, wie sie vorgeschrieben sind.

Na gut. Dann weiß ich Bescheid.

Worum geht es denn?
Wenn es unbedingt denormalisiert sein soll, kannst Du FK Wirkung auch durch Trigger und Functions erreichen. Ob das dann unbedingt in ein Array gesteckt werden muss, wäre eine andere Frage.
Wenn man über die Daten joinen will, wird’s nochmal unschön, wäre aber auch möglich.

Ist die Frage, ob der Nutzen solche Kapriolen rechtfertigt.

Dachte an einen simplen ANY-JOIN (INNER)…

SELECT
  U.name "user",
  R.name "role"
FROM public."USER" U
JOIN public."ROLES" R 
  ON R.id = ANY(U.role_id)

Klappt eigentlich ganz gut…
Falls ein Fremdschlüssel vorkommt, der in der Referenztabelle nicht existiert, wird dieser nicht angezeigt.

Aber es ist eben kein korrekter “Fremdschlüssel”, quasi nur ein INT[]-Feld, daher fehlt die Bindung (SQL-Schema).
Wenn man darauf verzichten kann, eigentlich gar nicht schlecht. Ich bräuchte mal 100M Nutzer und 10K Rollen, um dann die Abfragen zu vergleichen, wenn ich korrekte Bindungen/Fremdschlüssel (via Relationstabelle) oder eben diese abfrage. Ich vermute aber fast, dass diese Abfrage schneller ist. Okay, als LEFT, bzw. INNER JOIN. Aber das spielt hier jetzt ja erstmal noch keine Rolle.

Ich werde dennoch eine korrekte Bindung mit Relationstabelle nutzen, falls ich irgendwann doch mal auf ein anderes DB-System wechsle, dass keine INT[]s zulässt.
Ergebnisse.png
roles.png
user.png

Also was ist Dein Problem?
“korrekte Bindung”:

  • wenn Du fehlerhafte Referenzen vermeiden möchtest, bau Dir einen Trigger, der einen Fehler wirft, wenn das eintritt
  • wenn Du andere Bedürfnisse hast, die z.B. Metainfos zu den FK betrifft, die gibt’s natürlich nicht
  • Anzeige dieser Zusammenhänge in DB Tools erfolgt also auch nicht, daraus ergibt sich vermutlich Dokumentationsbedarf
  • wenn Du cascade Funktionalität haben willst, musst du selbst bauen
  • wahrscheinlich gibt’s noch mehr Aspekte

Denormalisierung sollte nicht aus Bequemlichkeit erfolgen, sondern handfeste Probleme (idR Performance) lösen. Die implizit verlorene Konsistenz des Modells muss mit anderen Mitteln sicher gestellt werden. Das muss mit betrachtet und gelöst werden bei den Effekten (Kosten/Nutzen)
Gewonnene Performance wird idR. an anderer Stelle mit Performance und/oder Volumenverlust bezahlt.

Teste es doch einfach gleich, erzeuge 100 Mio Nutzer und 10000 Rollen (wer braucht 10000 Rollen?) und vergleiche und poste die Antwort auch gleich hier.