Foreign key in oracle

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)   
);
开发者_Python百科

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.

From a data modeling standpoint, you have a few options

• You 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.
• You 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.
• You 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.

Generally, 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.

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.

Create a single parent table (for the sake of example I'll call it Party). Reference the Party table from all three tables A,B,C. As a result, the multiple foreign keys would be replaced by one foreign key referencing the Party table. This is an example of a generalisation / specialisation, "subtype" pattern which you will find in many data modelling books.