MySQL: unique informations cross 2 tables

Question

I have 2 tables battles & battle_user

structure:

battles table:

id     create_date
1        2015/...

battle_user table:

id     battle_id     user_id
1          1           1
2          1           2

Only 2 users accepted in each battle, the question is: Is there a way using primary/foreign keys (or indexing) to prevent insert another battle for same 2 users?

prevent this:

battles table:

id     create_date
1        2015/...
2        2015/...

battle_user table:

id     battle_id     user_id
1          1           1
2          1           2
3          2           1
4          2           2

prevent create 2 battles between same 2 users

If I'm not mistaken, the only way to prevent that would be by using a `trigger`. — sgeddes, Jun 22 '16 at 21:38
Can a user be involved in more than 1 battle? If not than all you have to do is set user_id as a unique index. This will first prevent a user from being in 2 battles at the same time. — nbayly, Jun 22 '16 at 21:39
Trigger before insert to check if it already existed @sgeddes ? — mwafi, Jun 22 '16 at 21:39
@nbayly yes user can be involved in infinite battles with different users — mwafi, Jun 22 '16 at 21:40
How are you calling the insert of the users? Can you show your code? The problem I see with using a trigger is that there is no easy way once your condition is met to drop the insert. At least from my experience in MySQL. It's not impossible but your issue might be better served with a `WHERE` in the insert. — nbayly, Jun 22 '16 at 21:46
@nbayly I want to prevent it in DB itself, prevent accept this insertion, (like prevent duplicate primary key) — mwafi, Jun 22 '16 at 21:47
Yeah, gets a little ugly if you cannot handle the inserts though. Are they at least transaction based? If not, you would allow the first insert but then have to decide how to handle the subsequent (duplicate) insert -- perhaps deleting the previous insert if it's now a duplicate. This would be much better to handle in your application. — sgeddes, Jun 22 '16 at 21:50
There's no built-in way to do this. The best way is to use a trigger that signals an error if it detects a a duplicate. http://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions/22489342#22489342 — Barmar, Jun 22 '16 at 21:51
OP In the spirit of clarity can you provide the insert command you are attempting to use? @Barmar the problem is that there can be duplicate users, there just can't be more than 2 users in a battle at a time. This would not trigger a unique restriction because there is none, only over multi rows. — nbayly, Jun 22 '16 at 21:54
@nbayly I know it won't be detected by the unique restriction. That's why I suggested he use `CREATE TRIGGER` to perform his check. — Barmar, Jun 22 '16 at 22:08
of course there is a way. You denormalize your table with additional columns and create a unique index. The question is, would you want to do that. — Drew, Jun 22 '16 at 23:16
I will reluctantly make a short answer out of it if you want. With a caveat saying at least it is a way. — Drew, Jun 23 '16 at 00:34

Drew · Answer 1 · 2016-06-23T02:34:17.773

As I said in the comments, I reluctantly will show how a unique key can be used in a de-normalized fashion, and the OP wanted to see it.

The concept is that you know the users, so have them wedged in the battles table. How you then proceed on to a battle_users table is up to you and I would recommend no change there.

create table battlesA1
(   id int auto_increment primary key,
    createDt datetime not null,
    user1 int not null,
    user2 int not null,
    -- FK Constraints go here (not shown, FK to users table)
    -- Then unique constraint goes here
    unique key(user1,user2) -- user1 is less than user2 on insert
    -- so on insert, utilize the least() and greatest() functions (mysql)
    -- or your front-end programming language
);

insert battlesA1(createDt,user1,user2) values ('2016-06-14 12:30:00',1,2);
-- success
insert battlesA1(createDt,user1,user2) values ('2016-06-14 12:30:00',1,2);
-- Error 1062, Duplicate Entry

least() and greatest() examples:

set @user1:=14;
set @user2:=7;
insert battlesA1(createDt,user1,user2) values ('2016-06-14 12:30:00', least(@user1,@user2), greatest(@user1,@user2) );
-- success
insert battlesA1(createDt,user1,user2) values ('2016-06-14 12:30:00', least(@user1,@user2), greatest(@user1,@user2) );
-- Error 1062, Duplicate Entry


set @user1:=6;
set @user2:=700;
insert battlesA1(createDt,user1,user2) values ('2016-06-14 12:30:00', least(@user1,@user2), greatest(@user1,@user2) );
-- success
insert battlesA1(createDt,user1,user2) values ('2016-06-14 12:30:00', least(@user1,@user2), greatest(@user1,@user2) );
-- Error 1062, Duplicate Entry

drop table battlesA1; -- perhaps the best command you see here.

Manual pages for least(), greatest() and LAST_INSERT_ID(). The latter was not used but is used often in cases like this.

So there you have it. You wanted to see it, and I am not terribly proud to show it.

But this will completely limit # of users in any battle to 2, It limited now but, Is it good to create 2 columns refer to same primary key? — mwafi, Jun 23 '16 at 06:27
Can you explain the full specs then. I found it a bit odd to have a battle for just 2 users then. I can get this to work with whatever info. The way I read what you wrote in question, this addresses how you wrote it — Drew, Jun 23 '16 at 07:51
This solves precisely what you said. It is impossible to create a second battle between these two users..... your words: *prevent create 2 battles between same 2 users* — Drew, Jun 23 '16 at 07:58
select will be harder specially when have `joins` related to `user_id` column — mwafi, Jun 23 '16 at 08:00
You will still have your `battle_users` table as I said at the top of the question. This solves your dilemna, and that is not in dispute. You can mark it solved or helpful if you so desire. This question is solved. — Drew, Jun 23 '16 at 08:02

score 0 · Answer 2 · answered Jun 22 '16 at 22:19

The condition is not simple, at the moment the only way to create a complex check before insert a row in MySQL table is a trigger.

I created a trigger that checks if there is another record with these users, but there is a problem (maybe): you will need to delete the not created battle id.

delimiter $$
drop trigger if exists `battle_user_bi`$$

create trigger `battle_user_bi` before insert 
    on `battle_user` for each row
begin
    declare msg varchar(100);
    declare usersInBattle int default 0;
    declare battleUsers int default 0;
    declare otherUser int default 0;

    set usersInBattle= (select count(*)
                    from  `battle_user` bu
                    where bu.battle_id = new.battle_id);


    if usersInBattle = 1 then  
        -- getting the other user
        set otherUser = (select user_id
                          from  `battle_user` bu
                          where bu.battle_id = new.battle_id);
        -- getting the same users in other battles
        set battleUsers = 
            (select count(*) from 
                (select bu.battle_id
                from  `battle_user` bu
                where bu.battle_id <> new.battle_id
                and bu.user_id = otherUser) 
              b1 inner join
                (select bu.battle_id
                from  `battle_user` bu
                where bu.battle_id <> new.battle_id
                and bu.user_id = new.user_id) b2 on b1.battle_id = b2.battle_id);


        if battleUsers > 0 then
            set msg = "There is already one battle for this users... ";
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
        end if;

    end if;

end$$
delimiter ;

Excuting in a empty table I get:

insert into battle_user values (1,1,1);
insert into battle_user values (2,1,2);
insert into battle_user values (3,2,1);
-- the next sentence will no execute
-- Error Code: 1644. There is already one battle for this users...
insert into battle_user values (4,2,2);

MySQL: unique informations cross 2 tables

2 Answers2