Does MySQL have any key constraints to handle unique pairings?

Question

Given a table of records, each with a unique ID, I want another table that can store unique pairings of records in the first table.

The second table clearly needs a composite unique key, but it seems to need an extra constraint too: that one of the keys always be less than the other one. That way the second table can never pair any record in the first table with itself, and can also avoid creating two separate pairings for the same two records, just because the keys appear the other way around.

Given the second table "pairing", containing the following values:

 key1 key2
  1    2
  1    3
  2    3

I would like the following behavior:

INSERT pairing SET key1=2, key2=1;
=> Duplicate key error

INSERT pairing SET key1=2, key2=2;
=> Invalid key error


INSERT pairing SET key1=4, key2=3;
[could give:]
=> Invalid key error

[but in fact ideally it would insert a record with:]
=> key1  key2
    3     4

and as in this final case, an equally ambidextrous SELECT query would also be welcome:

SELECT * FROM pairing WHERE key1=4 AND key2=3;
=> key1  key2
    3     4

However, I won't be surprised to find that MySQL does not offer this kind of functionality!

Nonetheless, this seems as though it would be a common requirement, so I wonder if anyone can show me any techniques to get the results I would like, with as little dependence on external programming as possible.

Answer 1

Unfortunately CHECK Constraints are not available in mysql as yet so the only solution seems to be to us a trigger.

CREATE TRIGGER pairing_check
BEFORE INSERT ON pairing
FOR EACH ROW
BEGIN
    IF NEW.key1 = NEW.key2 THEN
        SIGNAL 'Two keys should be different'set @
    END IF 
    /* We can't try an insert and then capture the exception because
"A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger." */
    IF (SELECT COUNT(*) FROM paring where key1=NEW.key1 and key2=NEW.key2) == 1 THEN
        SET @tmp = NEW.key1;
        NEW.key1 = NEW.key2;
        NEW.key2 = @tmp;
    END IF;
END

Even then you will be hard pressed to fill the full requirement because of mysql triggers have lots of restrictions on them.