W bazie danych rekordy w tabeli  powinny być jednoznacznie identyfikowane. SQLite dostarcza na to kilka sposobów. Czym jest PRIMARY KEY, INTEGER PRIMARY KEY, ROWID i AUTOINCREMENT i jaka jest między nimi różnica? Odpowiedź znajdziemy w niniejszym artykule.

Czym jest ROWID ?

Rozpoczniemy od utworzenia prostej tabeli. Niech nasza tabela przechowuje dane dotyczące osób i nazywa się person . Będzie przechowywała imiona i nazwiska osób, a więc będą potrzebne kolumny do ich przechowywania, które nazwiemy: fname , lname :

CREATE TABLE person ( fname text, lname text);

Utworzyliśmy tabelę, wpiszmy do niej kilka rekordów:

INSERT INTO person VALUES('Anna','Nowak');
INSERT INTO person VALUES('Tomasz','Wiśniewski');
INSERT INTO person VALUES('Maria','Kowalska');

Dane mamy wstawione. Pewnie zauważyłeś, że tabela nie ma zdefiniowanego klucza głównego. Jak jest więc identyfikowany wiersz w tabeli? SQLite posiada mechanizm, który podczas tworzenia tabeli dodaje specjalną ukrytą kolumnę  nazwaną ROWID. Do niej jest dla każdego wiersza generowana automatycznie i wstawiana unikalna wartość (id) w postaci liczby całkowitej. Wartości są nadawane sekwencyjnie zaczynając od 1.

SQLite pozwala pobrać ROWID wiersza. Pobierzmy więc ROWID i wszystkie inne kolumny z naszej tabeli person .

SELECT ROWID, * FROM person;

Jak widać pierwszy wiersz otrzymał rowid=1, drugi 2, trzeci 3.

Usuńmy wiersz z rowid=2:

DELETE FROM person WHERE lname='Wiśniewski';

A teraz wstawiamy nowy rekord:

INSERT INTO person VALUES('Stefan','Nowakowski');

Pobieramy dane i rowid każdego rekordu:

SELECT ROWID , * FROM person ;

Największe rowid w tabeli było równe 3, więc kolejne nadane zostało jako 4, mimo że rekord z rowid został usunięty wcześniej i ta wartość była już wolna(nie była to jednak lczba najwięsza bo równa 2). Usuńmy więc tym razem ostatni rekord w tabeli z rowid=4.

DELETE FROM person WHERE lname='Nowakowski';

I wstawimy kolejny rekord:

INSERT INTO person VALUES('Amanda','Leczkowska');

Tym razem widać, że nowy rekord otrzymał używane kiedyś rowid=4, gdyż obecnie najwyższa wartość dla rowid była 3, więc została nadana kolejna równa 4, mimo że kiedyś używana.

SELECT ROWID , * FROM person;

Dokumentacja SQLite mówi, że każda tabela posiada ROWID z wyjątkiem tabeli, która jest wirtualną tabelą lub jest tabelą utworzoną z opcją  WITHOUT ROWID. Dostęp do rekordów posiadających ROWID jest szybki, gdyż dane w takiej tabeli są przechowywane jako struktura B-drzewa, w której rowid stanowi klucz do wartości.

PRMARY KEY a INTEGER PRMARY KEY

Aliasem dla ROWID jest INTEGER PRIMARY KEY, ale nie sam PRIMARY KEY. Przejdźmy więc do utworzenia tabeli person z  PRIMARY KEY:

CREATE TABLE person ( id PRIMARY KEY , fname text, lname text);

Tym razem w przeciwieństwie do ROWID, to my definiujemy klucz główny w tabeli (u nas  kolumna o nazwie id) , by identyfikowała każdy wiersz w w tabeli. Jednak nie wprowadzając typu danych dla klucza głównego, to SQLite będzie dopasowywał typ, a co za tym idzie nie zapewni nam identyfikacji wierszy. Dlaczego? Wprowadźmy kilka wierszy:

INSERT INTO person VALUES( 0 ,'Anna','Kowalska');
INSERT INTO person VALUES( -5 ,'Kasia','Nowak');
INSERT INTO person VALUES( 'nowa' ,'Alina','Nowakowska');
INSERT INTO person VALUES( NULL ,'Kazimierz','Nowak');
INSERT INTO person VALUES( NULL ,'Stefan','Kowalski');

Zauważmy, że jako klucz główny została przyjęta każda wartość: liczba ujemna, dodatnia, łańcuch znakowy a nawet NULL. Jednak jak widać NULL zostaje przyjmowany wiele razy, nie zostaje uznawany za tą samą wartość, co powoduje, że wiersz nie jest jednoznacznie identyfikowany. Jedynie powtórzenie innej wartości niż NULL wyrzuci błąd i nie wstawi rekordu.

Tak więc samo PRIMARY KEY nie zapewni unikalności gdyż umożliwia wstawianie NULL i jeszcze jego powtarzalność w tabeli. Aby nie dopuścić do tego, musimy nadać na tą kolumnę ograniczenie w postaci NOT NULL:

CREATE TABLE person ( id PRIMARY KEY NOT NULL , fname text, lname text);

