Fehlermeldung beim Erstellen eines Primary Keys

Hallo,
Ich habe in meiner Datenbank Tabellen, die aus Objekten bestehen: z.B.
CREATE TYPE dbobjverwalt AS (
iCountry bigint
, iRegion bigint
, iSubregion bigint
, iTown bigint
, iChangeId integer
, iInputId integer
, iOriginalFormat bigint
, iLagerraum bigint
, iLagerregal bigint
, iDatentraeger bigint
, strDatentraegerNr text
, iChangeDate bigint
, iSourceDate bigint
, strNotes text
, iVsLevel bigint
, iIdent bigint
, iArchiveFormat bigint
, iArchiveStatus bigint
, iArchiveDate bigint
, iDataEnabled smallint
, iIntArchiveStatus bigint

);

CREATE TABLE dbtbladrg (
objverwalt DBOBJVERWALT,
objfref DBOBJFREF,
objquicklook DBOBJQUICKLOOK,
objstore DBOBJSTORE,
objadrg DBOBJADRG,
objmbr DBOBJMBR
) ;

Nun möchte ich auf diese Tabellen Indexe und Constraints anlegen. Einen Unique Index konnte ich anlegen:
CREATE UNIQUE INDEX uk_dbtbladrg ON dbtbladrg (((objverwalt).isourcedate),((objverwalt).iarchiveformat),((objadrg).ieditionnr),((objadrg).strstocknumber),((objadrg).strkachelname),((objadrg).iadrgsued),((objadrg).iadrgnord),((objadrg).iadrgwest),((objadrg).iadrgost));

