3

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?

Shekhar Joshi
  • 958
  • 1
  • 13
  • 26
  • Not possible as a composite primary key, but you could create a functional index. I don't think MySQL supports this though. Another solution could be to add the constraint `email1 <= email2`. – wvdz Apr 29 '14 at 21:40

2 Answers2

3

In an RDBMS that supports check constraints you might enforce that the two entries appear in a specific order.* This makes sure that duplicates of the kind you described can't happen, because one of the two orderings will be rejected. MySQL unfortunately doesn't offer check constraints, although the accepted answer to this question suggests a way to achieve a similar result using triggers.

* This is assuming that you have a total order on the column data type, which in this case you do.

Community
  • 1
  • 1
Hammerite
  • 21,755
  • 6
  • 70
  • 91
1

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.

Shekhar Joshi
  • 958
  • 1
  • 13
  • 26