6
I have a company code of the product that is formed by two letters and 4 numbers (example: XX0000), it is advisable to use this code as primary key or create another column with code field and put unique for that code?
2 answers
9
Is this code unique and will never be changed for each line (entity)? If so, it is a good candidate for primary key yes. Otherwise I would suggest using another, some artificial (surrogate), one id internal database control probably self-improved in some way.
Every primary key must be unique (and not accept null, because it could only accept one to be unique) and ideally never be exchanged (why CPF is a bad primary key, nothing guarantees that the person will have the same CPF forever, imagine email, phone and things like).
Be careful because a lot of people think that a code will never be changed, but that’s not always true, I’ve seen many situations where codes that would never change and one day change. Companies restructure how to manipulate their products for various reasons. This XX It’s a concern, because it looks like it’s a classifier, and ratings change, it’s rare, but it changes. The problem is not having letters, the problem is the stability of the data.
It can even do different, but ideally primary keys should always be increasing. It has database that requires it to be, not the case of Mysql.
I give a answer that talks about myths about data.
3
To primary key should be a field, or set of them, that identify the record.
If the field cited by you fits this situation, it may be a primary key.
Just one caveat: this field in XX0000 format will be represented by a varchar in your bank. Perhaps the use of a numerical field to represent the primary key (an auto increment column, for example) bring better performance but would have to be evaluated. If so choose, could make your field a unique key to avoid duplication of values.
Browser other questions tagged mysql database modeling
You are not signed in. Login or sign up in order to post.