studio - restriccion unique sql server

Crear restricción única con columnas nulas

Question

Tengo una mesa con este diseño:

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

Quiero crear una restricción única similar a esto:

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

Sin embargo, esto permitirá múltiples filas con el mismo (UserId, RecipeId) , si MenuId IS NULL . Quiero permitir NULL en MenuId para almacenar un favorito que no tiene un menú asociado, pero solo quiero como máximo una de estas filas por usuario / par de recetas.

Las ideas que tengo hasta ahora son:

1. Use algún UUID codificado (como todos los ceros) en lugar de nulo.
   Sin embargo, MenuId tiene una restricción FK en los menús de cada usuario, por lo que tendría que crear un menú especial "nulo" para cada usuario, lo cual es una molestia.

2. Compruebe la existencia de una entrada nula utilizando un desencadenador en su lugar.
   Creo que es una molestia y me gusta evitar los desencadenantes siempre que sea posible. Además, no confío en ellos para garantizar que mis datos nunca estén en mal estado.

3. Solo olvídalo y comprueba la existencia previa de una entrada nula en el middle-ware o en una función de inserción, y no tienes esta restricción.

Estoy usando Postgres 9.0.

¿Hay algún método que esté pasando por alto?

Answer 1

Crea dos índices parciales :

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;

De esta forma, solo puede haber una combinación de (user_id, recipe_id) donde menu_id IS NULL, implementando efectivamente la restricción deseada.

Posibles inconvenientes: no puede tener una referencia de clave externa (user_id, menu_id, recipe_id) esta manera, no puede basar CLUSTER en un índice parcial, y las consultas sin una condición WHERE coincidente no pueden usar el índice parcial.

Parece poco probable que desee una referencia FK de tres columnas de ancho (utilice la columna PK en su lugar). Si necesita un índice completo , alternativamente puede descartar la condición WHERE de favo_3col_uni_idx y sus requisitos todavía se aplican.
El índice, que ahora comprende toda la tabla, se superpone con el otro y se agranda. Dependiendo de las consultas típicas y el porcentaje de valores NULL , esto puede o no ser útil. En situaciones extremas, incluso podría ayudar a mantener ambas versiones de favo_3col_uni_idx .

Aparte: Aconsejo no usar identificadores de casos mixtos en PostgreSQL .

Answer 2

Creo que hay un problema semántico aquí. En mi opinión, un usuario puede tener una (pero solo una ) receta favorita para preparar un menú específico. (El OP tiene el menú y la receta mezclados, si estoy equivocado: intercambie MenuId y RecipeId a continuación). Eso implica que {user, menu} debe ser una clave única en esta tabla. Y debería apuntar exactamente a una receta. Si el usuario no tiene una receta favorita para este menú específico, no debería haber ninguna fila para este par de claves {usuario, menú}. Además: la clave sustituta (FaVouRiteId) es superflua: las claves primarias compuestas son perfectamente válidas para las tablas de mapeo relacional.

Eso llevaría a la definición de tabla reducida:

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)
);

Answer 3

Puede almacenar favoritos sin menú asociado en una tabla separada:

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

Answer 4

Puede crear un índice único con unir en el MenuId:

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

Solo necesitarías elegir un UUID para COALESCE que nunca ocurrirá en la "vida real". Probablemente nunca verías un UUID de cero en la vida real, pero podrías agregar una restricción CHECK si eres paranoico (y dado que realmente están dispuestos a ayudarte ...):

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