Should I call my UUID primary key column ID or not?
I'm working on a database design that extensively uses
PRIMARY KEYs. However, this confronts me with a very consequential choice. How do I name these columns? I would call them
uuid, except that,
UUIDbeing an identifier, I then have to quote the field name everywhere:
CREATE TABLE thingie ( "uuid" UUID PRIMARY KEY DEFAULT public.gen_random.uuid(), foo VARCHAR, bar VARCHAR, );
A straight-forward alternative seems to be to instead call these columns
CREATE TABLE thingie ( id UUID PRIMARY KEY DEFAULT public.gen_random.uuid(), foo VARCHAR, bar VARCHAR, );
That way, I don't have the column name, and semantically I can argue that a UUID is indeed a sort of ID; in a Venn diagram, the UUID circle would be placed wholly in the ID circle.
However, I (and I am sure many others with) have grown so accustomed to associating
idwith an auto-incrementing
INTEGERcolumn that I'm afraid to break some sort of unspoken rule by calling these IDs
If you would brighten up my confusion with some solid bike-shedding, I would be much obliged. Indeed my question is: How would you call your
UUID-typed surrogate keys and why?
You shouldn't ascribe meaning to your surrogate keys beyond the fact that they are a unique identifier for each row, as such it doesn't matter what format it takes from the point of view of your schema design. All you should care about at that point is that it is a value that identifies something, so I stick with calling them
In fact what you have with a UUID, unless you have had a brain-fart and stored them as strings¹, is effectively a large integer. You can't do arithmetic on it³ but the engine doesn't see it as any different to a bigger
BIGINTbeing used as a key.
Another general thing is that I try to avoid any name (for a column, table, function, procedure, ...) that could be a keyword so need escaping. This is a point against calling them
uuidif the main point for calling them
idis not enough. Of course this can't be perfect, I would avoid
uuidas I know it is a type name in Postgres but devs who have only ever use SQL Server up to this point might not know that as it isn't a reserved word (or even a type name) there, and I might well use words that are not portable elsewhere similarly.
If you effectively store multiple surrogate keys due to integrating with another systems that use something else, your internal identifier, is
id. The others are real data with meaning so far as your system is concerned, not actually surrogate keys, and should be named in a way that describes their content or use. Real world examples include ISBN, StaffReferenceNumber/SRN, IRN, PPN, …, but you might also have something less general like SalesForceId or JoesPartStoreId. Even if you don't define a surrogate key in these circumstances⁴, keep the meaningful names instead of just
idso it is obvious that your rules probably don't control their generation and use.
 or id or Id, keeping consistent with the casing rules you follow elsewhere just in case your stuff ends up being interpreted in a case sensitive way later.
 I still maintain a legacy system that made this mistake two decades ago, a little before my time. It all works but of course there are storage size and performance “considerations”, and interesting bugs² lead to invalid UUID values such as empty-string turning up.
 I'm also counting past bad colleagues, including that young David Spillett who messed up a few such times many years ago, as bugs in the business!
 Well, you could if you tried hard enough, but not with any built-in functions.
 I would always have a surrogate key in these cases anyway. This means only your bugs can cause issues like duplicate keys or the need for expensive primary key value changes that affect many foreign keys, instead of being beholden to dealing with other systems' bugs too.