Re: Is Using Multiple Composite Primary key a Good Design Practice?

[Rick James]

I checked a system --
572 PRIMARY KEYs,
23% of them were composite
Longest had 6 columns

Performance and "best practice" dictate that the "optimal" PK be provided for the table. OK, that's vague. Let's discuss some potential issues:

Note 1:

PRIMARY KEY(a, b, c)
INDEX(d)

In InnoDB, the PK is implicitly included in each secondary index. So that is really like
INDEX(d, a, b, c)
making the index bulky. If (a,b,c) are TINYINTs, no big deal, If they are VARCHAR(255) utf8, then that is quite bulky.

If there are no secondary keys, this Note is irrelevant.

Note 2:

If one component of the PK (or other UNIQUE key) is a DATETIME or TIMESTAMP, then I question whether it is really unique -- can't you possibly get two items with the same second?

Note 3:

PRIMARY KEY(country, state, city)

Perhaps those should be normalized into another table, and you have only
PRIMARY KEY(location_id) ? Variants on this may be the 'right' way to deal with your 7-8 column PKs.

Note 4:

A FOREIGN KEY creates an INDEX to support the 'constraint'. Many operations require a lookup to validate the FK link; this is a performance hit. Purists like FKs; I prefer to validate my code so I don't need FKs. (OK, this has nothing to do with the number of columns in the FK.)

---
If you would care to show a few of the SHOW CREATE TABLE, I might come up with more comments on whether long PKs are OK or not OK.