CODEWITHSUNDEEP
sqlforeign-keys
Prateek
Prateek

Reputation: 161

referencing multiple tables through a single column in sql

In SQL can a single column in a table reference multiple tables?

E.g. if we have tables employee (PK emp_id, name) and customer (PK cust_id, name)

Can we have a table contact (id references [employee, customer], number);

Or do we necessarily need to make 2 tables:

contact_cust (cust_id references customer, number) and contact_emp (emp_id references employee, number)

I know that the second choice would be better even if the first one were possible. I just want to know is the first way possible?

Upvotes: 3

Views: 16426

Answers (2)

Disillusioned
Disillusioned

Reputation: 14832

No you can't. One option is to first generalise Employee/Customer as "Party" or "Stakeholder".
I.e.

TABLE: Party(PK Party_Id,
             Name)  
TABLE: Employeee(PK Emp_Id REFERENCES Party.Party_Id, 
                 Salary)  
TABLE: Customer(PK Cust_Id REFERENCES Party.Party_Id,
                CreditRating)

Then Contact would reference Party.

Upvotes: 7

paul
paul

Reputation: 22001

No, you cannot have a single column with a foreign key that references two different tables.

Upvotes: 2

Related Questions