3

First off, I am assuming that it is possible for a database to generate but not insert a duplicate UUID value for a column that has a unique constraint.

Is there any intelligent way that databases typically create UUID values for unique columns or does it simply go through the loop of:

  1. Generate a random UUID value for the column.
  2. Check to see if the value is unique in the context of that column.
  3. Insert the value into the column if it is unique, go back to step 1 if it isn't.

until it generates a unique value. I'm aware that the possibility of the database having to do a second iteration in a situation like this is infinitesimally small, but it's still possible. I'm just wondering how database engines deal with this possibility and if my description of the loop is similar to what actually happens.

  • UUID is not a random code, en.wikipedia.org/wiki/Universally_unique_identifier#Format – McNets Mar 15 '17 at 14:43
  • It's not guaranteed unique unless there is a unique constraint on the column. And it is theoretically possible (especially in the world of virtual servers) to have multiple computers generate the same UUID. This link has more details (MSSQL) technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx Finally, at least for databases you should use the NEXT SEQUENTIAL ID if you are going to insist on using UUID's as the primary key. Otherwise you are going to end up with a heavily fragmented table. – Jonathan Fite Mar 15 '17 at 14:43
  • @McNets Thanks for the link and for clearing up that misunderstanding but it's not relevant to the question I was asking. Edited the question to be more clear. – CanadaIT Mar 15 '17 at 14:48
  • @JonathanFite Thanks John, but my question is about the internals of database operation. My question is, what happens when a database chooses a value that is not unique to go into a column with a unique constraint? Does it just keep choosing more values until there are no violations, or does it have a way to intelligently "find" a value that is unique. – CanadaIT Mar 15 '17 at 14:52
  • DB cannot generate a 'random UUID', and it doesn't need to see if it is unique, – McNets Mar 15 '17 at 14:57
1

Generating UUIDs (or any values, for that matter -- e.g. new sequence values) and inserting whatever into tables are two distinct and unrelated actions.

The database engine cannot know for what purpose the ID is generated -- it simply returns that ID value to the application, which uses it as it sees fit.

When the application inserts something, not necessarily a UUID it may have obtained earlier, into a column on which a unique constraint is defined, the database engine will verify the value uniqueness according to the constraint rules and return an error to the application if validation fails. It's the application's job to deal with the error as it sees appropriate. It can request a new UUID from the database, manipulate the value it already has, or simply fail.

  • Thanks. So even though the database is aware that generating a UUID is its responsibility, it will simply error out if it generates a value that fails to satisfy the unique constraint? So if I understand correctly, the database engine developers are choosing not to handle this scenario because the possibility is so small? In this case, the application will just have to detect the error and issue another create command? – CanadaIT Mar 15 '17 at 16:03
  • No, you don't understand. You know it's a UUID, the database engine does not know or care what it is, whether it's supposed to be unique, or how it's used. Totally unrelated to that, if an application attempts to insert a non-unique value, UUID or not, into a column with a unique constraint, the database engine won't allow that without any knowledge of the value semantics. – mustaccio Mar 15 '17 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.