Two-sided UNIQUE INDEX for two columns

Question

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too (col2, col1)?

For example

PRIMARY KEY (col1, col2)

OR

UNIQUE INDEX (col1, col2)

If we have col1=33 && col2=54; how we can avoid INSERT of col1=54 && col2=33?

Answer 1

You may want to try creating a trigger that will check for the presence of (col1,col2) existing as (col2,col1)

Here is an example:

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;

The trigger is designed to break on purpose when FOUND_COUNT is 1.

Here is the sample executed:

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>

Give it a Try !!!

CAVEAT This does not work on bulk INSERTs. Only when inserting one row at a time.

I have used this technique and suggested it in other DBA StackExchange Questions

• check constraint does not work?
• Trigger in MySQL to prevent insertion

UPDATE 2012-02-29 11:46 EDT

I tried to INSERT the same four rows again.

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;

Here is what I get

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>

This trigger approach work well despite the quirky message.

Answer 2

In a standard SQL dbms, you'd enforce that kind of requirement by ordering the id numbers, and using a CHECK constraint. Application code, a stored procedure, or a user-defined function is responsible for putting the id numbers in the right order.

create table friends (
  user_a integer not null,  -- references users, not shown
  user_b integer not null,  -- references users, not shown
  primary key (user_a, user_b),
  check (user_a < user_b)
);

But MySQL doesn't enforce CHECK constraints. Using MySQL, I'd still use a stored procedure (not application code) to put the id numbers in the right order. But I'd also run a query or a report every now and then to make sure that all the values for user_a were less than the values for user_b.

select *
from friends
where user_a >= user_b;

I probably wouldn't include an UPDATE statement to fix those values at first. I'd want to track down what process, application, or user was going around my stored procedure to insert data directly into the table. (You can revoke direct access to the table, and require all access to go through stored procedures. But this isn't practical in some legacy systems; too much code to rewrite. And there are alternatives.)

Unfortunately, some requirements have to be implemented as administrative procedures, like running reports.

Answer 3

These rules cannot be enforced by a unique index within MySQL, even when you use a reverse index. You need to enforce this rule within your application business logic or using a database trigger/function/procedure which checks the data before it is saved.

Answer 4

I know this is a bit late to the game, but might I add...

We have a similar situation except that where ID1 and ID2 are the same, they actually link to the same table providing a related feature of sorts, again, many to many.

Since we'd be querying looking for potential ID's that might exist in either column, two indexes is actually more logical.... and expedient.... and not very costly.

UNIQUE INDEX (col1, col2) + UNIQUE INDEX (col2, col1)

Answer 5

If you are using integers/numbers, always make col1 the lower value and col2 the higher value when you modify or query the table.