use case for uuid in database-CodePudding

Let's say for example I have a table called test and the data is like that:

id name
1  John
2  Jay
3  Maria

Let's suppose this test gets updated and now the ids the names are for some reason allocated to different id , consider the name column as a unique column, it's just not the pprimary key of test but unique.

Next time I query test it may look like that:

   id name
    10  John
    12  Jay
    13  Maria

So in that case the id changed but the name is consistent can be traced back to the previous state of the test table. I believe this is bad practice to change id like that, but I don't have control over this table and this is how some folks handle right now the data. I would like to know if this is a good case for using uuid ? I'm not familiar with the concept of uuid, and how it's best to create something consistent as uniquely identifiable and also fast on search when I want to handle the data changes in this table. I would like to import this table on my end but create a key that is fast and that will not change during data imports.

CodePudding user response：

I feel like the problem you're trying to solve isn't clear.

Problem 1: The id column keeps getting updated. This seems weird so getting to the root of why that is happening seems like the real issue to resolve.

Problem 2: Uniquely identifying rows. You would like to use the id column or a new uuid column to uniquely identify but you've already said you can uniquely identify rows with the name column so what problem are you trying to solve here.

Problem 3: Performance. You're going to get best performance using an indexed integer (preferably primary key) column. Most likely id in this case. uuid won't help with performance.

Problem 4: Data changing on imports. This is likely due to auto increments or initial values set differently in DDL. You need to get a better understanding of what exactly is going wrong with your import.

Problem 5: If you don't have control over the values of the id column how would you be able to add your own uuid?

uuid is just a way of creating a unique value.

Oracle has a function to create uuid random_uuid().

CodePudding user response：

This is an XY-problem.

You have data in your table with a unique key in a given data type and when it gets reloaded then the unique key is being regenerated so that all the data gets given new unique values.

The data type you use does not matter; the issue is with the process of reloading the data and regenerating the unique keys. If you use a different data type and the unique keys are still regenerated then you have exactly the same problem.

Fix that problem first.