Postgres jest jednym z kilku najpopularniejszych otwartych systemów zarządzania relacyjnymi bazami danych.Jak w każdej przyzwoitej bazie danych więc i w Postgres, wystepuje bardzo przydatny system ograniczeń.Ograniczenia Postgres to sposób na poinformowanie systemu o tym, jakie rodzaje danych można wstawiać do tabel, kolumn lub wierszy. Dodanie ograniczeń do bazy danych chroni aplikację np. przed nieodpowiednimi danymi czy wartościami null.Ograniczenia Postgres doskonale sprawdzają się w wychwytywaniu nieodpowiednich wartości oraz innych wyjątków nie uwzględnionych w kodzie aplikacji.
Przykład schematu bazy danych systemu rezerwacji samochodów:
- Tabela dla użytkowników
- Tabela dla samochodów
- Tabela wypożyczeń – odnosząca się do użytkowników oraz samochodów, wraz z początkiem i końcem czasu wypożyczenia.
Dwie pierwsze tabele możemy ustawić dla użytkowników i samochodów bez żadnych ograniczeń, dodajmy więc:
CREATE TABLE uzytkownik (
id serial PRIMARY KEY,
imię text,
email text
);
CREATE TABLE auto (
id serial PRIMARY KEY,
numer text
);Ograniczenia klucza obcego
Aby dodać trzecią tabelę, która będzie odwoływać się do kluczy podstawowych z dwóch, powyższych tabel, użyjemy ograniczenia klucza obcego. Jest to część wiersza tabeli (kolumny) referencji.
CREATE TABLE wypozyczenie (
uzytkownik_id int references uzytkownik(id),
auto_id int references auto(id),
Start_time timestamp,
end_time timestamp,
event_title text
);
Dodanie tego ograniczenia klucza obcego jest bardzo dobrym sposobem na powiązanie naszej tabeli wypożyczeń z innymi tabelami danych. Zapewnia to powiązanie z ich kluczami podstawowymi.
ALTER TABLE
Jeżeli utworzyłeś tabele i po pewnym czasie stwierdzasz, że jednak przydałyby się dodać ograniczenia klucza obcego – nic straconego. Możesz również utworzyć ograniczenia klucza obcego po fakcie, w istniejącej już tabeli. Używamy do tego: ALTER TABLEPrzykład:
ALTER TABLE public.wypozyczenie
ADD CONSTRAINT wypozyczenie_auto_id_fkey FOREIGN KEY (auto_id) REFERENCES public.auto(id);
ALTER TABLE public.wypozyczenie
ADD CONSTRAINT wypozyczenie_uzytkownik_id_fkey FOREIGN KEY (uzytkownik_id) REFERENCES public.uzytkownik(id);Ograniczenie klucza obcego tutaj nazwane jest np. wypozyczenie_uzytkownik _id_fkey. Jeśli nie podasz nazwy własnej, Postgres wygeneruje ją za Ciebie.
Unikalne ograniczenia Postgres
Ograniczenie unikalne to ustawienie, które wymaga, aby dane w określonej kolumnie lub wierszu były, jak mówi sama nazwa – unikatowe. Jest to szczególnie przydatne podczas tworzenia nazw użytkowników, unikalnych tożsamości lub dowolnego klucza podstawowego.
Przykład: Chcemy nałożyć unikalne ograniczenia na numer samochodu, aby nie doszło do przypadkowego zduplikowania numeru samochodu:
ALTER TABLE ONLY public.wypozyczenie
ADD CONSTRAINT auto_numer_unique UNIQUE (numer);Kaskadowość i klucze obce
Korzystając z ograniczeń kluczy obcych, nie można nie wspomnieć o kaskadowych instrukcjach aktualizacji i usuwania. Klucze obce mogą, więc wpływać na dane zamieszczone w innych tabelach powiązanych głównie poprzez 2 modyfikatory: ON DELETE i ON UPDATE.
Usuwanie kaskadowe jest ważne np: jeśli musisz usunąć dane użytkownika ze względu na RODO. Załóżmy, że w naszym schemacie, że chcemy usunąć użytkowników po określonym czasie i chcemy również wyczyścić ich historię wypożyczeń samochodów. Dodanie tego ograniczenia spowoduje usunięcie wiersza w tabeli wypożyczeń, wtedy gdy wiersz użytkownika zostanie usunięty.
ALTER TABLE public.wypozyczenie
ADD CONSTRAINT wypozyczenie_uzytkownik_id_fkey FOREIGN KEY (uzytkownik_id) REFERENCES public.uzytkownik(id) ON DELETE CASCADE;Jeśli nie dodasz ON DELETE CASCADE, Postgres uniemożliwi usunięcie rekordu z tabeli użytkowników, chyba że wszystkie rekordy rezerwacji dla tego użytkownika zostaną najpierw usunięte.
Ważne: Instrukcje kaskadowe należy dodać w momencie dodawania ograniczenia klucza obcego. Nie ma możliwości dodania kaskadowości po fakcie np. za pomocą ALTER TABLE.
Ograniczenia Postgres Not-Null
Dodanie ograniczenia typu not-null zapewnienie, że nigdy nie dodasz wiersza z niekompletnymi danymi.
Przykład: Chcemy upewnić się, że samochód o określonym numerze jest możliwy do wypożyczenia.
ALTER TABLE public.wypozyczenie ALTER COLUMN auto_id SET NOT NULL;Innym przykładem będzie upewnienie się, że wszystkie wypożyczenia mają czas rozpoczęcia i zakończenia:
ALTER TABLE public. wypozyczenie ALTER COLUMN start_time SET NOT NULL;
ALTER TABLE public. wypozyczenie ALTER COLUMN end_time SET NOT NULL;
Ważne: Ograniczenia not null nie mają nazwy i nie pojawią się również w tabeli systemowej pg_constraints.
Ograniczenia sprawdzające
Dzięki ograniczeniom sprawdzającym dodajemy prostą logikę do danych, ponieważ baza danych będzie sprawdzać dane przed wstawieniem. Ograniczenia sprawdzające będą miały zastosowanie do pojedynczego wiersza w tabeli.Na przykład w naszym schemacie powinniśmy dodać trochę logiki do czasu wypożyczenia. Czas rozpoczęcia powinien być „bliższy” niż czas zakończenia. Czas rozpoczęcia powinien zaczynać się po 9 rano i kończyć się przed 22:00. Natomiast odstęp pomiędzy czasem rozpoczęcia a czasem zakończenia powinien być dłuższy niż 20 minut.
W tym konkretnym przypadku składnia ograniczeń sprawdzających wygląda następująco:
- Start_time jest mniejszy niż end_time
ALTER TABLE public.wypozyczenie ADD CONSTRAINT start_before_end check (start_time < end_time )- Start_time powinien być większy niż 9.00, end_time musi być mniejszy niż 22.00.
ALTER TABLE public.wypozyczenie ADD CONSTRAINT daytime_check check (start_time::time >= '09:00:00' AND end_time::time <= '22:00:00')- Przerwa pomiędzy start_time i end_time większa niż 20 minut.
ALTER TABLE public.wypozyczenie ADD CONSTRAINT interval_check check (end_time - start_time >= interval '20 minutes')Ograniczenia wykluczenia
Jeśli chcesz porównać wartości wiersza z innymi wierszami w tej samej tabeli, musisz użyć bardziej skomplikowanej metody znanej jako ograniczenie wykluczenia. Ograniczenia wykluczenia są używane do definiowania wyrażenia zwracającego wynik prawda lub fałsz i wstawiania danych tylko wtedy, gdy odpowiedź to fałsz. Odpowiedź – prawda będzie oznaczać, że te dane już istnieją, więc nie możesz ich wstawić. Odpowiedź – fałsz będzie oznaczać, że dane jeszcze nie istnieją i w tym wypadku możesz je wstawić.Typowe zastosowania ograniczenia wykluczenia to dodawanie ról dla użytkownika, w przypadku gdy może mieć tylko jedną rolę.
Przy ograniczeniu wykluczenia warto wspomnieć o danych przestrzennych. Ponieważ działają one w oparciu o strukturę boxu, wypełnionego punktami, których elementem składowym jest zakres czasu. Z tego też powodu przy ograniczeniu wykluczenia znajduje zastosowanie indeks GiST.
Indeks GiST
Krótko o indeksie GiST (ang. Generalized Search Trees) – to infrastruktura, w ramach której mogą być implementowane różne strategie indeksowania dla różnych danych. Standardowo dostępne są indeksy dla danych dwuwymiarowych.Indeks GIST pomoże Postgresowi sprawdzić, czy to pole jest wypełnione, czy nie. Nie musimy tworzyć indeksu bazowego osobno, utworzenie ograniczenia zrobi to za nas.
Ograniczenie wykluczenia prawdopodobnie w rozpatrywanym przypadku będzie posiadało:
- Oświadczenie WYKLUCZ
- Oświadczenie GiST
- Definicja box-u wraz z jego punktami
- && jest to operator który informuje o tym, jeżeli obramowanie box-u zachodzi na inny box
W naszym przypadku ważne jest aby pomiędzy boxami było trochę odstępu. W przykładzie dodana jest różnica 0,5. Ogólnie powinna to być dowolna liczba, która jest większa niż 0 i mniejsza niż jeden.
Ograniczenia wykluczenia dla czasu wypożyczenia samochodu, dzięki którym nowe rezerwacje nie pokrywają się z już istniejącymi:
ALTER TABLE public.wypozyczenie ADD CONSTRAINT wypozyczenie_overlap
EXCLUDE USING GIST (
box (
point(
extract(epoch from start_time),
auto_id
),
point(
extract(epoch from end_time) - 0.5,
auto_id + 0.5
)
)
WITH &&
);
Po więcej informacji zapraszamy na szkolenia z PostgreSQL
