11/30/2023 0 Comments Postgres generate uuid primary keyI 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"). This special column is used to technically identify records and can be used as foreign keys in relations.ĭo not confuse technical (also named "surrogate") keys with business keys. 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. ![]() TL DR: UUID V4 or its COMB variant are great to mitigate various security, stability, and architectural issues, but be aware of various pitfalls when using them. (This article has been also published at DZone) How to Do UUID as Primary Keys the Right Way
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |