RE: Circular FK's

We have had a model where relations of a company had several types of contact channels (adresses, e-mail, telephone) and possibly several occurences of each. We also had to note the default contact channel for each type for the relation. Which gives a foreign key from contact channel referencing the relation and a foreign key from relation which references the default contact channel. And you have to postpone the creation of foreign keys until you have created both tables with primary keys. In create table terms:

CREATE TABLE relation (
relid NUMBER NOT NULL,
some_column VARCHAR2(50),

default_addres NUMBER
default_email NUMBER
default_telephone NUMBER

ALTER TABLE relation
ADD (CONSTRAINT relation_pk PRIMARY KEY (relid));

CREATE TABLE contact_channel (
ccid NUMBER NOT NULL,
relid NUMBER NOT NULL,
type VARCHAR2(1),
data VARCHAR2 (30)
);

ALTER TABLE contact_channel
ADD (CONSTRAINT contact_channel_pk PRIMARY KEY (ccid));

ALTER TABLE relation
ADD (CONSTRAINT relation_fk1 FOREIGN KEY (default_addres)

    REFERENCES contact_channel (ccid));

ALTER TABLE contact_channel
ADD (CONSTRAINT contact_channel_fk1 FOREIGN KEY (relid)

    REFERENCES relation (relid));  

> -----Oorspronkelijk bericht-----
> Van: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] Namens Post, Ethan
> Verzonden: vrijdag 4 november 2005 16:34
> Aan: Oracle-L Freelists
> Onderwerp: 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