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.