How to make `id` automatically set by a query instead of `AUTO_INCREMENT`

Question

I have multiple tables for different kinds of objects. However, the ID of every object must be unique not only among objects of the same type but also across all objects of different types. In other words, the ID should be unique across different tables. To implement this concept, I created a special table with a single value counter, and I modified the INSERT queries to use it. Now it looks like this:

CREATE TABLE next_id (id INT NOT NULL); -- Table-counter
INSERT INTO next_id VALUES (1);         -- The only value that points to the id for the next object

CREATE TABLE object1 (              -- One of object kinds
    id INT NOT NULL PRIMARY KEY,
    mass INT NOT NULL               -- ... Another one specialized for object1 fields
);

CREATE TABLE object2 (              -- Other object kind
    id INT NOT NULL PRIMARY KEY,
    angles INT NOT NULL             -- ... Another one specialized for object2 fields
);

INSERT INTO object1 (id, mass) VALUES (   -- Inserting object1
    (
        SELECT id                         -- Setting it's id by getting it from the counter
        FROM next_id
    ), 5);
UPDATE next_id SET id=id+1;         -- Changing the counter so that the next object has a different id

INSERT INTO object2 (id, angles) VALUES ( -- The same thing, but for other object kind
    (
        SELECT id
        FROM next_id
    ), 3);
UPDATE next_id SET id=id+1;

But it looks very strange, and I'm worried that the UPDATE statement could be executed later by the planner, causing ID collisions and other issues. Can this be changed so that the ID behaves like AUTO_INCREMENT? I mean, setting the ID automatically, like this:

INSERT INTO object1 (mass) VALUES (5); -- Id is set automatically

Answer 1

You can use Triggers like this

first, we need to create a table next_id

CREATE TABLE next_id (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

Initialize the ID table

INSERT INTO next_id VALUES (NULL);

Now create a trigger for each table

1. object1

CREATE TRIGGER before_insert_object1
BEFORE INSERT ON object1
FOR EACH ROW
BEGIN
    DECLARE new_id INT;
    SET new_id = (SELECT id FROM next_id FOR UPDATE);
    SET NEW.id = new_id;
    UPDATE next_id SET id = id + 1;
END;

2. object2

CREATE TRIGGER before_insert_object2
BEFORE INSERT ON object2
FOR EACH ROW
BEGIN
    DECLARE new_id INT;
    SET new_id = (SELECT id FROM next_id FOR UPDATE);
    SET NEW.id = new_id;
    UPDATE next_id SET id = id + 1;
END;

Alternative you can use the UUIDs like this

CREATE TABLE object1 (
    id CHAR(36) NOT NULL PRIMARY KEY,
    mass INT NOT NULL
);

CREATE TABLE object2 (
    id CHAR(36) NOT NULL PRIMARY KEY,
    angles INT NOT NULL
);

Insert Data using UUIDs

INSERT INTO object1 (id, mass) VALUES (UUID(), 5);
INSERT INTO object2 (id, angles) VALUES (UUID(), 3);