Klucze i ograniczenia powszechnie używane w SQL

czym są klucze i ograniczenia?

baza danych musi być zgodna z określonymi właściwościami, aby zachować integralność i jakość przechowywanych danych. Klucze i ograniczenia to reguły określające, jakie wartości danych są dozwolone w określonych kolumnach danych. Są one ważnym pojęciem bazy danych i są częścią definicji schematu bazy danych. Definiowanie kluczy i ograniczeń jest częścią procesu projektowania bazy danych i zapewnia, że dane w bazie danych są wiarygodne i zachowują swoją integralność. Ograniczenia mogą dotyczyć określonej kolumny, całej tabeli, więcej niż jednej tabeli lub całego schematu. Niezawodny system baz danych zapewnia, że ograniczenia utrzymują się przez cały czas. Klucze są szczególnym rodzajem ograniczenia używanym do ustanawiania relacji i wyjątkowości.

klucz podstawowy

niezbędną częścią nawiązywania relacji między dwoma podmiotami lub dwoma elementami danych jest prawidłowa identyfikacja danych. W SQL unikalna identyfikacja danych ma kluczowe znaczenie. Klucz podstawowy to unikalny identyfikator wiersza danych. W analogii do arkusza kalkulacyjnego, której do tej pory używaliśmy, zawsze była kolumna id. Chociaż każda kolumna może służyć jako klucz podstawowy, użycie kolumny o nazwie id jest łatwe ze względów mnemonicznych i jest popularną konwencją. W systemie RDBMS każda tabela może mieć tylko jeden klucz podstawowy.

klucz podstawowy jest gwarantowanym unikalnym identyfikatorem wiersza w tabeli. Z tego powodu możemy użyć podstawowej wartości klucza wiersza, aby odwołać się do tego wiersza z innych tabel. W poprzednim rozdziale ustaliliśmy relacje między wieloma tabelami na podstawie tego, ale relacyjne bazy danych potrzebują sposobu, aby się ze sobą odnosić, a Kolumna klucza podstawowego jest sposobem na sprawne działanie. W PostgreSQL klucze podstawowe mają również zalety wydajności, ponieważ wiadomo, że będą one często używane do wyszukiwania danych, zwłaszcza w wyszukiwaniach wielostolikowych.

w poprzednich rozdziałach widzieliśmy już składnię wyznaczania kolumny jako kolumny klucza podstawowego:

przyjrzyjmy się powyższemu poleceniu, które tworzy tabelę o nazwie users, z punktu widzenia zrozumienia kluczy podstawowych.

1. id kolumna jest podstawowym kluczem tej tabeli. Możemy również powiedzieć, że ograniczenie klucza podstawowego zostało zastosowane do kolumny id.
2. idmoże przechowywać tylko wartości liczbowe (int).
3. dla każdego nowego wiersza dodanego do tabeli pole id będzie automatycznie zwiększane o 1. W przypadku wielu RDBMS używane jest słowo kluczowe AUTO_INCREMENT. Z PostgreSQL, używamy serial do automatycznego zwiększania naszych ID i ustawiamy ich typ jako integer. Nie jest wymagane ręczne określanie wartości klucza podstawowego za każdym razem, gdy wstawiasz dane, PostgreSQL zrobi to za Ciebie, jeśli podasz serial jako typ dla id.

klucz obcy

kolumny klucza obcego służą do odwoływania się do innego wiersza danych, być może w innej tabeli. Aby odwołać się do innego wiersza, baza danych potrzebuje unikalnego identyfikatora dla tego wiersza. Dlatego kolumny kluczy obcych zawierają wartość klucza podstawowego wiersza, do którego odwołuje się wiersz. Na przykład tabela może mieć kolumnę o nazwie user_id jako kolumnę klucza obcego, która odpowiada kolumnie id tabeli users. Klucze obce to sposób, w jaki RDBMS ustawia relacje między wierszami danych, w tej samej tabeli lub w różnych tabelach.

z powyższego polecenia SQL widać, że linia do ustalenia tej relacji jest

FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE

w powyższym przykładzie id jest podstawowym kluczem tabeli books, który służy do nawiązania relacji z tabelą recenzji. Dane id są przechowywane w tabeli recenzji w polu book_id, aby zmapować książki z recenzjami. Klauzula ON DELETE CASCADE wskazuje, że jeśli książka zostanie usunięta, wszystkie recenzje związane z tą książką są również usuwane.

klucz złożony jako klucz podstawowy

w poprzednim rozdziale stworzyliśmy relację wiele-do-wielu, w której nasz klucz podstawowy składał się z dwóch kolumn. user_id i book_id razem tworzyły klucz podstawowy dla tabeli odsyłaczy, jak widać w tej linii instrukcji SQL.

PRIMARY KEY (user_id, book_id)

gdy primary_key składa się z unikalnej pary PRIMARY KEY (user_id, book_id), jest znany jako klucz złożony. Zwykle występuje w wielu-do-wielu relacjach, i musimy dodać dodatkową tabelę do przechowywania tych danych relacji. Klucz złożony zapewnia, że dane w tabeli będą unikalne dla relacji między Użytkownikiem a książką. Przykład:

|User_id | Book_id | | 1 | 1 | | 1 | 2 | | 2 | 1 | 

unikalne ograniczenia

oprócz klucza podstawowego, baza danych pozwala również na zastosowanie unikalnego ograniczenia w dowolnej kolumnie w bazie danych. Na przykład, w tabeli książki, możemy wybrać nie tylko mieć book_id jako unikalny klucz podstawowy, ale także musimy przechowywać numer ISBN książki. ISBN, International Standard Book Number to 10-cyfrowy unikalny numer dla książki. Możemy zdecydować się na zaprojektowanie naszej bazy danych, aby ta kolumna była unikalna. Mimo że nie jest to klucz podstawowy, duplikaty ISBNs nie będą dozwolone w tabeli książek, a jakość danych zostanie zachowana.

unikalne ograniczenie uniemożliwia duplikaty wpis do tabeli książek dla wartości kolumny isbn.

ograniczenia NOT NULL

w wielu naszych poleceniach SQL widzieliśmy „NOT NULL”. Ograniczenie NOT NULL zapobiega dopuszczaniu wartości null przez kolumnę (tzn. ta kolumna musi zawierać dane). Jest to ważne dla kluczowych dziedzin. Na przykład: jeśli chcesz dodać użytkownika do bazy danych, musisz podać nazwę użytkownika. Bez tego ograniczenia baza danych zostanie wypełniona wartościami null i stanie się mniej użyteczna.

jak SQL radzi sobie z tymi relacjami?

PostgreSQL umożliwia relacje pomiędzy schematem i danymi różnych tabel poprzez użycie kluczy obcych i operacji zwanej JOIN. Jak sama nazwa wskazuje, celem jest łączenie tabel. Dzięki temu mechanizmowi możemy na przykład uzyskać pełne dane autora powyższej tabeli recenzji, ponieważ ma ona pole user_id, które odnosi się do tabeli user. Oto szybki przykład:

teraz, gdy rozumiemy, jak tworzyć wiele tabel i klucze odgrywają rolę w ustanawianiu relacji, przyjrzymy się bliżej różnym połączeniom i temu, jak zapytania SQL są budowane za pomocą tych połączeń, aby pobrać dane wymagane z wielu tabel.