兩列的雙面 UNIQUE INDEX

創建具有復合PRIMARY KEY或使用UNIQUE INDEX兩列的表可保證 col1、col2 的唯一性。是否有一種棘手的方法可以使兩列的反向順序也唯一（col2，col1）？

例如

PRIMARY KEY (col1, col2)

要麼

UNIQUE INDEX (col1, col2)

如果我們有col1=33 && col2=54；我們怎樣才能INSERT避免col1=54 && col2=33？

您可能想嘗試創建一個觸發器，該觸發器將檢查 (col1,col2) 是否存在為 (col2,col1)

這是一個例子：

use test
drop table if exists ali;
create table ali
(
   col1 int not null,
   col2 int not null,
   primary key (col1,col2)
);
DELIMITER $$
CREATE TRIGGER ali_bi BEFORE INSERT ON ali FOR EACH ROW 
BEGIN 
   DECLARE found_count,newcol1,newcol2,dummy INT;
   SET newcol1 = NEW.col1;
   SET newcol2 = NEW.col2;
   SELECT COUNT(1) INTO found_count FROM ali
   WHERE col1 = newcol2 AND col2 = newcol1;
   IF found_count = 1 THEN
       SELECT 1 INTO dummy FROM information_schema.tables;
   END IF;
END; $$ 
DELIMITER ;
INSERT INTO ali VALUES (1,2);
INSERT INTO ali VALUES (3,4);
INSERT INTO ali VALUES (2,1);
INSERT INTO ali VALUES (4,3);
SELECT * FROM ali;

觸發器設計為在 FOUND_COUNT 為 1 時故意中斷。

這是執行的範例：

mysql> use test
Database changed
mysql> drop table if exists ali;
Query OK, 0 rows affected (0.03 sec)

mysql> create table ali
   -> (
   ->     col1 int not null,
   ->     col2 int not null,
   ->     primary key (col1,col2)
   -> );
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER ali_bi BEFORE INSERT ON ali FOR EACH ROW
   -> BEGIN
   ->     DECLARE found_count,newcol1,newcol2,dummy INT;
   ->     SET newcol1 = NEW.col1;
   ->     SET newcol2 = NEW.col2;
   ->     SELECT COUNT(1) INTO found_count FROM ali
   ->     WHERE col1 = newcol2 AND col2 = newcol1;
   ->     IF found_count = 1 THEN
   ->         SELECT 1 INTO dummy FROM information_schema.tables;
   ->     END IF;
   -> END; $$
Query OK, 0 rows affected (0.07 sec)

mysql> DELIMITER ;
mysql> INSERT INTO ali VALUES (1,2);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO ali VALUES (3,4);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO ali VALUES (2,1);
ERROR 1172 (42000): Result consisted of more than one row
mysql> INSERT INTO ali VALUES (4,3);
ERROR 1172 (42000): Result consisted of more than one row
mysql> SELECT * FROM ali;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql>

試一試 ！！！

CAVEAT 這不適用於批量插入。僅在一次插入一行時。

我已經使用了這種技術，並在其他 DBA StackExchange Questions 中提出了建議

• 檢查約束不起作用？
• 在 MySQL 中觸發以防止插入

更新 2012-02-29 11:46 EDT

我試圖再次插入相同的四行。

INSERT INTO ali VALUES (1,2);
INSERT INTO ali VALUES (3,4);
INSERT INTO ali VALUES (2,1);
INSERT INTO ali VALUES (4,3);
SELECT * FROM ali;

這是我得到的

mysql> INSERT INTO ali VALUES (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
mysql> INSERT INTO ali VALUES (3,4);
ERROR 1062 (23000): Duplicate entry '3-4' for key 'PRIMARY'
mysql> INSERT INTO ali VALUES (2,1);
ERROR 1172 (42000): Result consisted of more than one row
mysql> INSERT INTO ali VALUES (4,3);
ERROR 1172 (42000): Result consisted of more than one row
mysql> SELECT * FROM ali;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql>

儘管有古怪的資訊，但這種觸發方法效果很好。

引用自：https://dba.stackexchange.com/questions/14109