Multi column primary keys?

Question

For example I have 2 tables, Users and UserRelations, and it is a one to many relationship.

For the UserRelations table, I can have an identity column and make it the primary key:

[RelationID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[TargetID] [int] NOT NULL,

Or I can design the table like:

[UserID] [int] NOT NULL,
[TargetID] [int] NOT NULL,

and make UserID + TargetID the primary key.

My question is what are the implications of going with each design, which is better for performance?

Answer 1

If you use the former design, with the superfluous identity column, there's no constraint against inserting two rows with identical UserID and TargetID. You'd have to create a UNIQUE constraint over the other two columns, which creates a compound index anyway.

On the other hand, some frameworks (e.g. Rails) insist that every table has a surrogate key named id so the "correct" design may not work. It depends on what code you're writing to use this table design.

Answer 2

This is almost a religious issue. For each person who says use a non-intelligent surrogate key, somebody else points outs that surrogate keys can be considered superfluous, etc, etc. So do whatever feels most comfortable for you and your team.

If you do decide to use a surrogate key, you should also put a unique constraint on the natural (in this case multi-column) key, to keep the integrity of your data.

I usually go for an additional surrogate key, as there are a number of desirable (not necessarily required) primary key characteristics that natural keys sometimes lack:

• Unique values: The primary key must uniquely identify each row in a table.
• Non-intelligent: The primary key should preferably have no embedded semantic meaning. In other words, it should not describe characteristics of the entity. A customer ID of 12345 is typically preferred over RoadWarrior.
• No change over time: The value of a primary key should not normally change. Changing a primary key value means you’re changing the identity of an entity, which doesn’t normally make sense. I prefer non-intelligent keys because they are less likely to change.
• Single-attribute: A primary key should have the minimum number of attributes possible. Single-attribute primary keys are desirable, because they’re easier for applications to work with, and they simplify the implementation of foreign keys.
• Numeric: It’s often easier to manage unique values when they are numeric. Most database systems have internal routines that enable auto-incrementing primary key attributes.

From a performance point of view, I suspect there is little difference in most cases. But as with any performance issue, you should measure where you have concerns.