Menu
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?
212
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.
Each database table needs a primary key because it ensures row-level accessibility. If you choose an appropriate primary key, you can specify a primary key value, which lets you query each table row individually and modify each row without altering other rows in the same table.
A set has no inherent order. This said, the key (a,b) is the same as the key (b,a) , because they contain the same elements. As to superkeys: Yes, that's just combinatorics.
In SQL Server: no, by it's clustering key - which default to the primary key, but doesn't have to be the same. The primary key's main function is to uniquely identify each row in the table - but it doesn't imply any (physical) sorting per se.
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.
51
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
