13. Januar 2009

Verzögerte Constraint-Prüfung: deferrable Constraints

English title: Circular Foreign Key Constraints with "deferred Constrains"

Wusstet Ihr schon, dass man einen Constraint in der Oracle-Datenbank deferred deklarieren kann, also dass er "verzögert" geprüft werden soll?
Did you know ... that a database constraint can be checked deferred?
Zunächst stellt sich die Frage, was "verzögert" überhaupt bedeutet - die Antwort ist einfach - ein als deferred deklarierter Constraint wird erst beim COMMIT überprüft und nicht sofort beim Ausführen des jeweiligen DML-Kommandos. Daber wozu braucht man das?
The first question arising is what deferred means: The answer is simple. Deferred means that the particular constraint is checked after the transaction is finished with a COMMIT. The "normal" behaviour is immediate checking just after the DML command was executed.
Angenommen, wir haben zwei Tabellen: KUNDE und ADRESSEN. Ein Kunde kann mehrere Adressen haben, daher die 1:n-Beziehung. Aber eine der Adressen soll als "primäre" Anschrift deklariert werden. Insofern würde man die Tabellen wie folgt anlegen ...
Imagine two tables: KUNDEN (for Customers) and and ADRESSEN (for addresses). One customer can have multiple addresses but one of these addresses must be marked as "primary" address. So the tables are being created as follows ...
create table kunden(
  id       number (10)   not null,
  name     varchar2(200) not null,
  prim_adr number(10)    not null,
  constraint pk_kunden primary key (id)
)
/

create table adressen(
  id       number(10)    not null,
  kunde_id number(10)    not null,
  strasse  varchar2(200),
  plz      number(5),
  ort      varchar2(200),
  constraint pk_adressen primary key (id)
)
/

alter table kunden add constraint fk_kunde_prim_adr foreign key (prim_adr) 
  references adressen (id)
/

alter table adressen add constraint fk_adressen_kunde foreign key (kunde_id)
  references kunden(id)
/
... was eine schöne Überkreuzbeziehung darstellt. Das Problem ist nun nur, dass man hier keinen einzigen Satz eingefügt bekommt. Denn um einen Kunden zu erzeugen, muss erstmal eine Adresse vorhanden sein; und um eine Adresse anlegen zu können, braucht man einen Kunden - Die Katze beißt sich also in den Schwanz.
... which means that you cannot insert any row. The foreign keys model a circular reference - to create a customer you need the primary address. But you need the customer in order to create an address. So you're chasing your own tail.
Es gibt allerdings schon seit Oracle8i eine Lösung dafür - den als deferred markierten Constraint - was bedeutet, dass die Prüfung erst beim COMMIT erfolgt. Und zwar gibt es hierfür zwei Einstellungen. Zunächst muss ein Constraint als deferrable deklariert sein. Zusätzlich muss die Ausführung dann noch auf deferred gesetzt werden. Für dieses Beispiel würde man die Fremdschlüssel-Constraints dann so anlegen:
But there is a solution. Since Oracle8i there are deferrable constraints - the constraint is created in that manner that it's checking is being performed after the commit. This is being achieved in two steps: First the constraint must be marked as deferrable and then the check setting must be changed from immediate to deferred. The following constraint definitions do both:
alter table kunden add constraint fk_kunde_prim_adr foreign key (prim_adr) 
  references adressen (id)
  deferrable initially deferred
/

alter table adressen add constraint fk_adressen_kunde foreign key (kunde_id)
  references kunden(id)
  deferrable initially deferred
/
Nun kann man Sätze in die Tabellen einfügen - das folgende Beispiel fügt einen Kunden ein, "vergißt" aber dann die Adressen ...
Now you can create rows - the following example creates a customer - but omits the address. The commit command raises the error message.
SQL> insert into kunden values (1, 'Testkunde', 1);

1 Zeile wurde erstellt.

SQL> commit;
commit
*
FEHLER in Zeile 1:
ORA-02091: Transaktion wurde zurückgesetzt
ORA-02291: Integritäts-Constraint (PARTNER.FK_KUNDE_PRIM_ADR) verletzt -
übergeordneter Schlüssel nicht gefunden
Wenn man zusätzlich die Adressen einfügt, ist alles in Ordnung:
If the addresses get created in the same transaction everything is well:
SQL> insert into kunden values (1, 'Testkunde', 1);

1 Zeile wurde erstellt.

SQL> insert into adressen values (1, 1, 'Musterstr. 1','80912','München');

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.
Obwohl hier also mit Fremdschlüsseln ein "Zirkelschluß" implementiert wurde, kann man trotzdem problemlos Sätze einfügen - die Prüfung findet eben erst mit Abschluß der Transaktion statt. Das ist, finde ich, ein sehr nettes Feature der Oracle-Datenbank.
So the circular reference is not a problem anymore - the constraint checking isbeing deferred to the end of the transaction. This is IMHO a very nice database feature.
Wenn ein Constraint als deferrable deklariert ist, kann er dennoch sofort (immediate) geprüft werden - dass geht dann wie folgt:
Deferred constraints also allow immediate checking - you just have to switch it (for your session).
SQL> set constraint fk_kunde_prim_adr immediate;

Constraint wurde festgelegt.
... oder für alle ...
... or for all constraints ...
SQL> set constraint all immediate;

Constraint wurde festgelegt.
Mit set constraint [[Name]|all] [deferred|immediate] kann das Verhalten der Constraints pro Session umgeschaltet werden - Grundvoraussetzung ist jedoch, dass der Constraint deferrable ist.
The set constraint [[Name]|all] [deferred|immediate] command switches the checking behaviour for one or all constraints between immediate and deferred. It's only valid for constraints created as deferrable.

Kommentare:

Friedhold Matz hat gesagt…

Hallo Carsten,

besten Dank auch für diesen wertvollen Hinweis!
Interessant scheint mir die Anwendung der verzögerten Constraint-Checks beim Update mit natürlichen Schlüssel zu sein.
Man hat sich schon so an die künstlichen, redundanten Schlüssel gewöhnt..

Grüße,
Friedhold

Fina hat gesagt…

Vielen Dank für deinen Post! Sehr schön und kurz erklärt ^__^

LG
Fina

Anonym hat gesagt…

gute Erklärung. Danke

Beliebte Postings