Re: looking for a globally unique row ID

On 2017-09-14 15:06, Rafal Pietrak wrote:
> W dniu 14.09.2017 o 10:57, George Neuner pisze:
>> On Thu, 14 Sep 2017 09:45:59 +0200, Rafal Pietrak <rafal(at)ztk-rp(dot)eu>
>> wrote:
>>
>>> Hello everybody,
>>>
>>> Can anybody help me find a way to implement an ID which:
>>>
>>> 1. guarantees being unique across multiple tables.
>>>
>>> 2. guarantees its uniqueness not only during INSERT, but also during
>>> the
>>> lifetime of the database/application (e.i. during future UPDATES).
>>>
>>> 3. guarantees persistence of value across database
>>> backup/restore/upgrade.
>
> Seeing the answers I feel, I should probably have added:
>
> 4. not necessarily guarantee "planetary wide" uniquness. Meaning:
> backup/restore should instantiate those ID dupplication on the second
> instance of the database.
>
>>
>> UUID is the obvious choice, but it does take a lot of space.
>
> I was hoping for something like a database-scoped "primary key" - which
> in particular does not need to be anything big.... provided the dataset
> is small.
>
> As far as I can tell, UUID is an ID, that is "simple/fast" to generate,
> and has "extremally low" probability of collisions.
>
> Instead I was looking for a "mechanizms/program-sql-idioms" which don't
> have to be particularly efficient, but once generated, no matter what,
> the uniqueness is asurred by the database. Including UPDATEs - e.i.
> assignment of a completly new ID for a particular ROW.
>
> But I understand I may quit searching - there is nothing "so simple".
>

If it is only one database, on one server, then couldn't you just use
one sequence?
If oyu prefix the value with some identifier of the current table then
you cannot get duplicates
across tables even if you reset the sequence.