0

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.

user1070300
  • 836
  • 9
  • 15
  • You should be able to do a CHECK CONSTRAINT on the table definition that `key1 < key2` – Thilo Dec 20 '16 at 00:22
  • Or maybe not: "CHECK constraints are parsed but ignored": http://stackoverflow.com/a/2115641/14955 – Thilo Dec 20 '16 at 00:24
  • The only type of constraints MySQL currently implements are unique keys and foreign keys. – Barmar Dec 20 '16 at 01:30

1 Answers1

2

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.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • This. You're standing at the precipice of the cliff that is MySQL's poor implementation of ANSI SQL features. Triggers and transactions are two of the big ones that, while implemented in MySQL, do not behave in a manner consistent with most other RDBMSes. If you're committed to MySQL at this point I would suggest handling this case in the application code. – Sammitch Dec 20 '16 at 01:47
  • I'd like to accept this answer as the best one can do, but it seems to need a little editing before it will work as advertised. If I understand the concept properly, it seems as though the second part should exchange the keys if NEW.key1 > NEW.key2, rather than if the key pair already exists (since unless I'm confused the latter would guarantee an extra record for the same pair, rather than preventing it). Also the third to last line should be NEW.key2 = @tmp, if the exchange is going to work the way I think it should. – user1070300 Dec 20 '16 at 16:39
  • perhapsp a slight misunderstanding of the question. You are saying if k1,k2 appears k2, k1 should not appear in the table? – e4c5 Dec 20 '16 at 23:54
  • Exactly: any pair of records in the first table should be uniquely identified by a single record in the second table. Apologies for the lack of clarity. I have adapted your answer into a trigger that works for me, so thanks for the help. – user1070300 Dec 21 '16 at 21:43