A oto próba wstawienia rekordu z NULL dla kolumny z kluczem głównym:

INSERT INTO person VALUES( NULL ,'Stefan','Kowalski');

Podobnego zachowania możemy spodziewać się dla INTEGER PRIMARY KEY bez NOT NULL:

CREATE TABLE person ( id INTEGER PRIMARY KEY , fname text, lname text);

SQLite w tej sytuacji pozwoli na wstawienie kilka razy NULL jako klucza głównego:

a błąd pojawi się dopiero przy próbie wstawienia wartości tekstowej jako klucza (zdefiniowanie jako INTEGER zadziałało). I tu musimy zadbać o dodanie do definicji tej kolumny NOT NULL :

CREATE TABLE person ( id INTEGER PRIMARY KEY NOT NULL , fname text, lname text);

INTEGER PRMARY KEY z AUTOINCREMENT

Co jeszcze odróżnia INTEGER PRIMARY KEY NOT NULL od ROWID ? W tym pierwszym przypadku musimy wstawiać wartość do tabeli podczas wstawiania rekordu do tabeli, w drugim dzieje się to w sposób dla nas ukryty. Dlatego często do PRIMARY KEY dodaje się AUTOINCREMENT , czyli automatyczne generowanie kolejnych liczb całkowitych zaczynając od 1 , tak jak ma to miejsce w przypadku ROWID:

CREATE TABLE person ( id INTEGER PRIMARY KEY AUTONICREMENT , fname text, lname text);

Wtedy musimy taką kolumnę omijać podczas wstawiania danych, gdyż wartości dla tej kolumny dla każdego  rekordu są automatycznie generowane:

Jak zapewne zauważyłeś, wstawienie NULL i tak powoduje nadanie kolejnej liczby całkowitej. Zdefiniowanie AUTOINCREMENT dla kolumny zwalnia nas z dodawania NOT NULL .

Pamiętaj, że AUTOINCREMENT może być dodane tylko dla kolumny zdefiniowanej jako INTEGER PRIMARY KEY, w pozostałych przypadkach zwróci błąd.

Co lepsze,  INTEGER PRIMARY KEY AUTOINCREMENT , czy ROWID ?

ROWID – usunięcie ostatniego rekordu z  rowid=3 spowoduje, że następny wstawiany  rekord będzie miał to samo rowid=3, co już wcześnie w artykule sprawdziliśmy na przykładzie.Tak samo działa INTEGER PRIMARY KEY, które jest alisem dla ROWID.

Natomiast INTEGER PRIMARY KEY AUTOINCREMENT mimo usunięcia rekordu powoduje, że kolejny nadany id będzie o 1 większy od ostatniego nadanego, nawet jeśli został usunięty. Istnieje więc ochrona wcześniej już użytych przez usunięte rekordy id. Sprawdźmy to na przykładzie. Tworzymy tabelę t :

create table t(id INTEGER PRIMARY KEY AUTOINCREMENT , fname text);

Następnie wstawiamy do niej kilka rekordów:

insert into t values( null ,’Ania’);
insert into t values( null ,’Kasia’);
insert into t values( null ,’Zosia’);

Pobieramy wszystkie dane z tabeli, aby zobaczyć automatycznie nadane id:

Teraz usuniemy rekord z Zosią i wstawimy nowy ze Stefanem:

DELETE FROM t WHERE fname='Zosia';
INSERT INTO t VALUES(null,'Stefan');

I sprawdzamy jaki id otrzymał Stefan. Nie jest to id usuniętego rekordu z Zosią, a następny kolejny numer, czyli 4:

Należy przy tym podkreślić, że INTEGER PRIMARY KEY AUTOINCREMENT jest wolniejsze od ROWID i bardziej zasobożerne: zużywa więcej mocy procesora, pamięci i przestrzeni dyskowej. ROWID jest bardzo szybkie. Jeśli więc nie masz potrzeby użycia INTEGER PRIMARY KEY AUTOINCREMENT, pozostań przy ROWID.

Spójrzmy jeszcze na jedną rzecz:

Jak widać, jeśli masz już zdefiniowaną kolumnę jako INTEGER PRIMARY KEY, a w tym przypadku tak jest, rowid dla tej tabeli będzie miało przypisane wartości nadane przez kolumnę INTEGER PRIMARY KEY. Dlatego też w tym przypadku pobranie rowid dla ostatniego rekordu jest 4, a nie 3, jakby to było, gdyby nie było takiej kolumny INTEGER PRIMARY KEY, gdyż ROWID wykorzystuje id ostatnich usuniętych rekordów jeśli ta liczba jest większa od istniejących już w bazie.

Tak samo będzie jeśli zdefiniowana kolumna id nie będzie AUTOINCREMENT:

create table person(id INTEGER PRIMARY KEY , fname text);

Po wstawieniu rekordów sprawdzamy id z liczbami nie po kolei jeden po jeden:

ROWID jest w tym przypadku też równe zdefiniowanej kolumnie INTEGER PRIMARY KEY.

Zestawienie sposobów identyfikowania wierszy omówionych w artykule, zamieszczam w tabeli poniżej: