Search code examples
mysqldatabasekeycomposite

Mysql composite key order relevance

I have this mySql table:

create table associations
(email1 varchar(30), 
email2 varchar(30),
primary key(email1, email2));

Now in this table if I insert two rows as follows:

insert into associations values('[email protected]','[email protected]');

insert into associations values('[email protected]','[email protected]');

these are acceptable in the database. What I want to do is they should not be acceptable into the database as I am only interested in the combination of the keys and not their order.

Is there any inbuilt functionality/keyword/hack in mysql(or for that matter any other database) which allows me to do so?

Solution

  • this worked for me, I came up with this trigger:

    mysql> create trigger insert_check_associations before insert on associations
-> for each row
-> begin
-> if new.email1>new.email2 then
-> set @a = new.email2;
-> set new.email2 = new.email1;
-> set new.email1 = @a;
-> end if;
-> end;//

    Query OK, 0 rows affected (0.07 sec)

    This trigger swaps the two values in the increasing order.