CODEWITHSUNDEEP
sql-serverentity-frameworkcomposite-primary-key
Stephen Holt
Stephen Holt

Reputation: 2368

Why does primary key order matter?

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?

Upvotes: 8

Views: 18218

Answers (1)

Bill Gregg
Bill Gregg

Reputation: 7147

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.

Upvotes: 13

Related Questions