How to set UNIQUE constraint to multiple columns MySQL?

Question

I have a table

table location_category {
    id,
    location_id,
    category_id,
    is_primary
}

What I want is to set a UNIQUE constraint for the combination location_id and is_primary. I get that using this will make a multi column UNIQUE constraint

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`location_id`, `is_primary`);

But my concern is that we can have multiple categories for a location but only set 1 category as primary. For example:

| id | location_id | category_id | is_primary |
| 1  |      1      |      1      |      0     |
| 2  |      1      |      2      |      0     |
| 3  |      1      |      3      |      1     |
| 4  |      1      |      4      |      0     |

Will this violate the UNIQUE contraint? Since I have multiple instances of location_id = 1 and is_primary = 0?

Just trying to figure this out. Thank you for helping.

@Akina you should make that an answer, with some explanation. though a trigger may be a better answer for this class of problem. — ysth
– ysth, Commented Oct 15, 2020 at 18:58
@nbk looks to me like that would defeat the purpose of the unique index — ysth
– ysth, Commented Oct 15, 2020 at 18:59
@Akina so what data type should I set is_primary to? Can I still set it to tinyint? Can I update tinyint to accept NULL whenever I update another category to primary? — TheGPWorx
– TheGPWorx, Commented Oct 15, 2020 at 19:02

nbk · Accepted Answer · 2020-10-15 19:10:49Z

2

There is no need to change anything, UNIQUE allows multiple NULL values

CREATE TABLE `votes` (
  `id` INTEGER,
  `location_id` INTEGER,
  `category_id` INTEGER,
  `is_primary` INTEGER
);

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`location_id`, `is_primary`);

INSERT INTO `votes`
  (`id`, `location_id`, `category_id`, `is_primary`)
VALUES
  ('1', '1', '1', NULL),
  ('2', '1', '2', NULL),
  ('3', '1', '3', '1'),
  ('4', '1', '4', NULL);

SELECT * from `votes`

id | location_id | category_id | is_primary
-: | ----------: | ----------: | ---------:
 1 |           1 |           1 |       null
 2 |           1 |           2 |       null
 3 |           1 |           3 |          1
 4 |           1 |           4 |       null

db<>fiddle here

So you can only have one location with is primary 1

answered Oct 15, 2020 at 19:10

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TheGPWorx Over a year ago

I see. How about when I select another category as the primary, say I select category_id 1 as the primary, does MySQL allow updating INTEGER with NULL?

ysth Over a year ago

@Paul yes, but you have to set current primary category's is_primary to null before setting the new one to 1 (though it can be in the same update statement, e.g. update votes set is_primary=if(category_id=2,1,null) where location_id=1 order by is_primary desc)

ysth Over a year ago

though with all of this, you would be better off with a separate location_primary_category table with just a unique location_id and a category_id indicating the primary category for the location

Collectives™ on Stack Overflow

How to set UNIQUE constraint to multiple columns MySQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related