How do Databases Ensure Uniqueness of Primary Keys, Particularly UUID Keys

database-internalsprimary-keyunique-constraint

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.

Best Answer

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.

Related Question