Reputation: 887
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
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