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.

Related Solutions

Sql-server – SQL Server 2005, Foreign key check against part of a table

No this isn't possible.

I'd probably create a separate table categoriesWithArticles that holds the relevant categoryids (instead of having the flag) and have the FK reference that table instead.

Another more convoluted way to enforce it and keep the column would be

CREATE TABLE categories
(
categoryId int primary key,
hasarticles bit not null,
unique(categoryId, hasarticles)
)

CREATE TABLE articles
(
categoryId int,
hasarticles as cast (1 as bit) persisted,
FOREIGN KEY (categoryId, hasarticles) REFERENCES categories(categoryId, hasarticles)
)

But this requires the creation of a technically redundant unique constraint as well as the additional persisted column in the articles table.

Sql-server – Composite Primary Key efficiency as a Foreign Key

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.

As you point out, you'll have a much simpler FK relationship.
A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column.
If business rules ever change, you won't have to reorder the table.

The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK. If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.

Best Answer

Related Solutions

Sql-server – SQL Server 2005, Foreign key check against part of a table

Sql-server – Composite Primary Key efficiency as a Foreign Key

Related Question