Subject | Which way to set up Foreign Key? |
---|---|
Author | Anthony Tanas |
Post date | 2006-09-13T02:07:55Z |
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. :)
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. :)