Unique combination in the table

Question

I have two tables: Houseand Picture. The table Picturehas a column homepage, which is booland a house_id. This means that there are images in the houses, and only photos marked as homepagewill be displayed on the main page.

Problem: There should be only one home image in the house. Or: there may be as many (house_id, homepage):(1, False), but only one (house_id, homepage):(1, True)tuple. How can I do this job for PostgreSQL?

Is there a name for this situation? Of course, this is not a primary key, as there can be many tuples (1, False).

The solution in the database helps. Bonus Points: Deciding how to implement this on Django, at the model level, would be great!

Answer 1

It can (and should) be solved at the database level if you want to guarantee data integrity at any time. There are various ways: partial is UNIQUE INDEX probably the simplest and most effective.

CREATE UNIQUE INDEX picture_homepage_uni ON picture (house_id) WHERE homepage;

→ sqlfiddle

It will also speed up requests for home page images as collateral.

Change Scheme

, homepage_id house, . 1 . picture.homepage. - , .