Re: [Q] circular foreign key constraints

In article <Hd4a5.3922$I76.279578_at_monger.newsread.com>,   "Van Messner" <vmessner_at_bestweb.net> wrote:
> I didn't quite understand your setup since you mention person,
> contact_person etc. But let's say we have a table for persons and a
 table
> for meetings.
>
> In general one person may attend zero or more meetings, and one
 meeting must
> have one or more persons (Or, depending on the definition of meeting,
 two or
> more persons).
>
> This is what's called a many-to-many relationship and a common
 solution is
> to have a third table - person_meetings - which has a row for each
 person at
> each meeting. Person_meetings is foreign keyed to persons and also to
> meetings.
>
> Van
>
> Nikolaus Rumm <nikolaus.rumm_at_spamremoveme.chello.at> wrote in message
> news:uLY95.6849$7D2.116639_at_news.chello.at...
> > Hello,
> >
> > maybe this is a silly question, but my problem is serious:
> >
> > I have 2 tables, say 'Meeting' and 'Person', that are in a 1:n
 relationship,
> > where meeting is the 1 side and Person is the n side.
> > So Person has a foreign key constraint on Meeting. So far so good.
> > However, I need exactly one contact person per Meeting, so my
 approach is
 to
> > add an additional foreign key constraint between Meeting and Person.
> >
> > Well, sounds good, but I can't create the tables, as Oracle says
 that the
> > foreign key referrer is not known.
> > How to handle this situation ? Should I create the tables
 separately and
 add
> > the foreign key constraints manually ?
> >
> > Any suggestions ?
> >
> > Thx
> >
> > Nikolaus

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

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


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