Menu
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.
723
PRIMARY KEY indexesPRIMARY KEYs are usually defined on the smallest set of columns that can uniquely identify a row within your table, such as an id column. For this reason, mySQL optimizes data storage with PRIMARY KEY .
You should mention that a primary key is always indexed, meaning that a primary key is also always an index key.
Because MySQL works faster with integers, the data type of the primary key column should be the integer e.g., INT, BIGINT . And you should ensure sure that value ranges of the integer type for the primary key are sufficient for storing all possible rows that the table may have.
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.
174
I've just tested on local MySQL - it works perfectly. But for some performance boost I would go for integer primary index.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
