> --- In firebird-support@yahoogroups.com, "Anthony Tanas" <anthony@...>
> wrote:
> >
> > I need to set up Foreign Key constraints in my database.
> >
> > I'm not sure which table I should add the constraint to. Here is an
> > example:
> >
> > I have a DOCTOR table.
> > I have an INSURANCE table.
> >
> > They are joined via a DOCTORINSURANCE table that contains the PK for
> each
> > table and then adds certain credential information for a doctor for
> > that insurance company.
>
> So you are saying that for a DOCTORINSURANCE record to be
> 'valid', there must exist a record in DOCTOR (matched between
> DOCTORINSURANCE.DOCTORID and DOCTOR.DOCTORID).

Yes, and also a corresponding entry in INSURANCE.

>
> If a doctor can be a member of multiple doctorinsurance
> relationships, then the foreign key must go the other way.
> Your foreign key should then rather constrain the
> DOCTORINSURANCE table.

Gotcha, that explains my problem. I'll need two FK constraints on
DOCTORINSURANCE, one for DOCTOR and one for INSURANCE.

Now for ON DELETE CASCADE and ON UPDATE CASCADE...does that mean if that if
the ID changed in DOCTOR it would cascade to DOCTORINSURANCE, or if it is
changed in DOCTORINSURANCE it would cascade the other way?

The ID would never change in this case, but I do have an example in another
case...but we'll use this example since it is similar:

So if the DOCTORID changed in the DOCTOR table, with an FK constraint on
DOCTORINSURANCE with ON UPDATE CASCASE...would the field in DOCTORINSURANCE
change?

Thanks Adam. :)

Additional Questions:

1) What happens if I put an FK constraint in place and some data in the
database allready violates it?

2) How does FK constraints affect performance?

3) Currently my application makes sure to delete related records. If I have
an FK constraint set up with ON DELETE CASCADE, should I just rely on the
database to take care of this sort of thing?