Referencing Primary key twice in one table

try abc

Referencing Primary key twice in one table
Is it possible to create 2 foreign keys from the same primary key, both of them in the same table?

This is a simplified version of the tables I want to create:

Supplier Table
-supplier_id(primary key)
-supplier_name

Asset table
-Asset_id
-Supplier_id(foreign key 1)
-ServicedBy_Supplier_id(foreign key 2)

If this is possible how can I make them refer back to the same primary key?
It does not support Cascade delete and update in both the foreign keys ?
What is the solution for this ?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Christian18y ago
If I understand you problem correctly, you have an asset that is purchased from one company and may be serviced or maintained by the same company or possibly a completely different company.

I agree with the above poster in that this is a normalization problem. Even if the list of companies that sell your asset are EXACTLY the same as companies that provide maintenance, your foreign key will cause problems by violating the one column to one column relationship.

One solution is to split the seller/maintenance table in to two different tables. With proper indexing, even a large table will be give results quickly.
Jan Montano18y ago
I don't know if there's a way to have 2 foreign keys referencing 1 primary key. Probably not because sql doesn't allow it.

With proper normalization of your database, you'll be able to eliminate this requirement.