使用空列创建唯一约束

Question

使用空列创建唯一约束我有一个这种布局的表：CREATE TABLE Favorites(   FavoriteId uuid NOT NULL PRIMARY KEY,   UserId uuid NOT NULL,   RecipeId uuid NOT NULL,   MenuId uuid)我想创建一个类似于此的唯一约束：ALTER TABLE FavoritesADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);但是，这将允许多行具有相同的(UserId, RecipeId)if MenuId IS NULL。我想允许NULL在MenuId存储不具有关联菜单中的最爱，但我只希望每个用户/食谱对这些行中最多只有一个。我到目前为止的想法是：使用一些硬编码的UUID（例如全零）而不是null。但是，MenuId每个用户的菜单都有一个FK约束，所以我必须为每个用户创建一个特殊的“空”菜单，这是一个麻烦。使用触发器检查是否存在空条目。我认为这是一个麻烦，我喜欢尽可能避免触发器。另外，我不相信他们能保证我的数据永远不会处于不良状态。只需忘记它并检查中间件或插入函数中是否存在空条目，并且没有此约束。我正在使用Postgres 9.0。我有什么方法可以忽略吗？

Answer 1

您可以在MenuId上创建一个带有合并的唯一索引：

CREATE UNIQUE INDEXFavorites_UniqueFavorite ON Favorites(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);

你只需要为COALESCE选择一个永远不会出现在现实生活中的UUID。在现实生活中你可能永远不会看到零UUID，但如果你是偏执狂，你可以添加一个CHECK约束（因为它们真的是为了让你......）：

alter table Favoritesadd constraint check(MenuId <> '00000000-0000-0000-0000-000000000000')