Составной ключ уникального индекса PostgreSQL

Мне нужно создать ограничение на таблицу на основе четырех столбцов, причем два из них взаимоисключающие, я имею в виду, если один NULL, то другой нет.

Идеальный сценарий был бы таким:

ALTER TABLE ONLY t_users_prizes
ADD CONSTRAINT t_user_prize_test_circuit_key
UNIQUE (id_event||':'||id_circuit, id_prize, id_user);

Но Postgres не допускает такой конкатенации.

Как я могу реализовать это ограничение?


postgresql null sql indexing unique-index
person Carlos Delgado    schedule 19.01.2013    source источник
comment
Вы бы позволили (1, NULL, 3, 4) и (NULL, 1, 3, 4) сосуществовать?   -  person Erwin Brandstetter    schedule 19.01.2013
comment
Насколько я понимаю, вопреки подразумеваемым ответам @Erwin и Catcall, оба взаимоисключающих столбца могут быть не нулевыми. Какова правильная интерпретация?   -  person Clodoaldo Neto    schedule 19.01.2013
comment
Оба наших ответа запрещают пару NULL.   -  person Mike Sherrill 'Cat Recall'    schedule 19.01.2013

Ответы (1)


arrow_upward
8
arrow_downward

Ограничение @Catcall CHECK требует, чтобы ровно одно из (a, b) должно быть NULL.

Но значения NULL не нарушают ограничение UNIQUE — два NULL никогда не считаются равными! Поэтому простое ограничение UNIQUE не работает.

Вы можете сделать это с помощью двух partial UNIQUE индексы:

CREATE TEMP TABLE foo (
   a int
  ,b int
  ,c int NOT NULL
  ,d int NOT NULL
  ,CHECK ((a IS NOT NULL AND b IS NULL) OR (b IS NOT NULL AND a IS NULL))
);

CREATE UNIQUE INDEX foo_acd_idx ON foo(a,c,d)
WHERE b is NULL;

CREATE UNIQUE INDEX foo_bcd_idx ON foo(b,c,d)
WHERE a is NULL;

INSERT INTO foo VALUES (NULL,2,3,4);
INSERT INTO foo VALUES (NULL,2,3,4);  -- error!

Я объявил c и d как NOT NULL, чтобы предотвратить дальнейшие осложнения.

Подробнее в этом тесно связанном ответе.

Если вы также хотите запретить (1, NULL, 3, 4) и (NULL, 1, 3, 4) (см. мой комментарий), вместо этого вы можете использовать один индекс с COALESCE:

CREATE UNIQUE INDEX foo_xcd_idx ON foo(COALESCE(a,b),c,d);
person Erwin Brandstetter    schedule 19.01.2013
comment
очень хороший ответ! это решает проблему и побуждает меня делать новые вещи в postgres. Спасибо! (Следующая задача: секционированная таблица!) - person Carlos Delgado; 22.01.2013