Sql-server – Foreign key part of a composite primary key

database-designperformancesql server

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?

Best Answer

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,<columns that determine a unique item>). 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.