Primary Key formats


I have some doubts that I would like to clarify, I have had bosses who have never wanted to work with ID Identity created by the same database manager, for the following reason that the correlative is lost. In my opinion the ID is not shown to the client so that I can worry about showing it or it can not be hidden.

There are developers who prefer to create their own ID giving it the size of the field like 0001, 000001, etc

Which of the two ways is good programming practice? Create your own ID type string with the size you want, is the time of the dinosaurs?

asked by Pedro Ávila 03.04.2016 в 04:20

2 answers


From my point of view it is important to use the self-generated IDs for the following reasons:

1. In Favor

  • They take up little space : IDs take up little space (and much less than those that you comment on 001 , 0001 or 00000001 ) since they are auto-incremental in that sense and numerical).

  • They allow to separate the logic : If another type of field is used that is not the ID as Primary Key and a change due to logic is needed, we have a problem. Using the IDs by default allows you to forget about the logic that you have to have and its possible changes.

  • Allow quick use of joins and other queries - If all tables have an ID and you want to join; making an FK that keeps the IDs for all the tables allows you to always schedule in the same way, without having to consult at any time what the field is called or if it can really be done.

  • Indexes : something VERY important. Performing indexes on IDs is easy and fast. Their use is just as easy and they do not depend on other data, so their access is very fast.

2. Against - Not everything is good

  • They do not avoid duplicates or their control : if a table has an ID created from, for example, a DNI / Name + Surname / etc ... you can easily keep a control of duplicates; that is not the case in this case and it has to be done with client / server / SQL-triggers-something logic in some other way. Although it is possible to use unique columns to solve this problem they can affect the performance of tables.

Surely there are other reasons but from my point of view it is important to use them, since the advantages offered by using the self-generated ID for most projects is ideal, the SQl databases are already by default a solution < em> generic itself, for specialized solutions there are NOSQL databases. In very specific cases it does not have to be that way, but the general norm is that.

answered by 14.06.2016 / 17:56

"Losing correlativity" (I suppose it means that "holes" can appear in the sequence) is not a problem. An "artificial" primary key should have no impact on business logic.

Pretending to have a consecutive incremental primary key (without holes) would imply a rather complicated logic to avoid "race conditions" (concurrent sessions that want to add a record simultaneously). It does not make sense - generally speaking.


Create your own ID type string with the size you want, is it the time of the dinosaurs?

Not necessarily, but probably. (I do not really understand what a fixed-width string has to do with keeping correlation) On the other hand, unless you really know what you are doing (who controls the sequence? What happens if two concurrent sessions try to insert a new record?), the generation is prone to errors and maintenance problems.

answered by 14.07.2016 в 16:25