Unique-ähnliche-Bedingungen über zwei Spalten in zwei Tabellen

[Hawkeye]

Hallo,
in meiner Dokumente-Verwaltung habe ich bisher in der Tabelle 'Dokumente' eine Spalte 'Dateiname' für Pfad- und Dateinamen benutzt. Eine Datei sollte dabei nur einmal vorkommen, weshalb ich dieser Spalte die Restriktion UNIQUE gab.

Damit ich den Pfad nicht bei jeder einzelnen Datei mit angeben muss, habe ich nun in der Tabelle 'Vorgang' eine Spalte 'Pfad' eingefügt. Über die 'Vorgang_ID' ist diese Tabelle mit der Tabelle 'Abschnitte' verknüpft. Die Tabelle 'Abschnitte' ist wiederum über die 'Abschnitt_ID' mit der Tabelle 'Dokumente' verknüpft, in der die Spalte 'Dateiname' steht.

Zusätzlich:
- Der Pfad muss nicht in 'Pfad' stehen, diese Spalte kann auch NULL sein.
- Der Pfad kann ganz oder teilweise (ergänzend zu 'Pad'; Unterordner) in der Spalte 'Dateiname' stehen.

Hat jemand eine Idee, wie ich einen UNIQUE-ähnlichen Check durchführen kann, dass eine Kombination aus Pfad und Dateinamen nur einmal vorkommt?

Viele Grüße
Hawkeye

 

[akretschmer]

edb=# create table hawkeye(id int generated always as identity primary key, path text not null, file text not null);
CREATE TABLE
edb=*# create unique index on hawkeye ((path || file));
CREATE INDEX
edb=*#
edb=*#
edb=*# insert into hawkeye (path, file) values ('/pfad/1/','file1');
INSERT 0 1
edb=*# insert into hawkeye (path, file) values ('/pfad/11/','file1');
INSERT 0 1
edb=*# insert into hawkeye (path, file) values ('/pfad/1/','file11');
INSERT 0 1
edb=*# insert into hawkeye (path, file) values ('/pfad/11/','file11');
INSERT 0 1
edb=*# insert into hawkeye (path, file) values ('/pfad/11/','file11');
FEHLER:  doppelter Schlüsselwert verletzt Unique-Constraint »hawkeye_expr_idx«
DETAIL:  Schlüssel »((path || file))=(/pfad/11/file11)« existiert bereits.
edb=*#

 

[Hawkeye]

Hallo akretschmer,
vielen Dank für Deine Antwort!
Das Problem ist nur, dass sich die beiden Spalten in zwei unterschiedlichen Tabellen befinden und ein Index sich meines Wissens nur auf eine Tabelle beziehen kann.

Viele Grüße
Hawkeye

 

[akretschmer]

Das Problem ist nur, dass sich die beiden Spalten in zwei unterschiedlichen Tabellen befinden und ein Index sich meines Wissens nur auf eine Tabelle beziehen kann.

Korrekt. Entweder am Modell was ändern, oder vielleicht via TRIGGER.

 

[dabadepdu]

Vorweg mal die Frage nach dem Anwendungsfall: was soll wirklich erreicht werden? Geht es wirklich um einen eindeutigen Dateinamen? Oder um eine eindeutige Datei? Könnte man alternativ z.B. den Namen eindeutig machen, beim Insert, automatisch?
Neben den Triggern gibt's noch Check Options für Views. Man definiert eine Bedingung in einem View (where clause) bei deren Verletzung das Insert mit Fehler abgelehnt wird, also wie bei einem Constraint. Der View würde natürlich ein Join der beiden Tabellen enthalten. Mir ist grad nicht klar, ob man damit die Eindeutigkeit regeln kann.
Eine weitere Möglichkeit ist eine SP, die das reguliert. Also ausschließliche Verwendung dieser SP für Inserts. Das finde ich transparenter als Trigger. Rechte müssen natürlich so definiert sein, dass man nicht hintenrum dagegen agieren kann.