Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: circular relationships ok?

Re: circular relationships ok?

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 2 Mar 2006 09:21:54 -0800
Message-ID: <1141320113.983925.281840@v46g2000cwv.googlegroups.com>


Technically, you can declare cycles with a CREATE SCHEMA statement which brings all the schema objects into being all at once.

But the practical results are generally bad. A simple A->B and B->A cycle can prevent you from inserting or deleting from both tables ("To get a job, you need experience; to get experience, you need a job").

The other "gotcha" is A->B, A->C and B->C with cascaded actions. I change A, which fires actions in both B and C. The change in B fires an action in C. But the changes to C are different on the same rows
(say SET NULL and SET DEFAULT); which one takes effect? I one early
version of DB2, the answer was whoever was the last guy to touch C -- unpredictable.

We can split up your example and get rid of the cycle:

CREATE TABLE Personnel
 (employee_id INTEGER NOT NULL PRIMARY KEY,  employee_name VARCHAR(50) NOT NULL,
 ..);

CREATE TABLE Teams
(team_nbr INTEGER NOT NULL,

 employee_id INTEGER NOT NULL

     REFERENCES Personnel (employee_id)
   ON UPDATE CASCADE
   ON DELETE CASCADE,
 PRIMARY KEY (team_nbr, employee_id);
  job_title CHAR(10) NOT NULL

     CHECK (job_title in ('mgr', 'member', ..)),   ..);

I could also add a constraint to be sure each team has a single manager, etc. Received on Thu Mar 02 2006 - 11:21:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US