Foreign key refering to primary keys across multiple tables?

Question

hi , i have three tables say city,state and road

1) city -> city_id(PK),name

2) state-> Stt_id(PK),name

3) Road-> Edge_id(PK), Admin_id(FK)

where Admin_id refers to city_id and Stt_id both.

This is done because the tables are too huge.

say city_id contains 1,2,3 and Stt_id contains 4,5,6

now if i am inserting 1,2,3,4,5,6 in admin_id it is throuing an error .. what is the solution of my problem,

regards

sanjay

Answer 1

Create an admin table that holds an entry for every city and state, using its admin_ids as city_id and stt_id in their respective tables. Then declare foreign keys on city_id, stt_id and road.admin_id, referencing admin.admin_id (retaining all the existing PKs, of course).