Oracle sql | Share generated by default on null as identity

Question

Is it possible to share the same "pool" for ids when using following syntax?

create table TEST (
   ID INTEGER
      generated by default on null as identity ( start with 1 nocycle noorder)  not null
      constraint CKC_ID  check (ID >= 0),
   constraint PK_ID  primary key (ID )
);


create table TEST2 (
   ID INTEGER
      generated by default on null as identity ( start with 1 nocycle noorder)  not null
      constraint CKC_ID2  check (ID >= 0),
   constraint PK_ID2  primary key (ID )
);

When both attribute have the same name? The only possibility I came up was to start both at different values. Like Test on 1000 and test 2 on 2000 but this is not a long term solution. I'm looking to a solution where I can "share" the same "pool" for ids, so that the id's will never overlap.

Answer 1

You can use DEFAULT ON NULL with a sequence:

CREATE SEQUENCE test_seq;

CREATE TABLE TEST (
   ID INT
      DEFAULT ON NULL TEST_SEQ.NEXTVAL
      NOT NULL
      CONSTRAINT PK_ID PRIMARY KEY
      CONSTRAINT CKC_ID  check (ID >= 0)
);

CREATE TABLE TEST2 (
   ID INT
      DEFAULT ON NULL TEST_SEQ.NEXTVAL
      NOT NULL
      CONSTRAINT PK_ID2 PRIMARY KEY
      CONSTRAINT CKC_ID2  check (ID >= 0)
);

Then if you insert some data:

INSERT INTO test ( id ) VALUES ( NULL );
INSERT INTO test2 ( id ) VALUES ( NULL );
INSERT INTO test2 ( id ) VALUES ( NULL );
INSERT INTO test ( id ) VALUES ( NULL );

Then:

SELECT * FROM test;

| ID |
| -: |
|  1 |
|  4 |

SELECT * FROM test;

| ID |
| -: |
|  2 |
|  3 |

db<>fiddle here