I expect to deal with potential database merges in my current project.
To avoid ID issues, instead of using Integers and auto increments, I am trying to use GUIDs as single IDs (this is the first time I try that).
What I did understand about the GUID, is that it is an unique, Oracle specific, UUID.
With that understanding in my mind, I prepared the following DDL:
CREATE TABLE country (
id RAW(16) DEFAULT sys_guid() NOT NULL,
active CHAR(1),
code_alpha2 CHAR(2),
code_alpha3 CHAR(3),
name VARCHAR2(255)
);
ALTER TABLE country ADD CONSTRAINT country_pk PRIMARY KEY ( id );
Where id is assigned sys_guid() as default value.
While trying to populate the table for first time, I noticed that GUID was actually always the same value.
SQL> INSERT INTO COUNTRY (CODE_ALPHA2, CODE_ALPHA3, NAME) VALUES ('ES', 'ESP' , 'Spain');
1 row created.
SQL> INSERT INTO COUNTRY (CODE_ALPHA2, CODE_ALPHA3, NAME) VALUES ('DE', 'DEU', 'Germany');
1 row created.
SQL> SELECT * FROM COUNTRY;
ID A CO COD NAME
-------------------------------- - -- --- ------------------------------
7F3620A311CE5506E053CF5DDE5C806F ES ESP Spain
7F3620A311CF5506E053CF5DDE5C806F DE DEU Germany
A probably redundant test:
SQL> SELECT SYS_GUID() FROM dual;
SYS_GUID()
--------------------------------
7F3620A311D05506E053CF5DDE5C806F
SQL> SELECT SYS_GUID() FROM dual;
SYS_GUID()
--------------------------------
7F3620A311D15506E053CF5DDE5C806F
Here are my questions:
1. With this information, I now assume that SYS_GUID returns a unique value per database installation. I would like to confirm if that assumption is correct or not.
2. Once that is clarified, I would like to know how could I get unique IDs automatically, (i.e., how to get UUIDs/GUIDs to ensure that all IDs generated will be different and assigned by default when no ID is provided).
I am using Oracle 18c XE.
Thanks!,