Does the order of fields in a multi-field Primary Key matter?

Topic

New Reply

As simplified as I can make, a User goes through a process to select clients they want to include in 1 or more reports. The process writes records into a SelectedClients table, with only 2 fields: UserID and ClientID.

Reports can then be run against this group of clients with this SQL:

SELECT tblClients.* FROM tblClients INNER JOIN tblSelectedClients
ON tblClients.ClientID = tblSelectedClients.ClientID
WHERE tblSelectedClients.UserID = [myUserID]

(Of course, the actual SQL for a specific report will have joins to other tables/queries, but I don’t think that’s an issue here.)

So my question is, should my PrimaryKey for tblSelectedClients be UserID/ClientID, or ClientID/UserID, or maybe it doesn’t even matter?

Reply | Quote

Replies

As long as neither would ever result in a duplicate primary key it really depends on volume in the query but I’d think you want the inner join to be as efficient as possible. So, I’d pick ClientId/UserId.

Joe

--Joe

Reply | Quote

I think you may need two indexes, one with clientID as the key and another with UserID as the key. The first will help with the join, the other will help finding the required user through the WHERE clause. This being the case, from this query alone, I don’t think it’s possible to advise one over the other. If you set the primary key as clientID/userId, you will need to have a second index for UserID, if the other way around, the second index will be needed for ClientID.

Of course, this may also depend on the indexes you have on tblClients. If you already have an index with ClientID as the first field and another with UserID as the first field, you can simply change the WHERE clause to set the condition on tblClients.UserID (if there is such field) and then set the primary key as ClientID / UserID. In this case, there will be no need for the second index.

Reply | Quote