Constrainty w Snowflake'u?

Constrainty, czyli ograniczenia, są ważnym elementem w bazach danych. Ich celem jest zapewnienie poprawności danych poprzez ograniczenie wartości, jakie można zapisywać w tabelach przy użyciu komend INSERT, UPDATE i DELETE.

Snowflake wspiera 4 typy ograniczeń:

Primary Key (Klucz Główny)
Foreign Key (Klucz Obcy)
Unique (Unikatowość)
Not Null (Wartość Nie-Pusta)

ALE… Snowflake egzekwuje jedynie ograniczenie Not Null.

Czym są poszczególne ograniczenia i jakie są ich różnice między bazami transakcyjnymi a Snowflake’iem?

Primary Key

Klucze Główne umożliwiają jednoznaczne zidentyfikowanie każdego wiersza w tabeli. Gwarantuje unikatowość wartości Klucza Głównego w ramach całej tabeli oraz nie może zawierać wartości NULL. Klucz Główny można ustanowić na pojedynczej lub wielu kolumnach. Klucze Główne mogą służyć jako referencje dla Kluczy Obcych w celu zachowania spójności danych.

Jak Klucz Główny działa w Snowflake’u?

Możesz stworzyć Klucz Główny w Snowflake’u, jednak będzie on egzekwował jedynie ograniczenie Not Null. W Snowflake’u Klucz Główny pełni dwojaką funkcję:

zwiększa czytelność
Użytkownicy odczytując definicje tabel będą wiedzieć czego powinni się spodziewać po danej kolumnie lub grupie kolumn: unikatowości danych i braku wartości NULL.
może zwiększyć wydajność zapytań
Jeśli Klucz Główny i Obcy zostały zdefiniowane z wykorzystaniem funkcji RELY Snowflake będzie mógł pominąć zbędne joiny.

Tworzenie tabeli z Kluczem Głównym

Podczas tworzenia tabeli wewnątrz definicji kolumny.

create or replace table table_name (
id number(10,0) constraint pk_users primary key
,<pozostałe kolumny>);

Podczas tworzenia tabeli na końcu definicji.
Umożliwia utworzenie Klucza Głównego składającego się z wielu kolumn.

create or replace table table_name (
id number(10,0)
,<pozostałe kolumny>,constraint pk_users primary key (nazwa kolumny lub lista kolumn));

Zmieniając definicję tabeli wykorzystując ALTER TABLE.
W tym przypadku Klucz Główny nie nałoży automatycznie ograniczenia Not Null.

alter table table_name
add constraint nazwa_klucza_głównego primary key (nazwa kolumny lub lista kolumn);

Przykład tabeli z Kluczem Głównym

create or replace transient table cst.cst.users
(
id number(10,0) constraint pk_users primary key
,first_name varchar
,last_name varchar
,birthday date
);
describe table cst.cst.users;

W opisie tabeli można zauważyć, że Klucz Główny jest utworzony na kolumnie ID, jak również wartość „N” przy parametrze „null?” świadczy o ograniczeniu Not Null.

Foreign Key

Klucz Obcy idzie w parze z Kluczem Głównym. Klucz Obcy jest tworzony na pojedynczej kolumnie lub grupie kolumn i pozwala stosować wartości jedynie zawarte już w Kluczu Głównym, do którego się odnosi. W związku z tym, jedynym ograniczeniem, jakie egzekwuje Klucz Obcy to wykorzystanie danych z zakresu już istniejącego w powiązanym Kluczu Głównym.

Jak Klucz Obcy działa w Snowflake’u?

Krótko mówiąc bardzo podobnie jak Klucz Główny. Snowflake nie będzie wymuszał stosowania jedynie wartości z powiązanego Klucza Głównego.

Po co w takim razie możliwość Kluczy Obcych w Snowflake’u?

zwiększa czytelność
Bardzo podobnie, jak przy Kluczu Głównym – użytkownicy odczytując definicje tabel będą wiedzieć czego powinni się spodziewać po danej kolumnie lub grupie kolumn: wartości kolumny lub grupy kolumn powinny zawierać jedynie wartości znajdujące się już w Kluczu Głównym
może zwiększyć wydajność zapytań
Jeśli Klucz Główny i Obcy zostały zdefiniowane z wykorzystaniem funkcji RELY Snowflake będzie mógł pominąć zbędne joiny.

