Why single primary key is better than composite keys?

Question

Why is the rejection of composite keys in favor of all tables using a single primary key named id? Cause generally all ORM follow this.

EDIT

I just started learning ruby on rails and in the book of agile development by pragmatic there is a line:--- Rails really don't work too well unless each table has a numeric primary key. It is less fussy about the name of the column. Same kind of line I read when I was learning Doctrine.

EDIT2 Please check this link too. I am getting more and more confused about this thing:--- Composite primary keys versus unique object ID field

From the above link:--

*the primary key should be constant and meaningless; non-surrogate keys usually fail one or both requirements, eventually

If the key is not constant, you have a future update issue that can get quite complicated if the key is not meaningless, then it is more likely to change, i.e. not be constant; see above

Take a simple, common example: a table of Inventory items. It may be tempting to make the item number (SKU number, barcode, part code, or whatever) the primary key, but then a year later all the item numbers change and you're left with a very messy update-the-whole-database problem...

EDIT: there's an additional issue that is more practical than philosophical. In many cases you're going to find a particular row somehow, then later update it or find it again (or both). With composite keys, there is more data to keep track of and more constraints in the WHERE clause for the re-find or update (or delete). It is also possible that one of the key segments may have changed in the meantime!. With a surrogate key, there is always only one value to retain (the surrogate ID) and by definition, it cannot change, which simplifies the situation significantly.*

Answer 1

I don't think there is a blanket statement that you should only ever use a single primary key named id.

Most people use a surrogate primary key as an auto generate int, because it isolates the primary key from ever needing to be changed, like if you make the PK the user name and they later changed their legal name. You would have to update the PK and all FK columns to reflect the new name. if you had used a surrogate primary key, you just update the user's name in one spot (because the tables join on the int not the name).

The size of a primary key is important because the PK is duplicated into every index you build on the table. If the PK is large (like a string) you have fewer keys per page in the index and the index will take more cache memory to store it. Ints are small.

Having a auto increment int PK lends itself to being a clustered index well, as rows are stored in this order and there is no need to go back and bump rows out of the way to insert a new row, you always add to the table's end.

Answer 2

The only real limitation that I have run into using composite keys regards using an IN expression with a subquery. This is a problem, because a subquery in an IN expression must return a single column (at least in T-SQL).

SELECT     emp.Name,     emp.UserDomain,     emp.UserID FROM     employee emp WHERE     ???? IN (SELECT e.UserDomain, e.UserID FROM ... /* some complex                                                         non-correlated subquery                                                         or CTE */             ) 

There are always work-arounds, of course, but sometimes it could be an annoyance.

This is hardly a reason to avoid a composite key in places where it makes sense to use one.