Unanswered: Enterprise Manager and Foreign Key Constraints
OK, I'm getting an error in Enterprise Manager that I don't understand. I have three tables. One is a list of hospitals - id, address details etc. Another is a list of stock - id, description etc. The third (called "hosp_stock") is supposed to be the one that links them. It has its own autonumber primary key, hospital id, stock id and a stock amount field.
Hopefully, you're now thinking "Ah, so he just needs to create a one-to-many relationship between stock/id and hosp_stock/stock_id, and another one between hospitals/id and hosp_stock/hospital_id.
That's what I thought, but when I go to save this relationship, I get the following error:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_hosp_stock_int_loc'. The conflict occurred in database 'patientline', table 'int_loc', column 'in_id'.
(The "int_loc" table is the hospitals list, the "in_id" column is its primary key that I linked the hosp_stock/hospital_id column to.)
I'm not sure that I've made this very clear, all I know is its a common way of creating such relationships. If you're familiar with Enterprise Manager, does any of it sound wrong? Why will it happily create a link between the stock table and the intermediate hosp_stock table, but not between hosp_stock and the hospitals table?
NB - if it's any help, I managed to get it to accept the relationship after deleting all the data existing in the hosp_stock table, but now I can't put any data back in.
It's ok chaps, I figured it out For the future reference of any interested parties: make sure that when you're entering information in a foreign key table, make sure that value exists in the correspodning column of the primary key table. For example, don't assume your hospital ID numbers begin at "1" and merrily sit putting data in for this non-existent hospital, otherwise you just get a lot of beeps and a sore head.