表中的唯一组合

本文介绍了表中的唯一组合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个表： House 和 Picture 。 图片表有一个列 homepage ，这是一个 bool 和 house_id 。这意味着房屋有图片，只有标记为首页的图片将出现在首页。

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.

问题：每个房子应该只有一张主页图片。或者：可以有（house_id，homepage）:( 1，False）但只有一个（house_id，homepage）： ）元组。我如何为PostgreSQL做这项工作？

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?

这样的情况有没有名字？这不是一个主键，当然，因为可以有许多（1，False）元组。

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.

解决方案对数据库的帮助。 奖励分数：如何在模型图层上对Django实现这一功能的解决方案将非常棒！

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

推荐答案

如果您想始终保证数据的完整性，它可以（而且应该）在DB级别上解决。有多种方法，部分 UNIQUE INDEX 可能是最简单的并且最有效。

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.

另一种方法是添加一个列 homepage_id 到表 house ，指向所选图片。自动，只能选择1张图像。您不需要 picture.homepage 。引用完整性可能有点棘手，因为两个方向的外键约束，但我有工作解决方案。

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.