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

you could try a before nsert trigger and get the max number of bith and add one, but ths system can fail. so why not use uuidv4s , they are unique — nbk, Oct 23 '20 at 04:02
insetad of the number you have a varchar(36) field and add the builtin function uuid() to the insert.or beeter the [uuidv4](https://stackoverflow.com/questions/32965743/how-to-generate-a-uuidv4-in-mysql) so now every id is unique and you can send them via trigger to the login page — nbk, Oct 23 '20 at 04:14
sorry I dont understand, can you answer how can I use this trigger please — karbi, Oct 23 '20 at 04:39
or, there is an idea , I put id_user auto increment in pairs and id_prof auto increment in odd , the question is , can I do this in MySQL — karbi, Oct 23 '20 at 04:53
hm Build a BEFORE INSERT TRIGGER SELECT THE max id from prof and from user and increase the highest number of both and use that as id — nbk, Oct 23 '20 at 05:11

nbk · Accepted Answer · 2021-05-22T23:24:54.773

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.

thank you so much, so I must delet my tables and re create it or can I continue with my old tables ( prof, user, login )? — karbi, Oct 23 '20 at 06:34
for the trigger solution, you need to remove the auto_incrmeet , if you have it in the for the second you need to change the int to varchar but you can use them overaall with out a problem. — nbk, Oct 23 '20 at 10:55

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

1 Answers1

Linked