--- 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).

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.

alter table DOCTORINSURANCE
add constraint FK_DOCTORINSURANCE_DOCTOR
foreign key (DOCTORID)
references DOCTOR (DOCTORID)
on delete CASCADE
on update CASCADE;

If you need to enforce the existence of at least one DOCTORINSURANCE
record, then you could use a stored procedure to add both in one
operation.

Adam