Уникальное ограничение по нескольким столбцам 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 в моем случае)?

77 голосов | спросил Manuel Leduc 27 TueEurope/Moscow2011-12-27T13:10:21+04:00Europe/Moscow12bEurope/MoscowTue, 27 Dec 2011 13:10:21 +0400 2011, 13:10:21

3 ответа


77

Вы можете сделать это в чистом 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

Итак:

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 байт).

ответил Erwin Brandstetter 27 TueEurope/Moscow2011-12-27T14:51:07+04:00Europe/Moscow12bEurope/MoscowTue, 27 Dec 2011 14:51:07 +0400 2011, 14:51:07
10

У меня была та же проблема, и я нашел другой способ иметь уникальный 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 более одного раза.

ответил Luc M 18 Mayam12 2012, 00:57:27
7

Null может означать, что значение не известно для этой строки в данный момент, но будет добавлено, когда это будет известно в будущем (пример FinishDate для запуска Project ) или что для этой строки не может быть применено никакое значение (пример EscapeVelocity для черной дыры Star).

По моему мнению, обычно лучше нормализовать таблицы, исключив все Nulls.

В вашем случае вы хотите разрешить NULLs в своем столбце, но вам нужно разрешить только один NULL. Зачем? Какая связь между этими двумя таблицами?

Возможно, вы можете просто изменить столбец на NOT NULL и сохранить вместо NULL специальное значение (например, -1)), которое как известно, никогда не появляется. Это решит проблему ограничения единственности.

ответил ypercubeᵀᴹ 28 WedEurope/Moscow2011-12-28T04:09:52+04:00Europe/Moscow12bEurope/MoscowWed, 28 Dec 2011 04:09:52 +0400 2011, 04:09:52

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132