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

10/14 10:20

### 问题：

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

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


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

ALTER TABLE Favorites


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? 我有什么方法可以忽略的吗？

0
0 收藏

0 评论
0 收藏
0