Interessanterweise konnte ich mit der gleichen Spaltenkombination keinen Primray Key anlegen.
Setze ich folgenden Befehl ab:
ALTER TABLE dbtbladrg ADD PRIMARY KEY (((objverwalt).isourcedate),((objverwalt).iarchiveformat),((objadrg).ieditionnr),((objadrg).strstocknumber),((objadrg).strkachelname),((objadrg).iadrgsued),((objadrg).iadrgnord),((objadrg).iadrgwest),((objadrg).iadrgost));
bekomme ich die Meldung
FEHLER: Syntaxfehler bei »(«
ZEILE 1: ALTER TABLE dbtbladrg ADD PRIMARY KEY (((objverwalt).isource…

Was mache ich hier falsch?

Danke für die Hilfe. Doreen

Ich persönlich bin überhaupt kein Fan der “Objekt Typen” von Postgres - vor allem als Datentyp für eine Spalte.

Mit einem vernünftig normalisierten Modell wäre das viel einfacher und vermutlich auch effizienter.

Hast Du einen bestimmten Grund das so umständlich zu machen?

Die Tabelle dbtbladrg sollte vermutlich mehr als eine Tabelle sein, aber bei den kryptischen Namen ist das schwer zu beurteilen.

Die Notation mit einem Präfix für den Datentyp ist auch eher ungewöhnlich im Datenbankumfeld - zumal die Groß/Kleinschreibung nicht gespeichert wird. Aus iIntArchiveStatus wird also iintarchivestatus und aus iIdent wird iident

1 „Gefällt mir“

Wenn Du versuchst den Primary Key auf Basis des Unique Index anzulegen ist die Fehlermeldung übrigens klarer:

ALTER TABLE dbtbladrg ADD PRIMARY KEY using index uk_dbtbladrg;
ERROR: index "uk_dbtbladrg" contains expressions
   Detail: Cannot create a primary key or unique constraint using such an index.

Danke. Das hilft weiter. Die Möglichkeit den Index zu nutzen hatte ich überlesen.

Ich teste eine Migration aus Oracle. Wir haben bestimmte Objekte, die wir in hunderten von Tabellen nutzen: also entschieden wir uns für Objectorientierte Tabellen. So können wir Änderungen an nur 1 Stelle überall durchreichen.
Jetzt muss ich mal schauen, ob diese Einschränkung Probleme macht, ob ich also die PKs für Foreign Keys brauche…

Würde ich nicht machen. Auf lange Sicht wird euch das viel mehr Probleme bereiten als ihr vermeintlich mit dem komplizierteren Setup einspart. Habt Ihr das in Oracle auch so gemacht?

Normalerweise würde man eine Tabelle anlegen und die an den “hunderten” von Stellen via Fremdschlüssel referenzieren. Da muss man Änderungen auch nur an einer Stelle machen.

OK, das habe ich jetzt nicht verstanden. Wenn ich Tabellen habe, wo ich Werte für OBJVERWALT, OBJFREF… speichern will, wie komme ich da mit einem Fremdschlüssel weiter?
Vor allem, da ich in jeder Tabelle andere Spaltenkombinationen aus den Objekten als Primary Key definieren muss?
Wenn also ein anderes Datendesign sinnvoll sein könnte, bin ich offen für Ideen.
Bei Oracle kamen wir durchaus gut klar mit diesem Datendesign!

Ein anderes Problem mit der gleichen Ausgangslage, darum stelle ich es hierein. Kann es sein, dass es auch nicht erlaubt ist, Foreign Keys auf Objektorientierte Tabellen zu vergeben?
Hier ein Bsp:

ALTER TABLE dbtbladrg ADD CONSTRAINT sys_c0022958 FOREIGN KEY (((objverwalt).iarchiveformat)) REFERENCES dbdomdataformat(idomainvalue) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
FEHLER: Syntaxfehler bei »(«
ZEILE 1: …btbladrg ADD CONSTRAINT sys_c0022958 FOREIGN KEY (((objverwa…

create table dbobjverwalt 
(
  id                  integer primary key,
  icountry            bigint,
  iregion             bigint,
  isubregion          bigint,
  itown               bigint,
  ichangeid           integer,
  iinputid            integer,
  ioriginalformat     bigint,
  ilagerraum          bigint,
  ilagerregal         bigint,
  idatentraeger       bigint,
  strdatentraegernr   text,
  ichangedate         bigint,
  isourcedate         bigint,
  strnotes            text,
  ivslevel            bigint,
  iident              bigint,
  iarchiveformat      bigint,
  iarchivestatus      bigint,
  iarchivedate        bigint,
  idataenabled        smallint,
  iintarchivestatus   bigint
);

create table dbtbladrg 
(
  objverwalt     int references dbobjverwalt,
  ... 
);

:star_struck: Das probiere ich gleich mal. Frau lernt nie aus

Ich bin beim Ausprobieren. Indexe auf drunterliegende Tabellen (keine Kombinationen aus verschiedenen Unterobjekten/Tabellen) kann ich direkt auf dbobjverwalt absetzen. Aber ich habe bisher noch keinen Weg gefunden, dann auf dbtbladrg einen Primary key abzusetzen.
ALTER TABLE dbtbladrg ADD PRIMARY KEY (((objverwalt).isourcedate),((objverwalt).iarchiveformat),((objadrg).ieditionnr),((objadrg).strstocknumber),((objadrg).strkachelname),((objadrg).iadrgsued),((objadrg).iadrgnord),((objadrg).iadrgwest),((objadrg).iadrgost));
funktioniert so nicht. Wie muss ich den Primary key definieren?
Auch das Erstellen des Indexes
CREATE UNIQUE INDEX uk_dbtbladrg ON dbtbladrg (((objverwalt).isourcedate),((objverwalt).iarchiveformat),((objadrg).ieditionnr),((objadrg).strstocknumber),((objadrg).strkachelname),((objadrg).iadrgsued),((objadrg).iadrgnord),((objadrg).iadrgwest),((objadrg).iadrgost));
funktioniert so nicht.
Wie mache ich das mit der neuen Idee der Tabelle aus TabellenReferenzen?
CREATE TABLE dbtbladrg (
objverwalt int references DBOBJVERWALT,
objfref int references DBOBJFREF,
objquicklook int references DBOBJQUICKLOOK,
objstore int references DBOBJSTORE,
objadrg int references DBOBJADRG,
objmbr int references DBOBJMBR
) ;

Du wandelst die CREATE TYPE Befehle in entsprechende CREATE TABLE Befehle um und fügst einen Primärschlüssel hinzu wie oben bei dbobjverwalt - eventuell aber mit einem generated always as identity. Wenn es einen “natürlichen” Primäschlüssel gibt, dann sollte über den zusätzlich eine UNIQUE Index definiert werden.

Wobei ich ehrlich sagen muss, dass ich das Konzept Deiner Primärschlüssel nicht wirklich verstehe. Mir kommt es schon komisch vor, dass die so viele Spalten enthalten müssen, und dann auch noch Spalten aus verschiedenen Entitäten. Ohne die fachlichen Anforderungen an dieses Datenmodell zu kennen, kann ich leider nicht wirklich mehr dazu sagen.

Nochmal die Frage: wie war das denn in Oracle gelöst? Habt ihr da “nested tables” verwendet (was auch schon in Oracle ein ziemlich schlechte Idee war)

in Oracle haben wir
Objekte:
create type dbobjverwalt as object ();
Tabellen:
create table dbtbladrg (objverwalt dbobjverwalt…)
Constraints:
CHECK ( objVerwalt.iIdent IS NOT NULL )
PRIMARY KEY (OBJVERWALT.ISOURCEDATE, OBJVERWALT.IARCHIVEFORMAT, …)
FOREIGN KEY (OBJVERWALT.IARCHIVEFORMAT)
REFERENCES DBDOMDATAFORMAT (IDOMAINVALUE)
Indexe:
create index idx1 on DBTBLADRG (OBJVERWALT.IARCHIVEDATE)

also alles recht einfach ansprechbar.

Bei selects/updates musst du einen alias nutzen.
select a.objverwalt.iarchivdate from dbtbladrg a;

Das funktioniert sehr gut und unsere Anwendung ist schon ein paar Jahre erfolgreich unterwegs. Jetzt versuche ich gerade rauszubekommen, was bei einer möglichen Umstellung auf postgres beachtet werden muss und laufe auf unerwartete Probleme.

Also, hier noch mal meine Frage:
Kann es sein, dass es auch nicht erlaubt ist, Foreign Keys auf Objektorientierte Tabellen zu vergeben?

Also das wir in Postgres schwierig (und ich halte auch die Oracle Lösung für eher fragwürdig wenn ich ehrlich bin).

Nachdem man keinen Primäschlüssel mit Feldern von “records” definieren kann, kann man natürlich auch keinen Fremdschlüssel anlegen.

Mein Verdacht ist, dass selbst wenn ihr das irgendwie zum Laufen kriegt, ihr auf lange Sicht ständig Probleme bekommen werdet. Ich würde die Umstellung als Chance sehen, und das Datenmodell vernünftig aufzuräumen und sauber normalisiert von vorne anfangen. Das wird sicherlich ein deutlich höherer Aufwand bei der Migration der Anwendung und der Daten aber ich denke auf lange Sicht rentiert sich das.

Mein FK soll auf eine flache Domaintabelle verweisen (DBDOM…), auf die ich ganz einfach einen Primary Key anlegen kann.
ALTER TABLE dbtbladrg ADD CONSTRAINT sys_c0022958 FOREIGN KEY (((objverwalt).iarchiveformat)) REFERENCES dbdomdataformat(idomainvalue) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
Das ist also eher weniger das Problem. Ich kann ja auch check-Constraints auf meine Objekt-Spalten anlegen:
ALTER TABLE dbtbladrg ADD CONSTRAINT sys_c0015924 CHECK ( (objverwalt).iarchiveformat IS NOT NULL);
Ich versuche mal in der Doku eine mögliche Ursache zu finden. Zu Dokumentationszwecken wäre es super, wenn ich weiss, WARUM es nicht funktioniert oder ob es nicht doch irgendwie gehen sollte.

Ich habe noch mal über Deinen Vorschlag mit dem geänderten Datenmodell nachgedacht (References statt Objekte). Dieser Vorschlag hilft nicht weiter. So kann ich ja nicht mit einem insert-Befehl alle Objekte/Tabellen befüllen, keinen logischen Schlüssel auf verschiedene Objekte/Tabellen-Spalten legen, der je Zieltabelle unterschiedlich sein kann… etc.
Evtl. müssen wir die Tabellen von Objekttabellen in flache Tabellen umwandeln. Begeistert mich nicht gerade…
Gibt es gute Möglichkeiten in PostgreSQL, Spalten als Objekte o.ä. zusammenzufassen und in anderen Tabellen wiederzuverwenden?

Hmm, tut mir leid, da kann ich Dir nicht helfen.

So eine de-normalisierte Struktur habe ich weder in Oracle noch in Postgres jemals verwendet (und würde ich auch nie verwenden).

Wenn in Postgres eine Tabelle angelegt wird, wird auch ein Typ gleichen Namens angelegt.

Wenn Du “Objekte” speichern willst, könntest Du eventuell Prozeduren oder Funktionen schreiben die Records dieser Typen entgegennehmen und dann in eine sauber normalisierte Struktur abspeichern. Du könntest Du auch Views anlegen die die normalisierten Tabellen als “Objekte” zurückliefern.

Sowas in der Art: Postgres 15 | db<>fiddle

OK, trotzdem danke. damit ist klar, dass wir bei Umstieg nach postgres “flache” Tabellen nutzen müssten. Das ist zumindestens schon mal eine Aussage.

Der Begriff den Du suchst ist: “normalisiert” :face_with_hand_over_mouth:

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.