RE: Circular FK's

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Fri, 4 Nov 2005 10:20:59 -0600
Message-ID: <1F989681BA05FA4CAD9FA849ED852057013C542F@pscdalpexch01.perotsystems.net>

So essentially you are saying "don't add a department unless you have an employee running it" and "don't add an employee unless he is in a department".

That makes sense.

Thanks!

-----Original Message-----

From: Lex de Haan [mailto:lex.de.haan_at_naturaljoin.nl] Sent: Friday, November 04, 2005 10:03 AM To: Post, Ethan; 'Oracle-L Freelists'
Subject: RE: Circular FK's

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

--

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