TheGPWorx
TheGPWorx

Reputation: 887

How to set UNIQUE constraint to multiple columns MySQL?

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.

Upvotes: 2

Views: 3761

Answers (1)

nbk
nbk

Reputation: 49373

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

Upvotes: 2

Related Questions