8

I recently set up a class in an EntityFramework project which designated a couple of its members as a composite key.

However, when it came to time to create the database from this it gave the error

Unable to determine composite primary key ordering for type 'NNNNN'. Use the ColumnAttribute or the HasKey method to specify an order for composite primary keys.

I know how to solve the problem, but I was just wondering why it cares about the order. Isn't a composite primary key just a set of columns on which to key the records, without any particular ordering necessary?

Stephen Holt
  • 2,360
  • 4
  • 26
  • 34
  • 2
    Not an EF specific comment but generally speaking it matters in that the index that supports `(a,b)` will efficiently support different queries than that supporting `(b,a)` (if you do a lot of searches `WHERE a = foo` the first one will be preferable. Also if one of the columns is ascending such as a datetime value placing this first may cause less fragmentation. – Martin Smith May 23 '13 at 12:00

1 Answers1

13

It matters because the order of primary keys matter in the database. Since primary keys are (usually) clustered, and always indices, whether the key is ordered as First Name, Last Name, SSN or SSN, Last Name, First Name, makes a huge difference. If I only have the SSN when I'm querying the table, and the PK is the only index, I'll see GREAT performance with the latter, and a full table scan with the former.

Bill Gregg
  • 7,067
  • 2
  • 22
  • 39
  • So when determining this ordering, the general rule is that I should pick whichever of the keys is mostly likely to be searched for and place that first in the list? – Stephen Holt May 23 '13 at 13:16
  • 5
    This is more art than science. Yes, picking the value that will most likely be had when querying is a good approach. But also consider how often the table is being inserted, updated, deleted. Since the PK is (probably) clustered, you don't want to be physically moving those rows around very often, and you don't want to have to split pages just to insert new rows. So often people will use a hidden identity (either an int or a guid) to be the only PK, making sure that new values are inserted at the end of the table. Then you can index on those other columns for fast selects. – Bill Gregg May 23 '13 at 13:40