Re: circular relationships ok?

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 - 18:21:54 CET