Artykuł
02/03/2012

Encyklopedia SQL - Klucze główne - PRIMARY KEY i IDENTITY

Autor: Paweł Wilkosz

Opublikowano: 2012-02-02

Niniejsza publikacja swoją tematyką skupiać się będzie na podstawach projektowania relacyjnych baz danych, ze szczególnym naciskiem na sposoby konstrukcji relacji pomiędzy tabelami za pomocą mechanizmu klucza głównego.

Przed wykonaniem zadań zapoznaj się z następującym materiałem:

Typy danych w T-SQL

Po wykonaniu zadań będziesz wiedział:

co to jest klucz główny oraz jaką rolę pełni w bazie danych,
jak oznaczać kolumnę do automatycznej generacji wartości.

Ograniczenie PRIMARY KEY

Zagadnienia ogólne

Określenie relacyjna baza danych swoją nazwę zawdzięcza matematyce, a dokładniej rzecz ujmując – teorii zbiorów. W ogólnej koncepcji tabele bazodanowe powiązane są ze sobą za pomocą relacji, co znacznie zwiększa elastyczność konstrukcji oraz pozwala na minimalizację rozmiarów zbioru informacji w stosunku do tradycyjnego (liniowego) przechowywania danych.

Każda z tabel musi posiadać swój własny, unikatowy w ramach własnej struktury, identyfikator – zwany kluczem głównym. PRIMARY KEY jednoznacznie identyfikuje każdą krotkę (rekord), dzięki czemu, oprócz spełnienia warunków normalizacji bazy danych (Normal Form), może występować jako łącznik w obrębie innych tabel. Aby lepiej zobrazować problem konieczności występowania klucza głównego, posłużę się następującym przykładem. Załóżmy, iż użytkownik chce zaprojektować prostą bazę danych, do której będzie zapisywał informacje, jakie produkty z jego sklepu internetowego zakupił dany klient. W tym celu musi zaprojektować trzy tabele: „Klient”, „Produkt”, „Zakupy”. Niech każda z nich zawiera kolumny oraz przykładowe wartości, przedstawione w Tabelach 1. i 2.

Tabela 1. Tabela "Klient".

Imię	Nazwisko	Adres	Telefon
Jan	Kowalski	Kraków 11	123 345 567
Krzysztof	Nowak	Warszawa 112	098 887 665

Tabela 2. Tabela "Produkty".

Nazwa produktu	Cena	Kod Produktu
Encyklopedia SQL	11	123456
Encyklopedia .NET	10	765434

O ile zaprojektowanie tabel „Klient” i „Produkty” nie było trudne, o tyle tabela „Zakupy” wymagać będzie większego poświęcenia. Należy bowiem rozróżnić, który klient zakupił konkretny produkt. Można pokusić się o rozpoznawanie petenta na podstawie imienia i nazwiska, wtedy tabela zakupy będzie wyglądać następująco:

Tabela 3. Tabela "Zakupy" bez klucza głównego.

Imię	Nazwisko	Nazwa produktu	Cena	Kod produktu
Jan	Kowalski	Encyklopedia SQL	11	123456
Krzysztof	Nowak	Encyklopedia SQL	11	123456

Ale co zrobić w przypadku, gdy tabela będzie posiadała informacje o dwóch osobach posiadających identyczne dane personalne? Gorzej, nie można wykluczyć, iż dwie osoby o takich samych nazwiskach zamieszkują pod tym samym adresem. Osobną kwestię zajmuje również sprawa wypełniania tabeli „Zakupy”, gdzie rekordy się powtarzają i zaburzają atomiczność krotek bazodanowych. Jedynym sensownym rozwiązaniem jest dodanie osobnej kolumny dla poszczególnych tabel, która będzie stanowić unikatowy identyfikator każdego z wierszy, jak to poczyniono w Tabelach 4. i 5.

Tabela 4. Tabela "Klient" z kluczem głównym (ID).

ID	Imię	Nazwisko	Adres	Telefon
1	Jan	Kowalski	Kraków 11	123 345 567
2	Krzysztof	Nowak	Warszawa 112	098 887 665

Tabela 5. Tabela "Produkty" z kluczem głównym (ID).

ID	Nazwa produktu	Cena	Kod Produktu
1	Encyklopedia SQL	11	123456
2	Encyklopedia .NET	10	765434

Na tej podstawie od tabeli „Zakupy” wymagać się będzie jedynie agregatu identyfikatorów z tabeli „Klient” i „Produkty” np.:

Tabela 6. Tabela "Zakupy" po refakturowaniu.

