CODEWITHSUNDEEP
sqldatabaseoracle-databaseforeign-keysforeign-key-relationship
Figen Güngör
Figen Güngör

Reputation: 12579

Two primary key reference to a foreign key

In our database system, we have student and personnel fields. They have PersonnelCardId and StudentCardId and we made them primary key. Then we have a payment device which holds cardID and we declared that it is a foreign key. And we couldn't insert a row in that case and after a research we learned that we can't reference two primary key to a foreign key. So how can we fix this situation? Thanks.

Upvotes: 1

Views: 1160

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

As you've stated, a foreign key can't reference two different tables at the same time. With your current schema, the cardID foreign key can only reference either PersonnelCardId or StudentCardId...

If I understand correctly, your schema looks something like:

PersonnelCard
----------------
PersonnelCardID - PK
PersonnelCardFields


StudentCard
----------------
StudentCardID - PK
StudentCardFields


PaymentDevice
----------------
PaymentDeviceID - PK
CardID - FK

To resolve this, you'll have to find a way to combine the PersonnelCard and StudentCard tables...

Card
----------------
CardID - PK
CardType - (Personnel or Student)
CardFields

The FK on the PaymentDevice table can now reference the PK in the new Card table.

Upvotes: 5

Related Questions