I have created a table in oracle which has one FK that refers to 3 primary keys in 3 different table.But when I want to insert into it I see an error says parent key not found!what should I do?
CREATE TABLE A
( X char(11) not null,
id char(11) not null,
PRIMARY KEY(X,id),
FOREIGN KEY(id) REFERENCES B(employee_id),
FOREIGN KEY(id) REFERENCES C(customer_id)
);
Justin Cave :
If your intention is that the ID column in A is either a foreign key to the EMPLOYEE_ID column in B or a foreign key to the CUSTOMER_ID column in C, you've got a problem-- you can't declare a foreign key for this either/or type relationship.\n\nFrom a data modeling standpoint, you have a few options\n\n\nYou can create two columns in A, an EMPLOYEE_ID column that is a nullable foreign key to the EMPLOYEE_ID column in B and a CUSTOMER_ID column that is a nullable foreign key to the CUSTOMER_ID column in C. You can then create a check constraint on A that exactly one of these two columns is NULL.\nYou can create a new ENTITY table that has all EMPLOYEE_ID and CUSTOMER_ID values. EMPLOYEE_ID in B would be a foreign key to the ENTITY_ID column in ENTITY as would the CUSTOMER_ID column in C and the ENTITY_ID column in A.\nYou can leave the columns in A alone and eliminate the foreign key. Then you would be responsible for verifying referential integrity in your code. This last option is generally not a good idea.\n\n\nGenerally, I'd also be really suspicious of columns declared as CHAR(11). There is virtually no case in Oracle where it really makes sense to use CHAR rather than VARCHAR2. At best, it's a wash.",
2011-04-15T03:00:45
Yanick Rochon :
Your column id in table A refers to a column in table B and also a column in table C. If you need to insert data in table A, the value in the id field must match a value in table B and C, otherwise you are breaking your FK constraint; it is impossible to insert a row in table A that contains a column refering to an unexisting row in table B or C.",
2011-04-15T02:55:10