CREATE TABLE nodes ( id INTEGER PRIMARY KEY, name VARCHAR(10) NOT NULL, feat1 CHAR(1), -- e.g., age feat2 CHAR(1) -- e.g., school attended or company ); CREATE TABLE edges ( a INTEGER NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE, b INTEGER NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (a, b) ); CREATE INDEX a_idx ON edges (a); CREATE INDEX b_idx ON edges (b);如果要表示一个无向图,则需要在该对的唯一性上添加一个CHECK约束。由于SQL标准不允许CHECK约束中的子查询,我如何检查该对的唯一性? 最佳答案 您可以装配看到(A,B)或(B,A)失败的触发器:这是触发器:DELIMITER $$CREATE TRIGGER edges_bi BEFORE INSERTON edges FOR EACH ROWBEGIN DECLARE found_count,dummy,diff,SomethingsWrong INT DEFAULT 0; DECLARE errmsg VARCHAR(128); SET diff = new.a - new.b; IF diff = 0 THEN SET errmsg = CONCAT('[',new.a,',',new.b,'] is Vertex, Not Edge'); SET SomethingsWrong = 1; END IF; SELECT COUNT(1) INTO found_count FROM edges WHERE (a=NEW.a AND b=NEW.b) OR (a=NEW.b AND b=NEW.a); IF found_count = 1 THEN SET errmsg = CONCAT('[',new.a,',',new.b,'] Already Exists'); SET SomethingsWrong = 1; END IF; IF SomethingsWrong = 1 THEN SELECT errmsg INTO dummy FROM edges WHERE 1=1; END IF;END; $$DELIMITER ;这是一个示例表:DROP DATABASE if exists saurabh;CREATE DATABASE saurabh;USE saurabhCREATE TABLE edges( a INTEGER NOT NULL, b INTEGER NOT NULL, PRIMARY KEY (a,b), UNIQUE KEY (b,a));请注意,我有一个PRIMARY KEY和UNIQUE KEY,而PRIMARY KEY的列却相反让我们创建表:mysql> DROP DATABASE if exists saurabh;Query OK, 1 row affected (0.01 sec)mysql> CREATE DATABASE saurabh;Query OK, 1 row affected (0.00 sec)mysql> USE saurabhDatabase changedmysql> CREATE TABLE edges -> ( -> a INTEGER NOT NULL, -> b INTEGER NOT NULL, -> PRIMARY KEY (a,b), -> UNIQUE KEY (b,a) -> );Query OK, 0 rows affected (0.12 sec)mysql>让我们创建触发器:mysql> DELIMITER $$mysql> CREATE TRIGGER edges_bi BEFORE INSERT -> ON edges FOR EACH ROW -> BEGIN -> DECLARE found_count,dummy,diff,SomethingsWrong INT DEFAULT 0; -> DECLARE errmsg VARCHAR(128); -> SET diff = new.a - new.b; -> IF diff = 0 THEN -> SET errmsg = CONCAT('[',new.a,',',new.b,'] is Vertex, Not Edge'); -> SET SomethingsWrong = 1; -> END IF; -> SELECT COUNT(1) INTO found_count FROM edges -> WHERE (a=NEW.a AND b=NEW.b) OR (a=NEW.b AND b=NEW.a); -> IF found_count = 1 THEN -> SET errmsg = CONCAT('[',new.a,',',new.b,'] Already Exists'); -> SET SomethingsWrong = 1; -> END IF; -> IF SomethingsWrong = 1 THEN -> SELECT errmsg INTO dummy FROM edges WHERE 1=1; -> END IF; -> END; $$Query OK, 0 rows affected (0.11 sec)mysql> DELIMITER ;以下是一些示例数据:INSERT INTO edges (a,b) VALUES (5,3);INSERT INTO edges (a,b) VALUES (3,3);INSERT INTO edges (a,b) VALUES (3,5);INSERT INTO edges (a,b) VALUES (5,5);SELECT * FROM edges;让我们尝试将它们加载到edges表中:mysql> INSERT INTO edges (a,b) VALUES (5,3);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO edges (a,b) VALUES (3,3);ERROR 1366 (HY000): Incorrect integer value: '[3,3] is Vertex, Not Edge' for column 'dummy' at row 1mysql> INSERT INTO edges (a,b) VALUES (3,5);ERROR 1366 (HY000): Incorrect integer value: '[3,5] Already Exists' for column 'dummy' at row 1mysql> INSERT INTO edges (a,b) VALUES (5,5);ERROR 1366 (HY000): Incorrect integer value: '[5,5] is Vertex, Not Edge' for column 'dummy' at row 1mysql> SELECT * FROM edges;+---+---+| a | b |+---+---+| 5 | 3 |+---+---+1 row in set (0.00 sec)请注意,阻止A = B条件可防止任何自循环警告如果以下情况,此触发器不起作用您以一个空表开始输入(3,3)作为第一行因为BEFORE INSERT触发器不会在空表上触发。使用A B输入有效行后,所有检查都会正确执行。试一试 !!!