• Hanzito (unregistered)

    So this is a design for a table with different types of records? Not surprised the dev then concluded the primary key constraint was the problem. After all, it's the frist thing that comes to mind.

  • (nodebb)

    It kept happening to them, for some reason

    My response to hearing that would be "well, maybe you should find out what that reason is."

    until eventually it stopped happening, also for some reason.

    And that, also.

  • (nodebb)

    part_uuid VARCHAR(40) NOT NULL,

    Q: a Universally Unique Identifier is a 128-bit number, which is 16 bytes binary. Typical text format is "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", 32 hexdigits plus 4 dashes, total of 36 characters. So why VARCHAR(40) instead of CHAR(36)?

    A: because it isn't really/always a UUID, they just like the sound of that name.

  • (nodebb) in reply to dpm

    Perhaps because they're used to code using 40-character buffers for UUIDs: 36 plus both braces and null-terminator makes 39, usually rounded up to 40 because it's a nice, round number.

    So you #define UUID_BUFFER_SIZE 40 and then you use 40 as well by reflex when creating the table (unless your combination of RDMBS, library and knowledge allows using an UUID type instead).

  • (nodebb) in reply to dpm

    It could be worse. At the place I work, they would've made it an NVARCHAR. Just in case the hexadecimal text has unicode characters in it, I guess.

  • Tim R (unregistered)

    That reminds me of this classic DailyWTF duplicate GUID problem from waaaay back in 2011

    https://thedailywtf.com/articles/A-More-Unique-Identifier

  • (nodebb) in reply to Balor64

    Just in case the hexadecimal text has unicode characters in it, I guess

    (wide-eyed innocent look)

    But hexadecimal text does have unicode characters in it! '0' to '9' and 'a' to 'f' (or 'A' to 'F') are all unicode characters.

    (/wide-eyed innocent look)

  • (nodebb)

    The record type looks like if they wanted to map an object oriented design to a database schema, and it defines the actual type. In this case it can only be one, but in others there may be more options, and they keep it for consistency (maybe an auto-generated schema or at least generic queries).

    It's super badly named for that, though, and it doesn't make sense to make it a primary key value.

  • (nodebb)

    MSSQL already has a datatype "uniqueidentifier", that is easy to generate and I assume is faster.

    This reminds me of opening a DB in SSMS, and seeing columns with True/False values in my own language. "Odd, did not know SSMS localised bit/boolean." No, somebody used strings for that. Case sensitive strings.

  • (nodebb)

    No, somebody used strings for that.

    BTSTT. Five years ago, when I first opened the application I'm maintaining now (MVC .NET, MSSQL) and saw "Y" and "N" for one column, I swear I had the same thought. Nope!

    [DeletePending] nvarchar NULL

    for a value which is /always/ "Y" or "N", never even null or empty or blank. WTF.

  • (nodebb)

    Lovely: my comment is being "held for moderation" . . . despite the fact that I am logged in.

  • (nodebb)

    Someone who knew what they were doing finally seeded a random number generator and committed the change and no one noticed during code review?

  • (nodebb)

    First of UUIDs are not unique, that is a misconception, collisions are highly unlikely. Considering that in my 25 years of professional experience I already witnessed myself a collision TWICE, I'd say whoever things there can't be collision hasn't been in the biz long enough (or never dealt with what is these days called "big data").

    Secondly, there's a good reason for composited keys with UUIDs and one example would be soft delete and versioning implementations. Obvious the ENUM in this example is odd, but yes, those tables are by default not simply addressable by a unique ID for obvious reasons. No idea what was here going on but you have always be careful with assumptions especially without knowing the business/use case of code.

  • Twither (unregistered) in reply to MaxiTB
    Comment held for moderation.
  • (nodebb)

    Beyond just the issue of 36 or 40 character "UUID"s as stroings, why VARCHAR which is for storing variable length strings, versus CHAR wich is for fixed-length strings.

    Damned good bet that however they choose to format their "UUID"s, they'll all be the same length.

    Addendum 2025-07-22 14:22: Gaah. I love typos in non-editable input boxes. Sorry to hurt all your eyes, folks!

  • Officer Johnny Holzkopf (unregistered)
    Comment held for moderation.
  • (nodebb) in reply to Steve_The_Cynic

    Given the UUID stuff I'm guessing that's SQL Server, which means the reason in both cases is "Microsoft".

    You're welcome.

Leave a comment on “A Unique Way to Primary Key”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article