Edit Question - KnowledgeHub

Title Body

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 . 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. The ideas I have so far are: Use some hard-coded UUID (such as all zeros) instead of 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. 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. 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. Is there any method I'm overlooking?

Tags (comma-separated) Cancel