Home Ask Login Register

Developers Planet

Your answer is one click away!

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.


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


Leave an answer

Quote of the day: live life