CODEWITHSUNDEEP
mysql
merthmagic
merthmagic

Reputation: 175

Should we store an auto-increment ID when we have a unique indexed column

I am designing a MySQL data table to store e-business orders,each order has an unique order number looks like '20160223xxxxxx',it is generated by a specified algorithm to ensure it is unique in the table. I am wondering if we still need an auto-increment field as primary key in the table since I think the order number seems also can be the primary key.

Upvotes: 0

Views: 371

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You do not need an auto-incremented id, because you have one. However, I am a big fan of synthetic primary keys:

  • They define a sequence for the inserts, so you know what rows were most efficient.
  • 4-byte integers (if your table is not really big) is more efficient for foreign key indexes than longer character strings.
  • If one of the unique order ids changes, a synthetic primary key makes it easy to change the value.
  • You are isolated from future changes in the order-key-generating system.
  • If you mysql the primary key is generally the clustered index. Hence, inserts with a synthetic key go to the end of the table -- which is efficient. Inserts with an outside key might be out-of-order -- which is inefficient.

Upvotes: 1

mkasberg
mkasberg

Reputation: 17272

You don't need to use an auto-inc field. Use the unique identifier as the PK of the table. It will save space.

Upvotes: 0

Related Questions