1

I have two tables: House and Picture. The Picture table has a column homepage, which is a bool, and a house_id. What this means is that houses have pictures, and only the pictures flagged as homepage will appear in the home page.

Problem: there should be only one home page picture per house. Or: there can be as many (house_id, homepage):(1, False) but only one (house_id, homepage):(1, True) tuples. How can I make that work for PostgreSQL?

Is there a name for a situation like that? That's not a Primary Key, of course, since there can be many (1, False) tuples.

The solution on the database helps. Bonus points: A solution on how to implement that on Django, on the model layer, would be great!

jpmelos
  • 3,283
  • 3
  • 23
  • 30

1 Answers1

1

It can (and should) be solved on the DB level if you want to guarantee data integrity at all times. There are various ways, a partial UNIQUE INDEX probably being the simplest and most effective.

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

->sqlfiddle

Will also speed up queries to retrieve the homepage pictures as collateral benefit.

Change schema

A different approach would be to add a column homepage_id to the table house, pointing to a selected picture. Automatically, only 1 picture can be selected. You wouldn't need picture.homepage any more. Referential integrity might be a bit tricky because of foreign key constraints in both directions, but I have working solutions like that.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228