Home / Questions / 6864
Accepted
Pedro Ávila
Asked: 2020-04-03 18:20:17 +0800 CST 2020-04-03 18:20:17 +0800 CST

Primary Key Formats

  • 772

I have some doubts that I would like to clarify, I have had bosses who have never wanted to work with ID Identity that is 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 I should worry about showing it or it cannot be hidden.

There are developers who prefer to create their own ID giving the field size as 0001, 000001, etc.

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

sql

2 Answers

  1. Best Answer

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

    1. In Favor

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

    • They allow logic to be separated : If a field type other than ID is used as the Primary Key and a change needs to be made due to logic, we have a problem. Using the default IDs allows you to not worry about the logic it may have and its possible changes.

    • They allow the quick use of joins and other queries - If all the tables have an ID and you want to do a join; making a FK that is kept with the IDs for all the tables allows you to always program in the same way, without having to check each time what the field is called or if it can really be done.

    • Indices : something VERY important. Indexing on IDs is easy and fast. Its 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 track of duplicates; that is not so 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 columns uniqueto solve this problem, they can affect the performance of the 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 auto-generated ID for most projects is ideal, SQL databases are already a generic solution by default , for specialized solutions there are NOSQL databases . In very specific cases it does not have to be this way, but the general rule is that.

    • 14

  2. "Losing correlativity" (I assume you mean "holes" can appear in the sequence) is no problem. An "artificial" primary key should have no impact on business logic.

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

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

    Not necessarily, but probably yes. (I also don't really understand what using a fixed-width string has to do with maintaining correlativity.) On the other hand, unless they really know what they're doing (who controls the sequence? what happens if two concurrent sessions try to insert a new record?), the build is prone to errors and maintenance issues.

    • -1