In case of sequence reset and ID reuse, this may conduct the wrong sorts. Likewise, developers may rely on PK values to sort items chronologically instead of using a dedicated date column.This is not an issue by itself as unicity is preserved but can induce subtle bugs if developers rely on PK to count the number of items instead of using a proper COUNT query. It is hence possible to get "holes" (absent values) in PK sequences. In rollback cases, the value is still incremented. In some SGBDR like PostgreSQL, a nextval operation is not truly transactional.Why You Should Avoid Auto-Incremented Integers Risks to Introduce Bugs Pretty easy to read and verbalize when the number of digit remains reasonable (but, as stated before, technical PK should not be used by humans anyway - use a function key instead). Comes with very little possibility of performance issues due to bad design. Why You Should Use Auto-Incremented Integers Each argument is provided along with an importance weight (from 1=minor to 5=major). Though this may be considered an obvious and no-brainer choice, it may be a bad idea in the long run. When working on existing projects, I often observe that PK is designed as auto-incremented integers. Using a text-based random UUID V4 (universally unique identifier), also referred to as GUID (globally unique identifier) by Microsoft.MySQL provides the AUTO_INCREMENT attribute. Most RDBMS like PostgreSQL or Oracle provides the SEQUENCE object allowing to auto-increment a value while respecting the ACID principles. Auto-incremented integers (starting at 1 or any larger value: 1, 100, 154555).The two formats matching these rules are AFAIK: The real world is just too complex to make any assumption about unicity. I once worked for a French governmental agency and observed both issues in different projects: 1) Legacy code relied on the first digit of the NIR (social identity number) to get the people type thus ignoring possible type reassignments (though the current type was available as a dedicated column) 2) We recently discovered that this unique ID was not so unique (for example, an ID shared temporarily by several members of an immigrant family or collisions following cities merging). For example, if you use e-mail as PK, you implicitly forbid modification of it in future releases: never say "never." Another problem with natural keys is the difficulty of ensuring unicity due to functional issues even when everything has been done to avoid them. Even if this is largely discussed, I would warn against using natural keys as PK altogether. If the ID format changes or is buggy (because of bad timezones handling for instance), some subtle issues may arise. Some developers may ignore that a date_creation column exists and will only rely on the PK's first four digits. Imagine an ID starting with the current year. Developers should not parse IDs for the wrong reasons. Reusing a deleted row PK is technically possible in relational databases but is a very bad idea because it contributes to generating confusion (for example, an older log file can reference an ID reused in the meantime by a new entity, thus conducting to false deductions). This is enforced by the UNIQUE constraint automatically added by RDBMS on every PK. They should only be processed and readable by machines, not humans. In this article, we will focus only on technical PKs. The most important tables (so-called entities in domain-driven design) may contain an alternate human-readable ID column (like the customer ID " G2F6D"). NOTE: Do not confuse technical (also named "surrogate") keys with function keys. This special column is used to technically identify records and can be used as foreign keys in relations. Even if the primary key can be composite (built of several columns), it is a widespread good practice to dedicate a special column (often named id or id_) to this end. Why Do We Need Technical IDs in the First Place?Īny properly designed relational database table owns a Primary Key (PK) allowing you to uniquely and stably identify each record.
0 Comments
Leave a Reply. |