Criar restrição exclusiva com colunas nulas

Eu tenho uma tabela com este layout:

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

Eu quero criar uma restrição única semelhante a esta:

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

No entanto, isso permitirá várias linhas com o mesmo (UserId, RecipeId) , se MenuId IS NULL . Eu quero permitir NULL no MenuId para armazenar um favorito que não tem menu associado, mas eu só quero no máximo uma dessas linhas por par de usuário / receita.

As ideias que tenho até agora são:

  1. Use algum UUID codificado (como todos os zeros) em vez de nulo.
    No entanto, MenuId tem uma restrição FK nos menus de cada usuário, então eu teria que criar um menu especial “nulo” para cada usuário que é um incômodo.

  2. Verifique a existência de uma input nula usando um gatilho.
    Eu acho que isso é um aborrecimento e eu gosto de evitar gatilhos sempre que possível. Além disso, não confio neles para garantir que meus dados nunca estejam em mau estado.

  3. Apenas esqueça e verifique a existência anterior de uma input nula no middleware ou em uma function de inserção, e não tenha essa restrição.

Estou usando o Postgres 9.0.

Existe algum método que eu estou negligenciando?

Crie dois índices parciais :

 CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id) WHERE menu_id IS NOT NULL; CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id) WHERE menu_id IS NULL; 

Desta forma, só pode haver uma combinação de (user_id, recipe_id) onde menu_id IS NULL, efetivamente implementando a restrição desejada.

Possíveis desvantagens: você não pode ter uma referência de chave estrangeira (user_id, menu_id, recipe_id) dessa forma, não é possível basear CLUSTER em um índice parcial e consultas sem uma condição WHERE correspondente não pode usar o índice parcial.

Parece improvável que você queira uma referência FK com três colunas de largura (use a coluna PK). Se você precisar de um índice completo , você pode, alternativamente, descartar a condição WHERE de favo_3col_uni_idx e seus requisitos ainda serão aplicados.
O índice, agora composto pela tabela inteira, sobrepõe-se ao outro e fica maior. Dependendo das consultas típicas e da porcentagem de valores NULL , isso pode ou não ser útil. Em situações extremas, pode até ajudar a manter as duas versões do favo_3col_uni_idx .

Além disso: Eu aconselho não usar identificadores de casos mistos no PostgreSQL .

Você pode criar um índice exclusivo com uma união no MenuId:

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

Você só precisa escolher um UUID para a COALESCE que nunca ocorrerá na “vida real”. Você provavelmente nunca verá um zero UUID na vida real, mas você poderia adicionar uma restrição CHECK se você é paranóico (e já que eles realmente querem te pegar …):

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

Você pode armazenar favoritos sem menu associado em uma tabela separada:

 CREATE TABLE FavoriteWithoutMenu ( FavoriteWithoutMenuId uuid NOT NULL, --Primary key UserId uuid NOT NULL, RecipeId uuid NOT NULL, UNIQUE KEY (UserId, RecipeId) ) 

Eu acho que há um problema semântico aqui. Na minha opinião, um usuário pode ter uma (mas apenas uma ) receita favorita para preparar um menu específico. (O OP tem menu e receita misturados; se eu estiver errado: por favor troque MenuId e RecipeId abaixo) Isso significa que {user, menu} deve ser uma chave única nesta tabela. E deve apontar exatamente para uma receita. Se o usuário não tiver nenhuma receita favorita para este menu específico, não deverá existir nenhuma linha para este par de chaves {usuário, menu}. Além disso: a chave substituta (FaVouRiteId) é supérflua: as chaves primárias compostas são perfeitamente válidas para tabelas de mapeamento relacional.

Isso levaria à definição de tabela reduzida:

 CREATE TABLE Favorites ( UserId uuid NOT NULL REFERENCES users(id) , MenuId uuid NOT NULL REFERENCES menus(id) , RecipeId uuid NOT NULL REFERENCES recipes(id) , PRIMARY KEY (UserId, MenuId) );