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.

Lets say I have a table with 3 fields:

  • id1
  • id2
  • mapTypeId

What I want is a primary/unique key that is any combination of id1 and id2. For example if I have this record already in the database:

  • 1, 2, 3

I should not be able to insert a record like:

  • 2, 1, 3

Right now I have id1 and id2 as my composite primary key however I am able to insert the data above, is there anyway to prevent this on the database level?

share|improve this question
4  
What do id1 and id2 represent? Sounds like they're the same entity. Why not always insert with the lower number as id1 (if it's possible to change the app) –  Phil Aug 7 '12 at 2:00
    
I don't think this is possible with MySQL as it does not support expression based indexes (which make this kind of requirement a piece of cake) –  a_horse_with_no_name Aug 7 '12 at 7:02
    
What you are asking for is a combination, but in reality the unique index stores (1,2,3) and (2,1,3) as permutations. –  RolandoMySQLDBA Aug 7 '12 at 14:48
add comment

3 Answers 3

Here is before trigger you can use to guarantee that id1 and id2 are inserted in min/max order

DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;

Here is some Sample Data:

DROP DATABASE IF EXISTS ryanzec;
CREATE DATABASE ryanzec;
USE ryanzec
CREATE TABLE mytable
(
    id1 INT NOT NULL,
    id2 INT NOT NULL,
    mapTypeId INT NOT NULL,
    primary key (id1,id2)
);
DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;
INSERT INTO mytable VALUES (1,2,3);
INSERT INTO mytable VALUES (2,1,3);
INSERT INTO mytable VALUES (5,4,6);
INSERT INTO mytable VALUES (4,5,6);
SELECT * FROM mytable;

Here it is the Sample Data Loaded

mysql> DROP DATABASE IF EXISTS ryanzec;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE ryanzec;
Query OK, 1 row affected (0.00 sec)

mysql> USE ryanzec
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id1 INT NOT NULL,
    ->     id2 INT NOT NULL,
    ->     mapTypeId INT NOT NULL,
    ->     primary key (id1,id2)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
    -> FOR EACH ROW
    -> BEGIN
    ->     DECLARE sum INT;
    ->     SET sum = NEW.id1 + NEW.id2;
    ->     SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    ->     SET NEW.id2 = sum - NEW.id1;
    -> END $$
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;

Watch What Happens When I Insert the Rows:

mysql> INSERT INTO mytable VALUES (1,2,3);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO mytable VALUES (2,1,3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
mysql> INSERT INTO mytable VALUES (5,4,6);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO mytable VALUES (4,5,6);
ERROR 1062 (23000): Duplicate entry '4-5' for key 'PRIMARY'
mysql> SELECT * FROM mytable;
+-----+-----+-----------+
| id1 | id2 | mapTypeId |
+-----+-----+-----------+
|   1 |   2 |         3 |
|   4 |   5 |         6 |
+-----+-----+-----------+
2 rows in set (0.00 sec)

mysql>

Now, no matter what order id1 and id2 are entered, id1 will always be less than or equal to id2

This may not be contextually what you want because id1 and id2 are unique identifiers.

share|improve this answer
add comment

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), through the VIRTUAL columns feature, this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne INT AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo INT AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.


This is another idea (untested, not really sure if it will work), to have one less column and no persistent columns:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, diff INT UNSIGNED AS ( id2 - id1 ) VIRTUAL 
         --- a computed column that will hopefully throw an error if id1 > id2
, PRIMARY KEY (id1, id2)
, ...                                   --- other FOREIGN KEY constraints, etc 
) ;
share|improve this answer
    
Interesting. Are computed columns the only way to create an "expression based index" in MariaDB? Seems a bit clumsy compared to create unique index on tablex (least(id1,id2), greates(id1,id2)). –  a_horse_with_no_name Aug 7 '12 at 8:48
    
@a_horse_with_no_name: From what I know, yes. –  ypercube Aug 7 '12 at 8:50
    
@a_horse_with_no_name: I have another idea, which may be better (if it works). –  ypercube Aug 7 '12 at 11:06
    
Nice one. But I'd probably use abs(id2 - id1) rather than relying on some type casting imposed by the unsigned attribute –  a_horse_with_no_name Aug 7 '12 at 11:21
1  
Ah, I see. I somehow assumed you would put a unique index on the virtual column - don't know why because that of course wouldn' work. –  a_horse_with_no_name Aug 7 '12 at 12:36
show 1 more comment

Yes you can but not with an index. You have to remember that the values [2,1,3] are a unique value even if the value [1,2,3] already exists. However you have 2 options available to you:

  • You could enforce this logic using a trigger to check that the value of id1 does not exist in id2 and vice-versa.

  • You could perform the check inside the stored procedure or business logic that inserts the data.(Recommended)

I hope this helps.

EDIT: As per the comment below MySQL does not support CHECK constraints, removed suggestion in the list above.

share|improve this answer
    
CHECK constraints are not supported in MySQL. If they were, a simple CHECK id1 < id2 would suffice. –  ypercube Aug 7 '12 at 7:02
    
Thanks, as soon as I pressed submit the same thing dawned on me! :) –  Mr.Brownstone Aug 7 '12 at 7:07
add comment

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.