Mysql : How to check uniqueness of pair

mysql, sql

Solution

You could rig up a trigger that fails upon seeing either `(A,B)` or `(B,A)` :

Here is the Trigger:

DELIMITER $$
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; $$
DELIMITER ;

Here is a sample table:

DROP DATABASE if exists saurabh;
CREATE DATABASE saurabh;
USE saurabh
CREATE TABLE edges
(
   a INTEGER NOT NULL,
   b INTEGER NOT NULL,
   PRIMARY KEY (a,b),
   UNIQUE KEY (b,a)
);

Notice that I have a PRIMARY KEY and a UNIQUE KEY with the columns of the PRIMARY KEY reversed

Let's create the table:

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 saurabh
Database changed
mysql> 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>

Let's create the Trigger:

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 ;

Here is some sample data:

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;

Let's try loading these into the `edges` table:

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 1
mysql> INSERT INTO edges (a,b) VALUES (3,5);
ERROR 1366 (HY000): Incorrect integer value: '[3,5] Already Exists' for column 'dummy' at row 1
mysql> 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 1
mysql> SELECT * FROM edges;
+---+---+
| a | b |
+---+---+
| 5 | 3 |
+---+---+
1 row in set (0.00 sec)

Note that blocking A=B conditions prevents any self-loops

CAVEAT

This trigger does not work if

- you start with an empty table

- enter `(3,3)` as the first row

because the `BEFORE INSERT` trigger does not fire on an empty table.

Once you enter a valid row with `A` <> `B` then all checks are performed properly.

Give it a Try !!!

Problem

``` 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); ``` If we want to represent an undirected graph, we need to add a CHECK constraint on the uniqueness of the pair. Since the SQL standard does not allow a subquery in the CHECK constraint,How can i check uniqueness of the pair?

Original source