Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
This smells strongly to me of a database normalization problem. If col1 and col2 are of the same type, then you should probably have modeled this with a many-to-many relationship instead of having two columns in one table. –  Dave Markle Feb 29 '12 at 16:58
    
@DaveMarkle You have a great nose ;) This is actually a normalized database of many-to-many relationship and this table is the map between two main tables. –  All Feb 29 '12 at 18:11

4 Answers 4

up vote 2 down vote accepted

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.

share|improve this answer
    
very interesting approach to solve the problem! Though I am not generally a fond of triggers; but it seems sometimes they are necessary ;) –  All Feb 29 '12 at 18:13

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.

share|improve this answer

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.

share|improve this answer
    
What you are referring to is about UNIQUE INDEX separately for each column. UNIQUE INDEX (col1, col2) make the combination unique. –  All Feb 29 '12 at 6:00
    
Yes, Ali that is what I meant, I have corrected the statement –  ssmusoke Feb 29 '12 at 6:15
    
This is the reason that I asked this question. I am looking for a trick to avoid 54,33 too; as UNIQUE INDEX cannot do this. –  All Feb 29 '12 at 6:21
    
Yes I have confirmed, the unique index does not do that even when you add the reverse, you may need to add custom logic within your application code to enforce that business rule. –  ssmusoke Feb 29 '12 at 6:37
1  
@RolandoMySQLDBA yes triggers are a necessary evil difficult to debug, but get the job done. –  ssmusoke Feb 29 '12 at 18:28

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)
share|improve this answer
1  
Those indexes are logically the same and will not enforce what the OP desires.. –  Rohan May 7 '13 at 11:44
    
@Rohan I see what you mean, having just thought about it ;) I was going for the index performance as well trying to combine the benefits of key orders for single column queries, but it does indeed NOT enforce the UNIQUE'ness completely, although it would limit duplication to at most two, not that that makes a difference. ;) –  oucil May 7 '13 at 15:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.