[Oracle] Kleines Beispiel zu zirkulären Fremdschlüsselbeziehungen

[Thomas Darimont]

Hallo,

wenn man eine zirkuläre Fremdschlüsselbeziehung zwischen zwei Tabellen abbilden möchte hat man
das Problem, das die "normal" definierten Foreign Key Constraints sofort beim Einfügen eines
Datensatzes in einer Tabelle geprüft wird. Dies verhindert dann realisieren der zirkulären Abhängigkeit zwischen
zwei Datensätzen, da man den einen nicht vor dem anderen einfügen kann - und umgekehrt.

Unter Oracle gibt es für diese Situation jedoch einen Ausweg: DEFERRABLE Constraints.
Damit lassen sich Constraints spezifizieren, die erst bei einem Commit ausgewertet werden.
Damit kann man dann ohne Probleme die gewünschte Datenkonstellation herstellen.

Hier ein kleines Beispiel dazu:

--clean up
 ALTER TABLE t1 DROP CONSTRAINT t1_fk; 
 ALTER TABLE t2 drop CONSTRAINT t2_fk;
 
 DROP TABLE t1;
 drop table t2;

-- Test Setup:
 create TABLE t1(
    pk INT NOT NULL
   ,fk INT not null 
   , constraint t1_pk PRIMARY KEY (pk)
 );
 
 create TABLE t2(
    pk INT NOT NULL
   ,fk INT not null
   , constraint t2_pk PRIMARY KEY(pk)
 );
 
--Hier wird per DEFERRABLE INITIALLY DEFERRED sichergestellt, dass die Constraint-Prüfung beim
--einfügen eines neuen Datensatzes erst am Ende der Transaktion durchgeführt wird.
--Dies erlaubt nun das einfügen von Daten mit zirkulären Fremdschlüsselbeziehungen.


 ALTER TABLE t1 ADD CONSTRAINT t1_fk FOREIGN KEY (fk) REFERENCES t2 (pk) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE t2 ADD CONSTRAINT t2_fk FOREIGN KEY (fk) REFERENCES t1 (pk) DEFERRABLE INITIALLY DEFERRED;
 
-- Daten einfügen
BEGIN
  INSERT INTO t1 VALUES(1,2);  
  insert into t2 values(2,1);  
  commit;
end;

-- Ausgabe:
SELECT * FROM t1
UNION ALL
SELECT * FROM t2
;

Ausgabe:

                                    PK                                     FK
-------------------------------------- --------------------------------------
                                     1                                      2 
                                     2                                      1

Einen interessanten Artikel zur verzögerten Constraint-Prüfung in Oracle findet man hier:
http://sql-plsql-de.blogspot.de/2009/01/verzgerte-constraint-prfung-deferrable.html


Gruß Tom