How To Create A 'Two-Sided' Unique Index On Two Fields?

Question

How can I efficiently create a unique index on two fields in a table like this: create table t (a integer, b integer);

where any unique combination of two different numbers cannot appear more than once on the same row in the table.

In order words if a row exists such that a=1 and b=2, another row cannot exist where a=2 and b=1 or a=1 and b=2. In other words two numbers cannot appear together more than once in any order.

I have no idea what such a constraint is called, hence the 'two-sided unique index' name in the title.

Update: If I have a composite key on columns (a,b), and a row (1,2) exists in the database, it is possible to insert another row (2,1) without an error. What I'm looking for is a way to prevent the same pair of numbers from being used more than once in any order...

Answer 1

See Two foreign keys instead of primary

Answer 2

I think this can only be done using a FOR INSERT trigger (in combination with a unique constraint on the two columns). I'm not really fluent in MySql syntax (my T-SQL is better), so I guess the following will contain some errors:

Edit: Cleaned up the syntax so it works for MySQL. Also, note you'll probably want to put this as a BEFORE UPDATE trigger too (with a different name of course).

Also, this method relies on having a primary or otherwise unique key on the two fields (ie. this trigger only checks the reverse doesn't already exist.) There doesn't seem to be any way of throwing an error from a trigger, so I dare say this is as good as it gets.

CREATE TRIGGER tr_CheckDuplicates_insert
BEFORE INSERT ON t
FOR EACH ROW
BEGIN
    DECLARE rowCount INT;
    SELECT COUNT(*) INTO rowCount
                   FROM t
                   WHERE a = NEW.b AND b = NEW.a;

    IF rowCount > 0 THEN
        -- Oops, we need a temporary variable here. Oh well.
        -- Switch the values so that the key will cause the insert to fail.
        SET rowCount = NEW.a, NEW.a = NEW.b, NEW.b = rowCount;
    END IF;
END;

Answer 3

In Oracle you could use a function-based index like this:

create unique index mytab_idx on mytab (least(a,b), greatest(a,b));

I don't know mySQL, but maybe something similar is possible? For example, you could add 2 new columns leastab and greatestab to the table, with a trigger to maintain them with the values of least(a,b) and greatest(a,b) respectively, and then create a unique index on (leastab, greatestab).

Answer 4

How about controlling what goes into the table so that you always store the smallest number into the first column and the largest one in the second? As long as it 'means' the same thing of course. It's probably less expensive to do it before it even gets to the database.

If this is impossible, you could save the fields as is but have them duplicated in numerical order into two OTHER fields, on which you would create the primary key (pseudo code-ish) :

COLUMN A : 2
COLUMN B : 1

COLUMN A_PK : 1  ( if new.a < new.b then new.a else new.b )
COLUMN B_PK : 2  ( if new.b > new.a then new.b else new.a )

This could easily be done with a trigger (as in Ronald's response) or handled higher up, in the application.