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.
Advertisement
Answer
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