Ograniczenie - jeden wiersz boolowski jest prawdziwy, wszystkie pozostałe wiersze fałszywe

Mam kolumnę: standard BOOLEAN NOT NULL

Chciałbym egzekwować jeden wiersz Prawda, a wszystkie inne Fałsz. Nie ma FK ani nic innego w zależności od tego ograniczenia. Wiem, że mogę to zrobić przy pomocy PLpgSQL, ale wydaje się to młotem. Wolałbym coś w rodzaju ograniczenia CHECKlub UNIQUEograniczenia. Im prościej, tym lepiej.

Jeden wiersz musi być prawdziwy, nie wszystkie mogą być fałszywe (więc pierwszy wstawiony wiersz musiałby być prawdziwy).

Wiersz będzie musiał zostać zaktualizowany, co oznacza, że ​​muszę czekać, aby sprawdzić ograniczenia, aż aktualizacje zostaną wykonane, ponieważ wszystkie wiersze mogą być ustawione na False pierwszy, a jeden wiersz True później.

Istnieje FK pomiędzy products.tax_rate_idi tax_rate.id, ale nie ma to nic wspólnego z domyślną lub standardową stawką podatkową, którą użytkownik może wybrać, aby ułatwić tworzenie nowych produktów.

PostgreSQL 9.5, jeśli ma to znaczenie.

tło

Tabela to stawka podatku. Jedna ze stawek podatkowych jest domyślna ( standardponieważ domyślnie jest to polecenie Postgres). Po dodaniu nowego produktu do produktu stosuje się standardową stawkę podatku. Jeśli nie standard, baza danych musi zgadnąć lub wykonać wszelkiego rodzaju niepotrzebne kontrole. Pomyślałem, że najprostszym rozwiązaniem jest upewnienie się, że istnieje standard.

Przez „domyślny” powyżej rozumiem warstwę prezentacji (UI). Istnieje możliwość zmiany domyślnej stawki podatkowej przez użytkownika. Muszę albo dodać dodatkowe kontrole, aby upewnić się, że GUI / użytkownik nie próbuje ustawić tax_rate_id na NULL, lub po prostu ustawić domyślną stawkę podatku.

Wariant 1

Ponieważ wszystko, czego potrzebujesz, to jedna kolumna z standard = true, ustaw standard na NULL we wszystkich innych wierszach. Następnie działa proste UNIQUEograniczenie, ponieważ wartości NULL nie naruszają go:

CREATE TABLE taxrate (
   taxrate int PRIMARY KEY
 , standard bool DEFAULT true
 , CONSTRAINT standard_true_or_null CHECK (standard) -- yes, that's the whole constraint
 , CONSTRAINT standard_only_1_true UNIQUE (standard)
);

DEFAULTjest opcjonalnym przypomnieniem, że pierwszy wprowadzony wiersz powinien stać się domyślny. Niczego to nie wymusza . Chociaż nie możesz ustawić więcej niż jednego wiersza standard = true, nadal możesz ustawić wszystkie wiersze na NULL. Nie ma czystego sposobu, aby temu zapobiec, stosując jedynie ograniczenia w jednej tabeli. CHECKograniczenia nie uwzględniają innych wierszy (bez brudnych lew).

Związane z:

• Ogranicz dwie określone wartości kolumn z istniejącej w tym samym czasie

• Dodaj ograniczenie, aby kolumna była unikalna dla grupy wierszy

Aktualizować:

BEGIN;
UPDATE taxrate SET standard = NULL WHERE standard;
UPDATE taxrate SET standard = TRUE WHERE taxrate = 2;
COMMIT;

Aby zezwolić na polecenie takie jak (gdzie ograniczenie jest spełnione tylko na końcu instrukcji):

WITH kingdead AS (
   UPDATE taxrate
   SET standard = NULL
   WHERE standard
   )
UPDATE taxrate
SET standard = TRUE
WHERE taxrate = 1;

.. UNIQUEograniczenie musiałoby być DEFERRABLE. Widzieć:

• Określone ograniczenie PONIŻSZE NATYCHMIASTOWE NATYCHMIASTOWE POWYŻSZE SĄ PONIESIONE?

dbfiddle tutaj

Wariant 2

Miej drugi stolik z jednym rzędem, taki jak:

Utwórz to jako superużytkownik:

CREATE TABLE taxrate (
   taxrate int PRIMARY KEY
);

