You want to know if it’s better to use one natural key or substitute (surrogate).
Only leave the registration field as the primary key.
That’s a natural key. Do you control that number? Will it ever be changed? Is there not the slightest chance that the same object in the database one day needs to have another unique identification? Is it short enough? Doesn’t it give room for error? Will it always be unique? It really identifies what the object is?
Most existing data in the world do not meet all these criteria, so a replacement key is chosen. If you take all this, you can use it without problems. But be careful, many people think you answer and one day find that you do not answer, it was something circumstantial.
Leave the registration field with an index unique
and create an id field with auto increment to be the primary key.
This is a substitute key. It is interesting in many cases to maintain an internal control in the system regardless of the number the user reads. I can’t say I’m the best in your case. If you can do the previous item well then you don’t have to do it, you’re just wasting space and performance.
Leave the registration field as the primary key and create a field id
indexed auto increment
, to assist.
Doesn’t seem like much of an advantage to me.
Contrary to what many people think there is always a primary key in every table. Even when the database does not require you to create one (it creates without you knowing). Also contrary to popular belief, the primary key is always an index. Data is always sorted in the database by the primary key. Secondary indices usually refer to the primary index.
It is recommended to use natural primary key? and It is good practice to use composite keys as the primary key?
– rray