MySQL: Why it is bad behaviour to have biz fields as PK?

Normally, biz fields are not auto-increment and strict increase.

So using biz fields as PK will have side effects. PK is used as file organization field. So inserting a PK which is between 2 fields will result in db re-organizing its structure.

And some cases, adding more fields as PK have only way of deleting current PK and recreate a PK. Resulting a copy of table process, a write lock of whole table, which is very slow and not tolerating.

So in most cases in MySQL, create a ID key as its PK, auto-increment it. And if have sort requirement, create sort index. If have unique requirement, create a unique requirement.

 

---

More to say, PK suggests to use number/int. Not recommend strings. If string as PK is too long, only prefix of it will be used as pk.