CODEWITHSUNDEEP
databaseoracle11g
Md Mosleh Uddin
Md Mosleh Uddin

Reputation: 27

Creating a table where the primary key will be from two different table oracle 11g

I have 3 tables. The schema is given bellow :

Employee (NID, Name, Salary, Designation, Phone, Branch)

Client (NID, Name, Insurance_type, Phone, Address, is_active, Pay_per_month, Start_year, End_year)

Payment (NID, Payable, Receivable)

The payment tables primary key will be NID which will be inherited from Employee and Client table. If we merge both NID columns from the Employee and Client Tables the all NID will be unique. Is there any way to make such table where NID will be inherited from both client and Employee table in oracle 11g?

Upvotes: 0

Views: 19

Answers (1)

TenG
TenG

Reputation: 4004

This doesn't look like a workable design.

The standard way to do this is to have a separate foreign key in the PAYMENT table for each parent, i.e.:

Payment (NID, NID_Client, NID_Emp, Payable, Receivable)

Where NID - unique key for the PAYMENT table

NID_Client is the Client.NID value

NID_Emp is the Employee.NID value

To add the FK constraints:

alter table payment ADD CONSTRAINT FK_Payment_Emp
    FOREIGN KEY (NID_Emp) REFERENCES Employee(NID);

alter table payment ADD CONSTRAINT FK_Payment_Client
    FOREIGN KEY (NID_Client) REFERENCES Client(NID);

Upvotes: 2

Related Questions