Двунаправленное ограничение уникального ключа для комбинации двух столбцов

У меня есть следующие столбцы таблицы в MySQL.

id
user_primary_email
user_secondary_email

Я хочу сделать комбинацию столбцов user_primary_email и user_secondary_email уникальной, чего я могу достичь с помощью UNIQUE KEY unique_key_name (user_primary_email, user_secondary_email)

Приведенное выше добавление ограничения уникального ключа поможет мне достичь описанного ниже сценария или, скорее, просто добавив уникальный ключ к самому отдельному столбцу.

user_primary_email = 'xyz@gmail.com' AND user_secondary_email = 'pqr@gmail.com'
user_primary_email = 'xyz@gmail.com' AND user_secondary_email = 'pqr@gmail.com' //This will not be allowed to enter due to unique key constraint
Теперь проблема, с которой я сталкиваюсь, заключается в том, что та же комбинация не должна быть добавлена обратным образом, как указано ниже. 
user_primary_email = 'pqr@gmail.com' AND user_secondary_email = 'xyz@gmail.com' //This should not be allowed to add since already same email id combination added once

id    |   user_primary_email   |   user_secondary_email
-------------------------------------------------------
1     |   xyz@gmail.com        |   pqr@gmail.com
-------------------------------------------------------
2     |   pqr@gmail.com        |   xyz@gmail.com       
-------------------------------------------------------

В в приведенном выше случае при вставке идентификатора строки 2 он должен выдать ошибку, так как обе комбинации идентификаторов электронной почты уже используются в идентификаторе строки 1.

Любая помощь была бы очень кстати.
2 5
mysql mariadb

2 ответа:

В любом MariaDB: 

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'
5

Для этого нет прямой поддержки, но вы можете использовать обходной путь для создания двунаправленного ключа: вам нужен уникальный ключ для упорядоченной версии ваших двух столбцов.

К счастью, вы можете очень легко сделать это. MySQL 5.7.6+ поддерживает сгенерированные столбцы и уникальные индексы для них, которые вы можете использовать, чтобы упорядочить ваши два значения и обеспечить уникальность. 
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 'a-b' for key 'unqBi_test_ab'

Это будет рассматривать null точно так же, как ваш текущий нормальный уникальный ключ, поэтому 

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

Все разрешено. Вы можно добавить coalesce(x,''), чтобы разрешить только одно пустое значение (либо null, либо ''), Если вы хотите. Если вы проверяете свои значения перед их добавлением (например, если они не содержат ,), Вы можете объединить эти два столбца в один, объединенный с , - хотя и с небольшой пользой, кроме просто наличия 1 дополнительного столбца.

Для 5.7.8+ вам больше не нужно ключевое слово STORED, чтобы иметь возможность использовать эти столбцы в индексе. Это ключевое слово влияет, если значения хранятся (с использованием дискового пространства) или вычисляется при необходимости (по умолчанию).

Перед MySQL 5.7.6, вы можете использовать триггер (на update и insert), чтобы обновить два столбца с этими значениями, та же логика применяется, это просто немного больше кода.

3