Postgresql: warunkowo unikalne ograniczenie


116

Chciałbym dodać ograniczenie, które wymusza unikalność kolumny tylko w części tabeli.

ALTER TABLE stop ADD CONSTRAINT myc UNIQUE (col_a) WHERE (col_b is null);

Powyższa WHEREczęść to myślenie życzeniowe.

Jakiś sposób to zrobić? A może powinienem wrócić do relacyjnej deski kreślarskiej?


2
Powszechnie robione. Zobacz „częściowy unikalny indeks”
Craig Ringer

11
@yvesonline nie, to zwykłe unikalne ograniczenie. Plakat chce częściowego, unikalnego ograniczenia.
Craig Ringer

Odpowiedzi:


186

PostgreSQL nie definiuje częściową (tj warunkowa) UNIQUEograniczający - jednak użytkownik może tworzyć częściową unikalny indeks . PostgreSQL używa unikalnych indeksów do implementacji unikalnych ograniczeń, więc efekt jest taki sam, po prostu nie zobaczysz ograniczenia wymienionego w information_schema.

CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);

Zobacz indeksy częściowe .


24
Wspaniały! Nieintuicyjne, że „ograniczenie” nie pojawia się jako ograniczenie, ale mimo to daje pożądany błądERROR: duplicate key value violates unique constraint "stop_myc"
EoghanM

7
Warto zauważyć, że nie pozwoli to na tworzenie odwołań do tego częściowo unikalnego pola.
ffflabs

11
Warto również zauważyć, że tego efektu indeksu nie można odroczyć. Jeśli musisz przeprowadzić zbiorcze aktualizacje, może to stanowić problem, ponieważ unikalność jest sprawdzana po każdym wierszu, a nie po instrukcji, jak w przypadku ograniczenia lub po transakcji, tak jak w przypadku odroczonego ograniczenia.
sage88

37

zostało już powiedziane, że PG nie definiuje częściowego (tj. warunkowego) ograniczenia UNIQUE. Dokumentacja mówi również, że preferowanym sposobem dodania unikalnego ograniczenia do tabeli są ADD CONSTRAINT unikalne indeksy

Preferowanym sposobem dodania unikatowego ograniczenia do tabeli jest ALTER TABLE ... ADD CONSTRAINT. Stosowanie indeksów do wymuszania unikatowych ograniczeń można uznać za szczegół implementacji, do którego nie należy uzyskiwać bezpośredniego dostępu. Należy jednak mieć świadomość, że nie ma potrzeby ręcznego tworzenia indeksów na unikalnych kolumnach; spowodowałoby to po prostu zduplikowanie automatycznie utworzonego indeksu.

Istnieje sposób na wdrożenie go za pomocą ograniczeń wykluczania (dziękuję @dukelion za to rozwiązanie)

W twoim przypadku tak będzie wyglądać

ALTER TABLE stop ADD CONSTRAINT myc EXCLUDE (col_a WITH =) WHERE (col_b IS null);

w tym podejściu nie używasz "using" do definiowania metody indeksowania, więc może to być bardzo wolne lub postgres tworzy domyślny indeks na tym? Ta metoda jest kanonicznym wyborem, ale nigdy nie jest lepszym wyborem! Myślę, że będziesz potrzebować klauzuli "using" z indeksem, aby ten wybór był lepszy.
Natan Medeiros

10
Chociaż wolniejsze, zaletą rozwiązania wykluczającego jest to, że można je odroczyć (i domyślnie odracza do końca instrukcji). W przeciwieństwie do tego, zaakceptowane unikalne rozwiązanie indeksu nie może zostać odroczone (i jest sprawdzane po każdej zmianie wiersza). Dlatego zbiorcza aktualizacja jest często niemożliwa, ponieważ kroki podczas aktualizacji naruszałyby unikalne ograniczenie, nawet jeśli nie zostałyby naruszone na końcu instrukcji aktualizacji atomowej.
sage88

1
Ta notatka została usunięta z dokumentacji w sierpniu 2015 r.
Raniz
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.