Can primary index be a CHAR in MySQL?

Question

My primary indexes are unique reference numbers like 002345 and 000023.

If I format them as integers I loose my zero's. They need to be 6 digits.

Can I use CHAR? I don't need any auto increments.

Answer 1

Yes, you can use a CHAR column as a primary key in MySQL.

However you should consider keeping your reference numbers as integers and applying the leading 0s at the application level.

You may also want to consider using a surrogate key that is not derived from the reference number or any other application data. The main advantage would be that your records won't lose their "handle" if the order reference number ever changes. You will also get the additional performance of an integer index as a positive side-effect.

Answer 2

I've just tested on local MySQL - it works perfectly. But for some performance boost I would go for integer primary index.

Answer 3

You can use order numbers as a primary key. But one day your boss will phone you and tell you the order number sent out for your most important customer was printed as 002346 not 002345 and that they can't receive payments until this error is fixed in your system...

You should also consider an integer primary key as it will save space and joins will be faster.