Re: [Q] circular foreign key constraints

In article <9p6a5.8812$7D2.179100_at_news.chello.at>,   "Nikolaus Rumm" <nikolaus.rumm_at_spamremoveme.chello.at> wrote:
> >
> > Van, your suggestion is good, except that Nikolaus expressly says he
> > doesn't need the M:M relationship. Instead his model only allows
 anyone
> > to attend at most one meeting.
> >
> > So the model is:
> >
> > Columns in Meeting:
> > MID, primary key
> > MName
> > ContactPersonID
> >
> > Columns in Person:
> > PID, primary key
> > Name
> > MID
> >
> > Then create FKs:
> > alter table Meeting add fk_cpid foreign key (ContactPersonID)
> > references Person (PID);
> > alter table Person add fk_mid foreign key (MID) references Meeting
> > (MID);
> >
> > Let me know if this is not clear.
> >
> > --
> > Yong Huang
> >
>
> Thank you, Yong (or Huang ?), that's exactly how I solved my problem.
> However I thought it would be possible to create the foreign key
 constraint
> when creating the table. Is the Oracle Designer able to generate the
 tables
> if there are circular constraints ?
>
> Thx
>
> Nikolaus

You're welcome. Yes, generally you can create foreign keys when you create the table. But in your case, you have to do at least one of them later.

Another thought, when you insert data, you can't use two consecutive insert statements, one inserting into Person, the other into Meeting. At least one of them has to leave one of the FK columns blank. Then a third statement UPDATE is used to set that FK value. If you don't follow this strategy, you have to disable the FK or make it deferrable first, if you're using Oracle 8. Then when you're done, reenable it.

--
Yong Huang

(yong321_at_yahoo.com)
(http://www.stormloader.com/yonghuang/)


Sent via Deja.com http://www.deja.com/
Before you buy.