RE: Circular FK's
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <Ethan.Post@xxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Nov 2005 17:02:35 +0100
Hi Ethan,
sure, this is quite common.
I have such a construct in the demo tables of my SQL book:
each employee works for a certain existing department,
and each department has an existing employee as its manager.
under the current SQL implementations (such as Oracle)
you indeed need deferred constraint checking, unfortunately.
there is a pending proposal to add "multiple assignments"
to the ISO standard SQL language, allowing you to specify
syntax like the following example:
SQL> insert into emp (...) values (...) ,
2 insert into dept (...) values (...) );
note the comma at the end of the first line, followed by the
semicolon at the end of the second line; the two inserts
should succeed or fail as an atomic change to the database.
deferred constraint checking should be avoided,
because it can easily lead to logical data inconsistencies.
kind regards,
Lex.
---------------------------------------------------------------------
Jonathan Lewis Seminar http://www.naturaljoin.nl/events/seminars.html
---------------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Post, Ethan
Sent: Friday, November 04, 2005 16:34
To: Oracle-L Freelists
Subject: Circular FK's
Has anyone ever seen a reason for two tables to have foreign keys on each other
(different columns)? I am working with an application in which this is the case
on a couple tables. I have not checked but I am assuming you have to initially
defer the constraint for this to even work. I am trying to determine if there is
ever a legit reason for this.
--
//www.freelists.org/webpage/oracle-l
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
Other related posts:
