RE: Circular FK's

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Fri, 4 Nov 2005 17:02:35 +0100
Message-Id: <20051104160235.94A8C2177BE@turing.freelists.org>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 04 2005 - 10:04:42 CST