Mysql Unique Index on Multiple Fields when FieldX = 1

Question

Scenario : Multiple product images, one main image.

Currently, I've got 2 tables to manage this. One for storing product images and another table for storing mainProductImageIds. In the table that stores a list of mainProductImageIds, I've got a unique index on prodid+isMain to enforce One Main Image Per Product Id.

My Question relates to the table below (if I was going to only use 1 table). How would I enforce 1 isMain=1 per prod? Is there any index setting that would error when I tried to set isMain=1 to image_id=2 since image=1 is already the main image? You can't put a unique_key on prod+isMain.

|image_id |prod|isMain
|---------|----|------
|0        |1   |0
|1        |1   |1
|2        |1   |0
|3        |2   |1
|4        |3   |0
|5        |3   |1

Would be simple with other DBMS (that support partial indexes), but I can't think of an easy solution in MySQL. — , Oct 22 '12 at 15:19
It should be possible to do this using a trigger on insert/update. — Terje D., Oct 22 '12 at 15:42

score 1 · Answer 1 · answered Oct 22 '12 at 15:42

You could try and do this with a TRIGGER.

CREATE TRIGGER check_unique BEFORE UPDATE ON `images`
    FOR EACH ROW
        SET NEW.isMain = IF(NEW.isMain = 0, 0,
            CASE WHEN ( SELECT MAX(isMain) FROM images AS q WHERE q.prod = NEW.prod ) = 1
            THEN NULL ELSE NEW.isMain END);

or, faster (especially if indexed)

CREATE TRIGGER check_unique BEFORE UPDATE ON `images`
    FOR EACH ROW
        SET NEW.isMain = IF(NEW.isMain = 0, 0,
           IF (EXISTS ( SELECT * FROM images AS q WHERE q.prod = NEW.prod AND q.isMain = 1), NULL, 1));

This will allow setting isMain to 0 always; if you want to set it to 1, then it will check that there are no other rows for the same product for which isMain is 1.

Example

In the example below, I cannot set isMain = 1 where prod=3 until I set isMain=0 on the other image which had isMain = 1.

CREATE TABLE images (
   image_id integer not null primary key auto_increment, 
   prod integer not null,     isMain integer not null );

INSERT INTO images VALUES (1, 1, 0), (2, 1, 1), (3, 2, 0), (4, 2, 1);

select * from images;
+----------+------+--------+
| image_id | prod | isMain |
+----------+------+--------+
|        1 |    1 |      0 |
|        2 |    1 |      1 |
|        3 |    2 |      0 |
|        4 |    2 |      1 |
+----------+------+--------+

CREATE TRIGGER check_unique BEFORE UPDATE
    ON `images` FOR EACH ROW
       SET NEW.isMain = IF(NEW.isMain = 0, 0, IF (EXISTS ( SELECT * FROM images AS q WHERE q.prod = NEW.prod AND q.isMain = 1), NULL, 1));

UPDATE images SET isMain = 1 WHERE image_id = 3;
ERROR 1048 (23000): Column 'isMain' cannot be null

UPDATE images SET isMain = 0 WHERE image_id = 4;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

UPDATE images SET isMain = 1 WHERE image_id = 3;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

You could extend this approach so that you can always set to 1 the isMain of any row... but the row that was isMain before for that product gets zeroed.

check out the solution I found to enforce it without triggers. Your update statements would apply to both solutions. — JustinP, Oct 22 '12 at 15:49
Yes, but I think the `NULL` trick it's less portable (it only works if there is *one* value you "don't want"), and less clean data-wise; you end up with two different data types, one `NULL`. That said, it *will* work, and it *will* be much faster! — LSerni, Oct 22 '12 at 15:52
well, in this case, i only need to worry about that 1 value.. isMain. And it being much faster is always a good thing. Thank you for being so helpful @Iserni ! — JustinP, Oct 22 '12 at 15:57

score 0 · Accepted Answer · edited May 23 '17 at 10:34

The answer, I learned, is really simple and has to do with how MySQL indexes NULL values. Over Here they talk about:

For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

so as long as my isMain field allow enforces one value and NULL (maybe an ENUM), I'm all set. The unique_index will only be enforces when isMain is set to a distinct value and not when it is set to NULL.

The Updated Table Looks like This:

|image_id |prod|isMain
|---------|----|------
|0        |1   |null
|1        |1   |1
|2        |1   |null
|3        |2   |1
|4        |3   |null
|5        |3   |1

Mysql Unique Index on Multiple Fields when FieldX = 1

2 Answers2

Example