That is easy, you are in the wrong direction.
In your case DOCTORINSURANCE shall point out a doctor and an insurance,
not a doctor is pointing out a DOCTORINSURANCE.
DOCTERID is not the PK on DOCTORINSURANCE

write

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

Anthony Tanas 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.
>
> I am trying to set up my FK relationship between DOCTOR and
> DOCTORINSURANCE:
>
> ---------- STATEMENT ----------
>
> alter table DOCTOR
> add constraint FK_DOCTOR_DOCTORINSURANCE
> foreign key (DOCTORID)
> references DOCTORINSURANCE(DOCTORID)
> on delete CASCADE
> on update CASCADE
>
> But am getting:
>
> ---------- ERROR MESSAGE ----------
>
> This operation is not defined for system tables.
> unsuccessful metadata update.
> could not find UNIQUE INDEX with specified columns.
>
> DOCTORID is the PK for DOCTOR
> DOCTORID and INSURANCEID are the PK for DOCTORINSURANCE
>
> So I have two questions:
>
> Am I trying to set up this constraint from the right "direction"
> (constraint
> on DOCTOR table) or should the constraint be on the DOCTORINSURANCE table?
> And secondly if this is correct, why am I getting this error?
>
> Thanks for any help. :)
>
>