Foreign key part of a composite primary key 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?
Your clustered index almost certainly should include RoomId for the reasons you correctly identify: it provides an efficient access path to all records for a given Room.
But there are other motivations are well.  If Item is a child of Room, then you probably have an identifying relationship like Room contains zero to many items.
This would naturally lead your primary key to be something like (RoomId,&lt;columns that determine a unique item&gt;).  So that could be (RoomId,ItemId)assuming that ItemId has some value beyond being a row pointer.
Otherwise it could be (RoomId,SerialNumber) or (RoomId,ItemType) - the exact answer depends on the data you're trying to store.