0

I want to update my table with a new column UUID as Primary key.

I used this, but it generates the same UUID while I want to add UUID as PK constraint

ALTER TABLE TEST 
    ADD ID_NUM VARCHAR(255)
    DEFAULT REGEXP_REPLACE(RAWTOHEX(SYS_GUID()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '\1-\2-\3-\4-\5')
    NOT NULL;

How to do this?

ranell
  • 683
  • 13
  • 29
  • 2
    I suppose they are "not same", but "differ (only) at one char" ... https://stackoverflow.com/q/3037462/592355 – xerx593 Apr 03 '20 at 10:37
  • 4
    Store the value as bytes. Convert to a string when you want to see the formatted value: https://stackoverflow.com/questions/153815/how-should-i-store-a-guid-in-oracle. – Gordon Linoff Apr 03 '20 at 10:43
  • 1
    @xerx593 You're right ! I didn't notice this difference ! Thanks a lot! – ranell Apr 03 '20 at 11:12
  • 3
    Oracle recommends to store UUIDs in a `raw(16)` column. Don't use varchar (and why the limit on 255 characters if you know it will never be longer than 36?) –  Apr 03 '20 at 11:18
  • Thanks for these precious advices. Is there any risk to have duplicates values in future since the inserted UUID are randoms ? – ranell Apr 03 '20 at 11:49
  • 2
    The chance to get duplicates is indeed not zero. But it's so small that it's highly unlikely that you will ever get them. –  Apr 03 '20 at 12:27
  • So why are you manipulating SYS_GUID() instead of just using raw SYS_GUID() ? – APC Apr 03 '20 at 21:12
  • @a_horse_with_no_name: I push for `raw(16)` all the time. Do you happen to have a source where Oracle recommends it? – wolφi May 06 '20 at 21:56
  • @wolφi the [example about sys_guid()](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SYS_GUID.html#GUID-761E36B4-32DA-497D-8829-3D4653381F9B) obviously the return value of a function should be stored in a column that matches the data type of that returned value. And [Ask Tom](https://asktom.oracle.com/pls/apex/asktom.search?tag=using-guids-as-primary-keys-20100419130634) –  May 07 '20 at 05:23
  • @a_horse_with_no_name That'll do nicely, thanks very much! – wolφi May 07 '20 at 09:48

0 Answers0