Bidirectional single key constraint for a combination of two columns

Question

I have columns below a table in MySQL.

id user_primary_email user_secondary_email 

I want to make the combination of user_primary_email and user_secondary_email unique that I can achieve using UNIQUE KEY unique_key_name (user_primary_email, user_secondary_email)

The above adding a unique key constraint will help me achieve the scenario below, or rather just adding a unique key to the single column itself.

 user_primary_email = ' [e-mail] ' AND user_secondary_email = ' [e-mail] ' user_primary_email = ' [e-mail] ' AND user_secondary_email = ' [e-mail] ' //This will not be allowed to enter due to unique key constraint 

Now the problem I am facing is the same combination that cannot be added in the reverse order, as indicated below.

 user_primary_email = ' [e-mail] ' AND user_secondary_email = ' [e-mail] ' //This should not be allowed to add since already same email id combination added once id | user_primary_email | user_secondary_email ------------------------------------------------------- 1 | [e-mail] | [e-mail] ------------------------------------------------------- 2 | [e-mail] | [e-mail] ------------------------------------------------------- 

In the above case, when inserting the line id 2, it should throw an error, because as a combination of email identifiers it is already used in line id 1 .

Any help would be great.

Answer 1

In any MariaDB application:

 CREATE TABLE `t` ( `id` int(11) NOT NULL, `user_primary_email` varchar(64) DEFAULT NULL, `user_secondary_email` varchar(64) DEFAULT NULL, `mycheck` varchar(128) AS (IF(user_primary_email<user_secondary_email,CONCAT(user_primary_email,user_secondary_email),CONCAT(user_secondary_email,user_primary_email))) PERSISTENT, PRIMARY KEY (`id`), UNIQUE KEY `mycheck` (`mycheck`) ); MariaDB [test]> insert into t values (1,'a','b',null); Query OK, 1 row affected (0.03 sec) MariaDB [test]> insert into t values (2,'b','a',null); ERROR 1062 (23000): Duplicate entry 'ab' for key 'mycheck' 

Answer 2

There is no direct support for this, but you can use a workaround to create a bidirectional key: you need a unique key in an ordered version of your two columns.

Fortunately, you can do this very easily. MySQL 5.7.6+ supports created columns and unique indexes for them, which you can use to order two values ​​and to ensure uniqueness.

 create table testBiDirKey ( a varchar(100), b varchar(100), a_ordered varchar(100) as (least(a, b)) STORED, b_ordered varchar(100) as (greatest(a, b)) STORED, unique key unqBi_test_ab (a_ordered, b_ordered) ); insert into testBiDirKey(a,b) values('a', 'b'); insert into testBiDirKey(a,b) values('b', 'a'); Error Code: 1062. Duplicate entry 'ab' for key 'unqBi_test_ab' 

This will handle null just like your current regular unique key, therefore

 insert into testBiDirKey(a,b) values('a', null); insert into testBiDirKey(a,b) values('a', null); insert into testBiDirKey(a,b) values(null, 'a'); 

all are allowed. You can add coalesce(x,'') for just one empty value (or null OR '' ) if you want. If you check your values ​​before adding them (for example, if they do not contain , ), you can combine these two columns with only one combined with , - albeit with a slight advantage, except that you have only 1 additional column .

For 5.7.8+, you no longer need the STORED keyword to use these columns in the index. This keyword is valid if values ​​are saved (using disk space) or calculated if necessary (default).

Before MySQL 5.7.6, you can use a trigger (for update and insert ) to update two columns with these values, the same logic applies, this is just a little more code.