Mysql table multiple primary key relation

Question

I have table with 2 columns. ID1 and ID2. I want make 2 columns as primary key.

When insert ID1=22 , ID2=55 and ID1=55 , ID2=22 this as same and do not insert and show error!

Note*: This table is a relation ship. Same bro and sis. Really have 3 colmuns: ID1 , ID2 , TYP.

ID1 = first user id
ID2 = second user id
TYP = type of relation ship(bro/sis/uncle/aunt...)

Now how can i set all columns as primary? When ID1 and ID2 as how i explain above and also make TYP primary too? Thanks@>

score 1 · Answer 1 · edited May 23 '17 at 12:25

You can have primary keys with multiple columns like in this or in this example. Simply add PRIMARY KEY (ID1, ID2) to your create table statement.

What I'm a bit worried about is that you say (22/55) and (55/22) should be detected as duplicates. With PK on ID1 and ID2 alone that would not be the case. (22/55) and (55/22) are different, but that's perfectly OK with a table for relationships: If 22 is the aunt of 55, then 55 is the nephew of 22, so there should be two rows in that table.

score 1 · Accepted Answer · answered Feb 20 '16 at 20:25

MySQL does not have an easy way of identifying that (id1, id2) and (id2, id1) should be the same.

So, you would need to create a trigger to handle this. If (id1, id2) is already a primary or unique key, then something like this:

delimiter $$
create trigger tr_table_insert before insert on t
for each row 
begin
    if (exists (select 1
                from t
                where t.id2 = new.id1 and t.id1 = new.id2
               )
       ) then
        set msg = concat('Combination ', new.id1, ' and ', new.id2, ' already exists);
    signal sqlstate '45000' set message_text = msg;
    end if;
end;
delimiter $$;

Mysql table multiple primary key relation

2 Answers2