ID	ID Klienta	ID Produktu
1	1	1
2	2	1
3	1	2

Podejście to pozwala w równie efektywny sposób odnotować sytuację, w której dany klient zakupił więcej niż jeden produkt (w Tabeli 6. klient o ID 1 zamówił produkt 1 i 2).

PRIMARY KEY w T-SQL

W kolejnej części publikacji chciałbym zaprezentować sposoby oznaczania kolumn jako kluczy głównych w języku T-SQL. Daną kolumnę, która ma pracować jako PRIMARY KEY w T-SQL najczęściej oznacza się przy tworzeniu tabel. W podstawowej składni wystarczy dodać owe słowo kluczowe zaraz po typie danych dla wskazanej definicji. Poniższy listing przedstawia skrypt tworzący tabelę klient, wraz ze wskazaniem ID jako klucza głównego:

CREATE TABLE Klient
(
    ID INT PRIMARY KEY,
    IMIE VARCHAR(20),
    NAZWISKO VARCHAR(20),
    ADRES VARCHAR(50),
    TELEFON VARCHAR(20)
)

Warto w tym miejscu nadmienić, iż rolę klucza głównego nie musi spełniać tylko jedna kolumna. Nic nie stoi na przeszkodzie, aby na PRIMARY KEY składały się dwie lub więcej wartości. Projektowanie takiej tabeli odbyć się może jedynie poprzez CONSTRAINT albo wskazanie kolumn w polu PRIMARY KEY (kolumna1, … , kolumnaN). Przykład tworzenia tabeli „Klienci”, wraz z kluczem głównym wskazanym na ID oraz PESEL, znajduje się na poniższym listingu:

CREATE TABLE Klient
(
    ID INT,
  PESEL INT,
    IMIE VARCHAR(20),
    NAZWISKO VARCHAR(20),
    ADRES VARCHAR(50),
    TELEFON VARCHAR(20)

  PRIMARY KEY (ID, PESEL)
)

Autoinkrementacja

We wstępie do niniejszego artykułu wspomniałem, iż klucz główny ma posiadać wartość unikatową w obrębie danej tabeli. W związku z tym programista baz danych już na początku może mieć problem z monitorowaniem aktualnej wartości kolumny przy dodawaniu kolejnych rekordów. Z pomocą, jak zwykle, przychodzi T-SQL wraz z opcją IDENTITY. Klauzula IDENTITY pozwala określić, od jakiego numeru SQL Server ma zacząć wprowadzać dane oraz o jaką wartość ma być powiększona kolejna z nich, po wykonaniu operacji INSERT. Następujący przykład pozwala lepiej zobrazować problem. Zakładając, iż użytkownik docelowo będzie chciał automatycznie wypełniać wartość pola ID dla tabeli „Klient” i powiększać ją o 2 po każdym wpisie, powinien utworzyć tabelę wg następującego schematu:

CREATE TABLE Klient
(
    ID      INT PRIMARY KEY IDENTITY(1,2),
  PESEL         INT,
    IMIE        VARCHAR(20),
    NAZWISKO        VARCHAR(20),
    ADRES       VARCHAR(50),
    TELEFON         VARCHAR(20)
)

Następnie powinien wprowadzić dane za pomocą polecenia:

INSERT INTO Klient
(PESEL, IMIE, NAZWISKO, ADRES, TELEFON)
VALUES
(123456, 'Jan', 'Kowalski', 'Krakow 111', '123 345 567'),
(123456, 'Jan', 'Nowak', 'Warszawa 111', '123 345 567')

Po wykonaniu tej opcji można z łatwością zauważyć, iż pole ID, mimo, iż nie zostało wskazane w zapytaniu INSERT, zostało wypełnione, a różnica pomiędzy dwoma kolejnymi rekordami wynosi dokładnie 2 (Rys. 1.).

Rys. 1. Wynik działania zapytania INSERT na tabeli "Klienci".

Informacja

Zapamietaj, że:

Klucz główny jednoznacznie identyfikuje każdy rekord danej tabeli.
Klucz główny może się składać z jednej bądź wielu kolumn.
Za pomocą klauzuli IDENTITY można wskazywać kolumny podlegające autoinkrementacji po operacji INSERT wraz z kolejną wartością, jaką mają przyjąć.

Podsumowanie

Artykuł swoją tematyką koncentrował się na prezentacji mechanizmu klucza prywatnego w tabelach baz danych.

W kolejnej publikacji dowiesz się, co to jest klucz obcy i w jaki sposób łączyć tabele w celu wyciągania interesujących informacji.

Dodatkowo zobacz: