Database Administration
sql-server-2008 database-design primary-key identity composite-types
Updated Thu, 22 Sep 2022 04:47:23 GMT

Composite Primary Key efficiency as a Foreign Key

I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the keys in this table as foreign keys.

My question is which approach is more efficient for lookup speeds:

1) Do I create the new table including all 4 columns and reference them all in a foreign key.


2) Do I create a new identity column in the Primary Key table and use this as a foreign key in the new table.

This database is expected to hold a very large amount of data, so I have built it up until now with a view to minimising the amount of data held in each table. With this in mind, option 2 would be the best approach since I will save 2 int columns and a datetime column for every row, but I want to avoid increasing the lookup time if unnecessary.


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.