5 Ответы Последний ответ: 12.01.2019 5:21, автор: racman

    Using SYS_GUID as Primary key / Unique IDs

    M.Emmanuel

      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!,