Create a cross-table unique ID

Most tables are created with a unique, progressive ID. But how to create an ID whose values are unique across multiple tables? For example, if value 1 is in table A, it must not be in table B.

First we need to create a numeric sequence. Most DBMSs supports sequences. This syntax will work on PostgreSQL, MariaDB and Oracle:

CREATE SEQUENCE seq1;

Note that we could also have multiple sequences with non-overlapping values:

CREATE SEQUENCE seq1 MINVALUE 1 INCREMENT 3;
CREATE SEQUENCE seq2 MINVALUE 2 INCREMENT 3;
CREATE SEQUENCE seq3 MINVALUE 3 INCREMENT 3;

Now we need to create a table whose primary key’s default value is the next value from a sequence.

MariaDB syntax

MariaDB supports a nextval() function that can be used as a default value:

CREATE TABLE users (
    id INTEGER DEFAULT nextval('seq1'),
    ...
);

PostgreSQL syntax

PostgreSQL is similar to MariaDB:

CREATE TABLE users (
    id SERIAL DEFAULT nextval('seq1') PRIMARY KEY,
    ...
);

Oracle syntax

In oracle sequences have a nextval method:

CREATE TABLE users (
    id NUMBER(10) DEFAULT seq1.nextval,
    ...
);
ALTER TABLE users ADD(CONSTRAINT users_pk PRIMARY KEY (id));

SQL Server syntax

CREATE TABLE users (
    id INT NOT NULL DEFAULT NEXT VALUE FOR seq1,
    ...
);

Db2 syntax

In Db2 sequences cannot be used as default values in the way showed above. There is a way to create a default sequence for one table, but this won’t solve the problem of having a unique ID across multiple tables.

Instead, we will have to insert values from a sequence explicitally:

CREATE TABLE users (
    id INTEGER NOT NULL,
    ...
)
INSERT INTO users (id, ...) VALUES (NEXT VALUE FOR seq1, ...);

Informix syntax

Informix is similar tp Db2:

CREATE TABLE users (
    id INT NOT NULL,
    PRIMARY KEY (id),
    ...
);
INSERT INTO users (id, ...) VALUES (NEXT VALUE FOR seq1, ...);