INT[] - FKs

Fragen und Probleme zu PL/pgSQL.
Antworten
Froschkoenig84
Neuer Benutzer
Beiträge: 3
Registriert: Mo 13. Mai 2019, 16:11

INT[] - FKs

Beitrag von Froschkoenig84 » Mo 13. Mai 2019, 16:59

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.

Code: Alles auswählen

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?

Bild

castorp
Erfahrener Benutzer
Beiträge: 1141
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: INT[] - FKs

Beitrag von castorp » Mo 13. Mai 2019, 17:03

Nein, geht nicht. Dafür sind arrays nicht gedacht. Wenn Du Deine Tabellen sauber normalisierst, kannst Du auch problemlos Fremdschlüssel definieren.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Froschkoenig84
Neuer Benutzer
Beiträge: 3
Registriert: Mo 13. Mai 2019, 16:11

Re: INT[] - FKs

Beitrag von Froschkoenig84 » Mo 13. Mai 2019, 20:08

Schade. :/ Also doch eine Relationstabelle.
Wäre halt bei multipler Option ziemlich cool. :)
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.

akretschmer
Erfahrener Benutzer
Beiträge: 7640
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: INT[] - FKs

Beitrag von akretschmer » Mo 13. Mai 2019, 21:12

Froschkoenig84 hat geschrieben:
Mo 13. Mai 2019, 20:08
Wobei die Abfrage alle LIKES eine bestimmten USERs etwas schwieriger wäre.

Das wäre wohl noch das geringste Problem ...
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pogomips
Erfahrener Benutzer
Beiträge: 105
Registriert: Fr 18. Nov 2016, 09:55

Re: INT[] - FKs

Beitrag von pogomips » Mo 13. Mai 2019, 22:16

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.

Froschkoenig84
Neuer Benutzer
Beiträge: 3
Registriert: Mo 13. Mai 2019, 16:11

Re: INT[] - FKs

Beitrag von Froschkoenig84 » Di 14. Mai 2019, 00:30

Dachte an einen simplen ANY-JOIN (INNER)...

Code: Alles auswählen

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.
Dateianhänge
user.png
user.png (109.74 KiB) 502 mal betrachtet
roles.png
roles.png (104.51 KiB) 502 mal betrachtet
Ergebnisse.png
Ergebnisse.png (106.46 KiB) 502 mal betrachtet

pogomips
Erfahrener Benutzer
Beiträge: 105
Registriert: Fr 18. Nov 2016, 09:55

Re: INT[] - FKs

Beitrag von pogomips » Di 14. Mai 2019, 19:57

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.

Antworten