CREATE TABLE taxrate_standard (
   taxrate int PRIMARY KEY REFERENCES taxrate
);

CREATE UNIQUE INDEX taxrate_standard_singleton ON taxrate_standard ((true));  -- singleton

REVOKE DELETE ON TABLE taxrate_standard FROM public;  -- can't delete

INSERT INTO taxrate (taxrate) VALUES (42);
INSERT INTO taxrate_standard (taxrate) VALUES (42);

Teraz zawsze jest jeden wiersz wskazujący na standard (w tym prostym przypadku również bezpośrednio reprezentujący stawkę standardową). Tylko superużytkownik może to złamać. Możesz także tego zabronić za pomocą spustu BEFORE DELETE.

dbfiddle tutaj

Związane z:

• Ograniczenie do wymuszania „co najmniej jednego” lub „dokładnie jednego” w bazie danych

Możesz dodać a, VIEWaby zobaczyć to samo, co w wariancie 1 :

CREATE VIEW taxrate_combined AS
SELECT t.*, (ts.taxrate = t.taxrate) AS standard
FROM   taxrate t
LEFT   JOIN taxrate_standard ts USING (taxrate);

W zapytaniach, w których wszystko czego potrzebujesz to standardowa stawka, użyj (tylko) taxrate_standard.taxratebezpośrednio.

Później dodałeś:

Istnieje FK pomiędzy products.tax_rate_iditax_rate.id

A realizacja biedaka wariantu 2 byłby po prostu dodać wiersz do products(lub jakiegokolwiek podobnego tabela) skierowaną do standardowej stawki podatkowej; fikcyjny produkt, który możesz nazwać „standardową stawką podatkową” - jeśli konfiguracja na to pozwala.

Ograniczenia FK wymuszają integralność referencyjną. Aby go ukończyć, wyegzekwuj tax_rate_id IS NOT NULLdla wiersza (jeśli nie jest tak w przypadku całej kolumny). I nie zezwalaj na jego usunięcie. Oba można włączyć w wyzwalacze. Bez dodatkowego stołu, ale mniej elegancki i nie tak niezawodny.

Możesz użyć filtrowanego indeksu

create table test
(
    id int primary key,
    foo bool
);

CREATE UNIQUE INDEX only_one_row_with_column_true_uix 
    ON test (foo) WHERE (foo);  --> where foo is true

insert into test values (1, false);
insert into test values (2, true);
insert into test values (3, false);
insert into test values (4, false);
insert into test values (5, true);

BŁĄD: zduplikowana wartość klucza narusza unikalne ograniczenie „only_one_row_with_column_true_uix”
SZCZEGÓŁY: Klucz (foo) = (t) już istnieje.

dbfiddle tutaj

Ale jak powiedziałeś, pierwszy wiersz musi być prawdziwy, wtedy możesz użyć ograniczenia CHECK, ale nawet używając funkcji możesz usunąć pierwszy wiersz później.

create function check_one_true(new_foo bool)
returns int as
$$
begin
    return 
    (
        select count(*) + (case new_foo when true then 1 else 0 end)
        from test 
        where foo = true
    );
end
$$
language plpgsql stable;

alter table test 
    add constraint ck_one_true check(check_one_true(foo) = 1); 

insert into test values (1, true);
insert into test values (2, false);
insert into test values (3, false);
insert into test values (4, false);

insert into test values (5, true);

BŁĄD: nowy wiersz dla relacji „test” narusza ograniczenie sprawdzania „ck_one_true”
SZCZEGÓŁY: Niepowodzenie wiersza zawiera (5, t).

select * from test;

id | bla
-: | : -
 1 | t  
 2 | fa  
 3 | fa  
 4 | fa  

delete from test where id = 1;

dbfiddle tutaj

Możesz to rozwiązać, dodając wyzwalacz PRZED USUNIĘCIEM, aby upewnić się, że pierwszy wiersz (foo to prawda) nigdy nie zostanie usunięty.

create function dont_delete_foo_true()
returns trigger as
$x$
begin
    if old.foo then
        raise exception 'Can''t delete row where foo is true.';
    end if;
    return old;
end;
$x$ language plpgsql;

create trigger trg_test_delete
before delete on test
for each row 
execute procedure dont_delete_foo_true();

delete from test where id = 1;

BŁĄD: nie można usunąć wiersza, w którym foo jest prawdziwe.

dbfiddle tutaj