MySQL Two-Sided UNIQUE INDEX for Two Columns – How to Implement

duplicationMySQLprimary-keyunique-constraint

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)

UNIQUE INDEX (col1, col2)

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

Best Answer

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

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.

Related Solutions

Mysql – Unique combination key MySQL

Here is before trigger you can use to guarantee that id1 and id2 are inserted in min/max order

DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;

Here is some Sample Data:

DROP DATABASE IF EXISTS ryanzec;
CREATE DATABASE ryanzec;
USE ryanzec
CREATE TABLE mytable
(
    id1 INT NOT NULL,
    id2 INT NOT NULL,
    mapTypeId INT NOT NULL,
    primary key (id1,id2)
);
DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;
INSERT INTO mytable VALUES (1,2,3);
INSERT INTO mytable VALUES (2,1,3);
INSERT INTO mytable VALUES (5,4,6);
INSERT INTO mytable VALUES (4,5,6);
SELECT * FROM mytable;

Here it is the Sample Data Loaded

mysql> DROP DATABASE IF EXISTS ryanzec;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE ryanzec;
Query OK, 1 row affected (0.00 sec)

mysql> USE ryanzec
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id1 INT NOT NULL,
    ->     id2 INT NOT NULL,
    ->     mapTypeId INT NOT NULL,
    ->     primary key (id1,id2)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
    -> FOR EACH ROW
    -> BEGIN
    ->     DECLARE sum INT;
    ->     SET sum = NEW.id1 + NEW.id2;
    ->     SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    ->     SET NEW.id2 = sum - NEW.id1;
    -> END $$
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;

Watch What Happens When I Insert the Rows:

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

mysql> INSERT INTO mytable VALUES (2,1,3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
mysql> INSERT INTO mytable VALUES (5,4,6);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO mytable VALUES (4,5,6);
ERROR 1062 (23000): Duplicate entry '4-5' for key 'PRIMARY'
mysql> SELECT * FROM mytable;
+-----+-----+-----------+
| id1 | id2 | mapTypeId |
+-----+-----+-----------+
|   1 |   2 |         3 |
|   4 |   5 |         6 |
+-----+-----+-----------+
2 rows in set (0.00 sec)

mysql>

Now, no matter what order id1 and id2 are entered, id1 will always be less than or equal to id2

This may not be contextually what you want because id1 and id2 are unique identifiers.

Mysql – Unique insert performance: Primary Key vs Unique Index

From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be. With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE or PRIMARY; MyISAM doesn't treat them differently in this case. If you were using InnoDB, however, there would be a difference since it stores the data in primary key order. If you don't need the id column, removing it and making domain the primary key would help performance.

Changing the collation should help since ascii is much simpler than utf8, but you might want to use ascii_general_ci instead of ascii_bin since domain names are case-insensitive.

One other way to do the queries would be to get the number of rows, INSERT DELAYED, flush the delayed writes, and then get the new row count. The difference in the counts would be the same as the affected rows. However, I don't think this would be significantly faster, but it would make the process more complex.

Best Answer

UPDATE 2012-02-29 11:46 EDT

Related Solutions

Mysql – Unique combination key MySQL

Mysql – Unique insert performance: Primary Key vs Unique Index

Related Question