使用空列创建唯一约束 - Create unique constraint with null columns

10/14 10:20
阅读数 0

问题:

I have a table with this layout: 我有一个具有这种布局的表:

CREATE TABLE Favorites
(
  FavoriteId uuid NOT NULL PRIMARY KEY,
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  MenuId uuid
)

I want to create a unique constraint similar to this: 我想创建一个与此类似的唯一约束:

ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);

However, this will allow multiple rows with the same (UserId, RecipeId) , if MenuId IS NULL . 但是,如果MenuId IS NULL ,这将允许多行具有相同的(UserId, RecipeId) I want to allow NULL in MenuId to store a favorite that has no associated menu, but I only want at most one of these rows per user/recipe pair. 我想允许MenuId NULL存储没有关联菜单的收藏夹,但是我只希望每个用户/配方对最多包含这些行之一。

The ideas I have so far are: 到目前为止,我的想法是:

  1. Use some hard-coded UUID (such as all zeros) instead of null. 使用一些硬编码的UUID(例如全零)而不是null。
    However, MenuId has a FK constraint on each user's menus, so I'd then have to create a special "null" menu for every user which is a hassle. 但是, MenuId对每个用户的菜单都有FK约束,因此我不得不为每个用户创建一个特殊的“空”菜单,这很麻烦。

  2. Check for existence of a null entry using a trigger instead. 而是使用触发器检查是否存在空条目。
    I think this is a hassle and I like avoiding triggers wherever possible. 我认为这很麻烦,我喜欢尽可能避免触发事件。 Plus, I don't trust them to guarantee my data is never in a bad state. 另外,我不信任他们以确保我的数据永远不会处于错误状态。

  3. Just forget about it and check for the previous existence of a null entry in the middle-ware or in a insert function, and don't have this constraint. 只需忘记它,然后检查中间件或插入函数中以前是否存在空条目,并且没有此约束。

I'm using Postgres 9.0. 我正在使用Postgres 9.0。

Is there any method I'm overlooking? 我有什么方法可以忽略的吗?


解决方案:

参考一: https://en.stackoom.com/question/YmNA
参考二: https://stackoom.com/question/YmNA
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部