mysql: how put two columns unique in two tables at once

Question

hi in MySQL i want to put two (unique) fields in two different table, in my example i have three table, login table, prof and user. I want the field (id_user) and the field (id_prof) not to be repeated to insert them also after (unique) in the login table in the field (num) e.g: id_user 1, 3, 6 ... id_prof: 2, 4, 5, 7 ... num: 1 ,2, 3, 4, 5, 6, 7...

Answer 1

This is the version with two Triggers.

But it can happen, that concurrent entries will have not unique numbers, also on heavy duty servers this also fails.

So you have to take login id unique and catch when the insert fails and then try to get anew id

CREATE TABLE prof(id BIGINT, name varchar(10));

CREATE TABLE user(id BIGINT, name varchar(10))

CREATE TABLE login(id BIGINT, name varchar(10))

CREATE TRIGGER before_prof_insert
BEFORE INSERT
ON prof FOR EACH ROW
BEGIN
    DECLARE prof_ BIGINT;
    DECLARE user_ BIGINT;
    DECLARE res_ BIGINT;
    SELECT MAX(id) into prof_ FROm prof;
    SELECT MAX(id) into user_ FROm user;
    
    IF user_ IS NULL AND @prof IS NULL THEN
        SET res_ := 1;
    ELSE
        IF user_ > prof_  THEN
            SET res_ := user_ + 1;
        ELSE
            SET res_ := prof_ + 1;
        END IF;
    END if;
    SET NEW.id = res_;
    
    INSERT INTO login VALUES (res_,NEW.name);

END

CREATE TRIGGER before_user_insert
BEFORE INSERT
ON user FOR EACH ROW
BEGIN
    DECLARE prof_ BIGINT;
    DECLARE user_ BIGINT;
    DECLARE res_ BIGINT;
    SELECT MAX(id) into prof_ FROm prof;
    SELECT MAX(id) into user_ FROm user;
    
    IF user_ IS NULL AND prof_ IS NULL THEN
        SET res_ := 1;
    ELSE
        IF user_ > prof_  THEN
            SET res_ := user_ + 1;
        ELSE
            SET res_ := prof_ + 1;
        END IF;
    END if;
    SET NEW.id = res_;
    
    INSERT INTO login VALUES (res_,NEW.name);

END

INSERT INTO prof VALUES (0,'profa');)

INSERT INTO user VALUES (0,'usera');

INSERT INTO prof VALUES (0,'profb');

INSERT INTO prof VALUES (0,'profc');

INSERT INTO user VALUES (0,'userb');

INSERT INTO prof VALUES (0,'profd');

SELECT * FROM prof;

id | name 
-: | :----
 1 | profa
 3 | profb
 4 | profc
 6 | profd

SELECT * FROM user;

id | name 
-: | :----
 2 | usera
 5 | userb

SELECT * FROM login;

id | name 
-: | :----
 1 | profa
 2 | usera
 3 | profb
 4 | profc
 5 | userb
 6 | profd

db<>fiddle here

Much easier it would be

INSERT INTO user VALUES (uuidv4(),'usera');
INSERT INTO prof VALUES (uuidv4(),'profa');

This would guarantee that the ids are unique for every table.