使用空列创建唯一约束

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存储不具有关联菜单中的最爱，但我只希望每个用户/食谱对这些行中最多只有一个。

我到目前为止的想法是：

1. 使用一些硬编码的UUID（例如全零）而不是null。
   但是，MenuId每个用户的菜单都有一个FK约束，所以我必须为每个用户创建一个特殊的“空”菜单，这是一个麻烦。

2. 使用触发器检查是否存在空条目。
   我认为这是一个麻烦，我喜欢尽可能避免触发器。另外，我不相信他们能保证我的数据永远不会处于不良状态。

3. 只需忘记它并检查中间件或插入函数中是否存在空条目，并且没有此约束。

我正在使用Postgres 9.0。

我有什么方法可以忽略吗？

Answer 1

您可以在MenuId上创建一个带有合并的唯一索引：CREATE&nbsp;UNIQUE&nbsp;INDEXFavorites_UniqueFavorite&nbsp;ON&nbsp;Favorites(UserId,&nbsp;COALESCE(MenuId,&nbsp;'00000000-0000-0000-0000-000000000000'),&nbsp;RecipeId);你只需要为COALESCE选择一个永远不会出现在现实生活中的UUID。在现实生活中你可能永远不会看到零UUID，但如果你是偏执狂，你可以添加一个CHECK约束（因为它们真的是为了让你......）：alter&nbsp;table&nbsp;Favoritesadd&nbsp;constraint&nbsp;check(MenuId&nbsp;<>&nbsp;'00000000-0000-0000-0000-000000000000')