I have a table Room
with primkey on column room_id
and another table Items
with primkey item_id
. Each Item has a foreign integer key to room_id. When my program runs, it only looks at items in a single room so it would help if my primary key was on (room_id, item_id) in this order so that data is sorted on disk in this order.
Is this possible? Or do I need to duplicate room_id
in Item
and not have it as foreign key?