Wielokolumnowe ograniczenie PostgreSQL i wartości NULL

Mam tabelę podobną do następującej:

create table my_table (
    id   int8 not null,
    id_A int8 not null,
    id_B int8 not null,
    id_C int8 null,
    constraint pk_my_table primary key (id),
    constraint u_constrainte unique (id_A, id_B, id_C)
);

I chcę (id_A, id_B, id_C)się wyróżniać w każdej sytuacji. Tak więc następujące dwie wstawki muszą powodować błąd:

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);

Ale nie zachowuje się zgodnie z oczekiwaniami, ponieważ zgodnie z dokumentacją dwie NULLwartości nie są ze sobą porównywane, więc obie wstawki przebiegają bezbłędnie.

Jak mogę zagwarantować moje wyjątkowe ograniczenie, nawet jeśli tak id_Cjest NULLw tym przypadku? Właściwie prawdziwe pytanie brzmi: czy mogę zagwarantować tego rodzaju wyjątkowość w „czystym sql”, czy też muszę go wdrożyć na wyższym poziomie (w moim przypadku java)?

Możesz to zrobić w czystym SQL . Utwórz częściowy unikalny indeks oprócz tego, który masz:

CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;

W ten sposób możesz wpisać (a, b, c)w tabeli:

(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)

Ale żaden z nich po raz drugi.

Lub użyj dwóchUNIQUE indeksów częściowych i bez pełnego indeksu (lub ograniczenia). Najlepsze rozwiązanie zależy od szczegółów twoich wymagań. Porównać:

• Utwórz unikalne ograniczenie z pustymi kolumnami

Chociaż jest to eleganckie i wydajne rozwiązanie dla pojedynczej zerowalnej kolumny w UNIQUEindeksie, szybko wymyka się spod kontroli, by uzyskać więcej. Omówienie tego - i jak korzystać z UPSERT z częściowymi indeksami:

• Problem PostgreSQL UPSERT z wartościami NULL

Na bok

Nie ma zastosowania w przypadku identyfikatorów mieszanych bez podwójnych cudzysłowów w PostgreSQL.

Państwo może rozważyć serialkolumnę jako klucz podstawowy lub IDENTITYkolumny w PostgreSQL 10 lub nowszej. Związane z:

• Kolumna tabeli automatycznego przyrostu

Więc:

CREATE TABLE my_table (
   my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- for pg 10+
-- my_table_id bigserial PRIMARY KEY  -- for pg 9.6 or older
 , id_a int8 NOT NULL
 , id_b int8 NOT NULL
 , id_c int8
 , CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);

Jeśli nie spodziewasz się więcej niż 2 miliardów wierszy (> 2147483647) przez cały okres użytkowania tabeli (w tym odpadów i wierszy usuniętych), rozważ integer(4 bajty) zamiast bigint(8 bajtów).

Miałem ten sam problem i znalazłem inny sposób na umieszczenie w tabeli unikatowej wartości NULL.

CREATE UNIQUE INDEX index_name ON table_name( COALESCE( foreign_key_field, -1) )

W moim przypadku pole foreign_key_fieldjest dodatnią liczbą całkowitą i nigdy nie będzie wynosić -1.

Tak więc, aby odpowiedzieć na Manual Leduc, mogłoby być inne rozwiązanie

CREATE UNIQUE INDEX  u_constrainte (COALESCE(id_a, -1), COALESCE(id_b,-1),COALESCE(id_c, -1) )

Zakładam, że identyfikatory nie będą wynosić -1.

Jaka jest zaleta tworzenia częściowego indeksu?
W przypadku, gdy nie masz klauzuli NOT NULL id_a, id_bi id_cmożesz być NULL razem tylko raz.
Przy częściowym indeksie 3 pola mogą być NULL więcej niż jeden raz.

Wartość Null może oznaczać, że wartość nie jest w tej chwili znana dla tego wiersza, ale zostanie dodana, jeśli będzie znana, w przyszłości (na przykład FinishDatedla biegu Project) lub że dla tego wiersza nie będzie można zastosować żadnej wartości (na przykład EscapeVelocitydla czarnej dziury Star).

Moim zdaniem zwykle lepiej jest znormalizować tabele, eliminując wszystkie wartości Null.

W twoim przypadku chcesz zezwolić NULLsw kolumnie, ale chcesz, NULLaby dozwolona była tylko jedna . Dlaczego? Jaki to związek między dwiema tabelami?

Być może możesz po prostu zmienić kolumnę NOT NULLi zapisać zamiast NULLniej specjalną wartość (jak -1), o której wiadomo, że nigdy się nie pojawia. To rozwiąże problem ograniczenia wyjątkowości (ale może mieć inne potencjalnie niepożądane skutki uboczne. Na przykład użycie -1oznaczenia „nieznane / nie dotyczy” spowoduje wypaczenie dowolnej sumy lub średnich obliczeń w kolumnie. Lub wszystkie takie obliczenia będą musiały uwzględnij wartość specjalną i zignoruj ​​ją).