Crear restricción única con columnas nulas

Question

Crear restricción única con columnas nulas

Tengo una tabla 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 esta:

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 que NULL en MenuId almacene un favorito que no tenga 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:

Use algún UUID codificado (como todos los ceros) en lugar de null.
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 que es una molestia.
Compruebe la existencia de una entrada nula usando un disparador en su lugar.
Creo que esto 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.
Simplemente olvídalo y comprueba la existencia previa de una entrada nula en el middle-ware o en una función insert, y no tenga esta restricción.

Estoy usando Postgres 9.0.

¿hay algún método que estoy vistas?

169

database-design sql postgresql null referential-integrity

Author: Erwin Brandstetter, 2011-11-28

Source

4 answers

Puede crear un índice único con una fusión en el MenuID:

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

Solo necesitas elegir un UUID para la FUSIÓN que nunca ocurrirá en la "vida real". Probablemente nunca verás un UUID cero en la vida real, pero podrías agregar una restricción de VERIFICACIÓN si eres paranoico (y desde que realmente están fuera de ti...):

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

45

Author: mu is too short,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-27 23:17:15

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

2

Author: ypercubeᵀᴹ,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-27 21:29:51

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 menú y receta mezclados; si me equivoco: por favor intercambie MenuID y RecipeId abajo) Esto implica que {user, menu} debe ser una clave única en esta tabla. Y debe apuntar a exactamente una receta. Si el usuario no tiene una receta favorita para este menú específico no debe existir ninguna fila para este par de claves {user,menu}. También: el la clave sustituta (Favoriteid) es superflua: las claves primarias compuestas son perfectamente válidas para tablas de asignación relacional.

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

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

0

Author: wildplasser,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-27 23:22:50

score 270 · Accepted Answer

Crear 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 manera, solo puede haber una combinación de (user_id, recipe_id) donde menu_id ES NULL, implementando efectivamente la restricción deseada.

Posibles inconvenientes: no puede tener una clave foránea que haga referencia a (user_id, menu_id, recipe_id) de 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 (use el PK columna en su lugar). Si necesita un índice complete, alternativamente puede eliminar la condición WHERE de favo_3col_uni_idx y sus requisitos aún se aplican.
El índice, que ahora comprende toda la tabla, se superpone con la otra y se hace más grande. Dependiendo de las consultas típicas y el porcentaje de valores NULL, esto puede o no ser útil. En situaciones extremas podría incluso ayudar a mantener ambas versiones de favo_3col_uni_idx.

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