Tworzenie tabeli z Kluczem Obcym

Podczas tworzenia tabeli wewnątrz definicji kolumny.

create or replace table table_name (
id number(10,0) constraint nazwa_klucza foreign key references     nazwa_tabeli_do_którek_klucz_obcy_się_odnosi (nazwa_kolumny),<pozostałe kolumny>);

Podczas tworzenia tabeli na końcu definicji.
Umożliwia utworzenie Klucza Głównego składającego się z wielu kolumn.

create or replace table table_name (
id number(10,0)
,<pozostałe kolumny>,constraint nazwa_klucza foreign key (kolumna/kolumny_tej_tabeli) references nazwa_tabeli_do_którek_klucz_obcy_się_odnosi (nazwa kolumny lub lista kolumn);

Zmieniając definicję tabeli wykorzystując ALTER TABLE.
W tym przypadku Klucz Główny nie nałoży automatycznie ograniczenia Not Null.

alter table table_name
add constraint nazwa_klucza foreign key (nazwa kolumny lub lista kolumn) references nazwa_tabeli_do_którek_klucz_obcy_się_odnosi (nazwa kolumny lub lista kolumn);

Przykład tabeli z Kluczem Obcym

create or replace transient table cst.cst.transactions
(
id number(10,0) constraint pk_transactions primary key
,user_id number(10,0) constraint fk_transactions_users foreign key references cst.cst.users (id)
);
describe table cst.cst.transactions;

W opisie tabeli brak wzmianki o Kluczach Obcych.

Poniżej 2 przykłady jak można wydobyć informację o Kluczach Obcych:

Za pomocą funkcji GET_DDL() można poznać definicję całej tabeli.

select get_ddl('table','cst.cst.transactions');

Odczytując tabele table_constraints i referential_constraints dostępne w schemacie information_schema.

select *
from information_schema.referential_constraints;

Tu jednak nie dowiesz się na jakich kolumnach zostały utworzone klucze.

Unique

Unikalność można przypisać do pojedynczej kolumny lub grupy kolumn w tabeli. To ograniczenie sprawia, że zawartość kolumny lub grupy kolumn musi być unikatowa w ramach całej tabeli.

Jak ograniczenie Unique działa w Snowflake’u?

W przypadku Snowflake’a nic nie stoi na przeszkodzie, żeby utworzyć taki constraint, jednak Snowflake nie egzekwuje go. Użytkownik i tak będzie w stanie dopisać duplikujące się wartości.

Przykład tabeli z constraintem Unique

create or replace transient table cst.cst.users
(
id number(10,0) primary key
,first_name varchar unique
,last_name varchar
,birthday date
);
describe table cst.cst.users;

Poniżej widać, że na kolumnie FIRST_NAME został utworzony constraint Unique.

Not Null

Ograniczenie Not Null przypisuje się do pojedynczych kolumn w tabeli i sprawia, że nie da się wstawić wiersza z brakiem danych w tych kolumnach. Nie da się też zmodyfikować wartości, żeby zostawić pustą wartość.

Jak ograniczenie Not Null działa w Snowflake’u?

Not Null jest (na moment pisania tego wpisu) jedynym ograniczeniem, które Snowflake rzeczywiście egzekwuje pod względem weryfikacji wartości w kolumnie.

Tworzenie ograniczenia Not Null

Ograniczenie Not Null może być utworzone jedynie podczas tworzenia tabeli. Nie da się go dodać później poleceniem ALTER TABLE.

create or replace transient table cst.cst.users
(
id number(10,0) primary key
,first_name varchar unique not null
,last_name varchar not null
,birthday date
);
describe table cst.cst.users;

Wartość „N” parametru „null?” oznacza brak możliwości wprowadzania wierszy bez danych w tych kolumnach ani modyfikowania wierszy w taki sposób, żeby usunąć dane z tych kolumn.

Czy Snowflake będzie egzekwował kiedyś te ograniczenia?

Zapowiada się, że tak. Mają to przynieść tabele hybrydowe (Unistore: Hybrid Table). Na dzień tworzenia tego wpisu jeszcze nie są dostępne. Opiszę je w odrębnym wpisie, a niecierpliwych odsyłam do strony Snowflake’a.

Constrainty w Snowflake’u?