There are two basic techniques: Generating keys with a sequenceĪ sequence is a database object whose sole purpose in life is to generate unique numbers. Techniques for auto-generated primary keys in PostgreSQL Some people even argue that you should use an artificial primary key even if there is a natural one, but I won’t go into that “holy war”. But typically, there is no such attribute, and you have to generate an artificial primary key. Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. This is because foreign key constraints typically reference primary keys, and changing a primary key that is referenced elsewhere causes trouble or unnecessary work. You are well advised to choose a primary key that is not only unique, but also never changes during the lifetime of a table row. If you wonder why, search the internet for the thousands of questions asking for help with removing duplicate entries from a table. In a relational database, it is important to be able to identify an individual table row. Why auto-generated primary keys?Įvery table needs a primary key. In this article, I’ll explore the options and give recommendations. UPDATED : Sometimes customers ask me about the best choice for auto-generated primary keys. If you're still on the fence, what should I choose for my primary key? has excellent advice and a solid analysis of the tradeoffs.Auto-generated auto-increment autoincrement identity columns postgresql primary key sequence uuid I'm not proposing that every database switch to GUID primary keys, but I do think it's important to know the option is out there. The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes.It is a whopping 4 times larger than the traditional 4-byte index value this can have serious performance and storage implications if you're not careful.Most replication scenarios require GUID columns anyway.You can generate IDs anywhere, instead of having to roundtrip to the database.Allows easy distribution of databases across multiple servers.Allows easy merging of records from different databases.Unique across every table, every database, every server.But it's still a question of balancing the tradeoffs between traditional 4-byte integer IDs and 16-byte GUIDs: GUID Pros
GUID primary keys are a natural fit for many development scenarios, such as replication, or when you need to generate primary keys outside the database. Using a GUID as a row identity value feels more natural- and certainly more truly unique- than a 32-bit integer. The Cost of GUIDs as Primary Keys (SQL Server 2000).But that cost may not be as great as you think: Using sixteen bytes instead of four bytes for a primary key? Have you lost your mind? Those additional 12 bytes do come at a cost. Perhaps that's why I read with great interest recent accounts of people switching their database tables from traditional integer primary keys. Each globally unique ID is like a beautiful snowflake: every one a unique item waiting to be born. Long-time readers of this blog know that I have an inordinate fondness for GUIDs.
0 kommentar(er)
