• 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

    Considering that in my 25 years of professional experience I already witnessed myself a collision TWICE

    You should rethink the value of those 25 years.

    The reason to think that UUIDs are not truly unique is to suppose that the keys are just random and then wave your hands and say "Birthday Paradox" to summon a collision.

    In actuality, you need to generate a trillion-trillion UUIDs (by random generation) to have a 10% chance of collision.

    Furthermore, UUIDs are not generally generated by a random process.

    In order to actually have a collision, you'll need to do one or more of the following:

    1. Generate "random" keys using a 32 or 64 bit pseudo-random generator (this is not a valid method for generating a UUID),
    2. Reset the internal time on whatever machine is generating UUIDs,
    3. Clone a MAC address and share it between machines generating UUIDs.

    No guesses which method you've been using.

  • (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)

    We can see a form of "symptomatic programming" here. The test starts failing? Remove the test! This calculation should give 19, but often it gives 20, sometimes 21, what to do? Hardcode the expected value! There are warnings? Ignore them, they're not errors. I also made a new UUID, should solve the problem now: ÐÈADBËËF–ØØ24—ÁFFE-199€-407708154711000Ä.

  • (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.

  • (nodebb) in reply to Twither

    No sure what you are talking about, there's only one way two generate UUIDs in .net, the purely random classical variant and the v7 which is way more prone to collisions due to their smaller randomness scope.

    For starters, if you never encounter collisions then either you only do small scale hobby projects or you are working with project that actually don't properly log collisions because they happen in practice - a lot. And it is easily explained way, first off you number roughly translate to the whole scope which is not the case, because as I mentioned before there are different version which different probabilities. In fact a few version have long been deem obsolete because they were even back in the 90s very prone to collisions. And here we end up with the next issues, those are theoretical distributions, even crypto level RNGs without dedicated hardware come nowhere near those distributions especially when you are working in a localized distributed cluster. And finally you don't understand probabilities, they are averages. You can have a million collisions in a row and it would still be perfectly falling the those odds, in fact the whole point of randomness is to not have sequential predictable results.

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

Log In or post as a guest

Replying to comment #682509:

Log Out

« Return to Article