Doodlemeat February 2016

CONSTRAINT by checking value in other table

I have items, categories and a items_category table like this:

table items
+----+-------+
| id | title |
+----+-------+

table categories
+----+------+------+
| id | name | main |
+----+------+------+

table items_categories
+---------+-------------+
| item_id | category_id |
+---------+-------------+

I need to make a constraint so that an item can have ONLY ONE main category, but otherwise, any amount of main = false categories. My initial thought was to set UNIQUE(item_id, category_id) but that is just limiting an item to one category.

Then I thought about having another column on the items_categories table called main which is an exact duplicate of the main column referenced by the category_id column, like this:

table items_categories
+---------+-------------+------+
| item_id | category_id | main |
+---------+-------------+------+

UNIQUE(item_id, category_id, main)

But that is not 100% normalized data and I would want to avoid that if possible.

Answers


Jakub Kania February 2016

What you need is a partial index:

CREATE UNIQUE INDEX main_category_unique ON items_categories (item_id)
    WHERE main = TRUE;

Post Status

Asked in February 2016
Viewed 2,626 times
Voted 9
Answered 1 times

Search




Leave an answer