表中的唯一组合 [英] Unique combination in a table
问题描述
我有两个表： 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;
还会加速查询以将首页图片检索为附带利益
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.
这篇关于表中的唯一组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
