Referencing Primary key twice in one table

Question

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 ?

Christian · Answer

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 Montano · Answer

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.