CODEWITHSUNDEEP
mysqlsqlunique
Resh32
Resh32

Reputation: 6590

MySQL - Combinatory constraint for two columns

This is not the typical constraint on two columns.

Here is a table with foreign keys ref1, ref2:

connection_id | ref1_id  | ref2_id
1             |     1    |    2

I would like to allow:

connection_id | ref1_id  | ref2_id
1             |     1    |    2
2             |     1    |    3

but disallow these two:

(typical unique key on ref1,ref2 - this I know how to do)
connection_id | ref1_id  | ref2_id
1             |     1    |    2
2             |     1    |    2

but also!!: 

(this is the problem)
connection_id | ref1_id  | ref2_id
1             |     1    |    2
2             |     2    |    1

because I would like only one ref1-ref2 pair - for me the pair (ref1,ref2) or (1,2) is the same as (2,1) and should be disallowed by a unique key constraint. Is there a way to do that in MySQL?

I am sure that it was answered already, but when searching I always hit the typical unique constraint on two columns.

Upvotes: 2

Views: 595

Answers (3)

xception
xception

Reputation: 4287

Variant 1: Considering your datatype for ref1_id and ref2_id are within the limits of a integer data type and there exists an integer data type which can fit sizeof ref1_id + sizeof ref2_id in mysql.

Add a new calculated column to your table with the type which can fit it and calculate it as min(ref1_id, ref2_id) << (size_in_bits_of_the_biggest_of_the_types_of_ref1_id_and_ref2_id) + max(ref1_id, ref2_id) within a trigger before insert or update - this way the whole row will be rejected due to unique constraint violation.

Variant 2: Use this if variant 1 is not applicable as it will be slower.

Add a new calculated column to your table as varchar long enough to fit the text representations of concat(max(ref1_id), ' ', max(ref2_id)) - fake code, use max posible value here and calculate it as CONCAT(min(ref1_id, ref2_id), ' ', max(ref1_id, ref2_id)) within a trigger before insert or update - this way the whole row will be rejected due to unique constraint violation.

Upvotes: 1

vearutop
vearutop

Reputation: 4062

You need to use canonical order of data in INSERT/UPDATE queries (for example ref1_id always less than ref2_id) or custom check duplicates with ON BEFORE INSERT/UPDATE trigger.

This task can not be solved using constraints only.

Edit: There is no way to abort INSERT or UPDATE statement with trigger, so the whole solution is even worse :-)

DELIMITER ###

CREATE TRIGGER `after_up` 
  AFTER UPDATE ON `my_table`
FOR EACH ROW 
BEGIN
  DECLARE collision INT DEFAULT 0;
  SELECT 1  
    INTO collision
    FROM my_table 
    WHERE ref1_id = NEW.ref2_id AND ref2_id = NEW.ref1_id;
  IF collision
  THEN -- reverting update
    UPDATE my_table SET ref2_id = OLD.ref2_id, ref1_id = OLD.ref1_id WHERE connection_id = OLD.connection_id;
  END IF;
END

###

CREATE TRIGGER `after_in` 
  AFTER INSERT ON `my_table`
FOR EACH ROW 
BEGIN
  DECLARE collision INT DEFAULT 0;
  SELECT 1  
    INTO collision
    FROM my_table 
    WHERE ref1_id = NEW.ref2_id AND ref2_id = NEW.ref1_id;
  IF collision
  THEN -- deleting new row
    DELETE FROM my_table WHERE connection_id = NEW.connection_id;
  END IF;
END

###

delimiter ;

Edit 2: Just found hack to abort query in trigger (DROP TABLE nonexistent_table_name)

DELIMITER ###

CREATE TRIGGER `before_up` 
  BEFORE UPDATE ON `my_table`
FOR EACH ROW 
BEGIN
  DECLARE collision INT DEFAULT 0;
  SELECT 1  
    INTO collision
    FROM my_table 
    WHERE ref1_id = NEW.ref2_id AND ref2_id = NEW.ref1_id;
  IF collision
  THEN -- throwing error
     DROP TABLE __error_duplicate_detected;        
  END IF;
END

###

CREATE TRIGGER `before_in` 
  BEFORE INSERT ON `my_table`
FOR EACH ROW 
BEGIN
  DECLARE collision INT DEFAULT 0;
  SELECT 1  
    INTO collision
    FROM my_table 
    WHERE ref1_id = NEW.ref2_id AND ref2_id = NEW.ref1_id;
  IF collision
  THEN -- throwing error
     DROP TABLE __error_duplicate_detected;        
  END IF;
END

###

delimiter ;

Upvotes: 1

wildplasser
wildplasser

Reputation: 44250

DROP TABLE pair CASCADE;
CREATE TABLE pair
      ( pair_id SERIAL NOT NULL PRIMARY KEY
      , aaa INTEGER NOT NULL
      , bbb INTEGER NOT NULL
      , CONSTRAINT aaa_bbb UNIQUE (aaa, bbb)
    );

CREATE UNIQUE INDEX aaaXXXbbb ON pair ( LEAST(aaa, bbb), GREATEST(aaa, bbb) )
        ;
INSERT INTO pair(aaa,bbb) VALUES(1,1), (1,2),(2,2);

INSERT INTO pair(aaa,bbb) VALUES(2,1);

SELECT * FROM pair;

Result:

INSERT 0 3
ERROR:  duplicate key value violates unique constraint "aaaxxxbbb"
DETAIL:  Key ((LEAST(aaa, bbb)), (GREATEST(aaa, bbb)))=(1, 2) already exists.
 pair_id | aaa | bbb 
---------+-----+-----
       1 |   1 |   1
       2 |   1 |   2
       3 |   2 |   2
(3 rows)

I don't know if mysql allows and enforces constraints or indexes on expressions. Postgres does allow indexes on expressions, but unfortunately no constraints on expressions. If indexes are not possible the obvious tie-breaking constraint would of course be aaa >= bbb, like in @vearutob 's answer.

BTW: in this model, the constraint on (aaa,bbb) is redundant, because the condition is already covered by the index. (I would like to have them swapped: the constraint on {least,greatest} and the index on the bare columns, but postgres does not seem to allow this yet)

Upvotes: 1

Related Questions