Уникальное ограничение по нескольким столбцам PostgreSQL и значения NULL
У меня есть таблица вроде следующего:
создать таблицу my_table (
id int8 не null,
id_A int8 не null,
id_B int8 не null,
id_C int8 null,
ограничение pk_my_table первичный ключ (id),
ограничение u_constrainte unique (id_A, id_B, id_C)
);
И я хочу, чтобы (id_A, id_B, id_C) отличался в любой ситуации. Таким образом, следующие две вставки должны приводить к ошибке:
INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);
Но он не ведет себя так, как ожидалось, потому что согласно документации два значения NULL не сравниваются друг с другом, поэтому обе вставки проходят без ошибок.
Как я могу гарантировать свое уникальное ограничение, даже если id_C может быть NULL в этом случае?
Собственно, реальный вопрос: могу ли я гарантировать такую уникальность в «чистом sql» или мне нужно реализовать его на более высоком уровне (java в моем случае)?
3 ответа
Вы можете сделать это в чистом SQL . Создайте частичный уникальный индекс дополнительно к тому, который у вас есть:
CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;
Таким образом вы вводите для (a, b, c) в своей таблице:
(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)
Но ни один из них во второй раз.
Или используйте два частичные UNIQUE индексы и полный индекс (или ограничение). Лучшее решение зависит от деталей ваших требований. Для сравнения:
Asides
-
Не использовать для идентификаторы смешанных случаев без двойных кавычек в PostgreSQL.
-
Вы могли бы рассмотреть
serialв качестве первичного ключа илиIDENTITYв Postgres 10 или новее.
Итак:
CREATE TABLE my_table (
my_table_id bigint ПОЛУЧЕН ПО УМОЛЧАНИЮ КАК ИДЕНТИФИКАЦИЯ ПЕРВИЧНЫЙ КЛЮЧ - для pg 10+
- my_table_id bigserial PRIMARY KEY - для п. 9.6 или старше
, id_a int8 NOT NULL
, id_b int8 NOT NULL
, id_c int8
, CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);
Если вы не ожидаете более 2 миллиардов строк (> 2147483647) за время жизни вашей таблицы (включая отходы и удаленные строки), рассмотрите integer (4 байта) вместо bigint (8 байт).
У меня была та же проблема, и я нашел другой способ иметь уникальный NULL в таблице.
CREATE UNIQUE INDEX index_name ON table_name (COALESCE (foreign_key_field, -1))
В моем случае поле foreign_key_field является положительным целым числом и никогда не будет равно -1.
Итак, чтобы ответить на Manual Leduc, другим решением может быть
CREATE UNIQUE INDEX u_constrainte UNIQUE (COALESCE (id_a, -1), COALESCE (id_b, -1), COALESCE (id_c, -1))
Я предполагаю, что идентификаторы не будут равны -1.
В чем преимущество создания частичного индекса?
Если у вас нет предложения NOT NULL, id_a, id_b и id_c может быть NULL вместе только один раз.
С частичным индексом 3 поля могут быть NULL более одного раза.
Null может означать, что значение не известно для этой строки в данный момент, но будет добавлено, когда это будет известно в будущем (пример FinishDate для запуска Project ) или что для этой строки не может быть применено никакое значение (пример EscapeVelocity для черной дыры Star).
По моему мнению, обычно лучше нормализовать таблицы, исключив все Nulls.
В вашем случае вы хотите разрешить NULLs в своем столбце, но вам нужно разрешить только один NULL. Зачем? Какая связь между этими двумя таблицами?
Возможно, вы можете просто изменить столбец на NOT NULL и сохранить вместо NULL специальное значение (например, -1)), которое как известно, никогда не появляется. Это решит проблему ограничения единственности.