Dokumentacja SQLite z przykładami: Klucze obce

Q: Jak utworzyć klucz obcy w SQLite?

Dodaj klauzulę REFERENCES othertable(column) do definicji kolumny w CREATE TABLE. Na przykład authorid INTEGER REFERENCES authors(id) sprawia, że author_id wskazuje na wiersz w authors. Kolumna, do której się odwołujesz, musi być PRIMARY KEY albo mieć ograniczenie UNIQUE.

Q: Co robi ON DELETE CASCADE w SQLite?

ON DELETE CASCADE każe SQLite automatycznie usuwać wiersze podrzędne, gdy usuwany jest ich rodzic. Inne opcje to RESTRICT (blokuje usunięcie), SET NULL (zeruje kolumnę klucza obcego), SET DEFAULT i NO ACTION (domyślna, w praktyce działa jak RESTRICT). Wybierz na podstawie tego, czy wiersze podrzędne mają sens bez rodzica.

Klucz obcy to wskaźnik między tabelami

Klucz obcy to kolumna w jednej tabeli, której wartość musi odpowiadać wierszowi w innej tabeli. W ten sposób relacyjne bazy danych mówią „ten wiersz posts należy do tamtego wiersza authors” bez kopiowania imienia i e-maila autora do każdego posta.

Oto najmniejszy możliwy przykład: tabela nadrzędna i podrzędna połączone kluczem obcym:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Cześć', 1), ('Notatki', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) to cała deklaracja klucza obcego. Mówi: ta kolumna przechowuje id z tabeli authors. Baza wie teraz, że obie tabele są powiązane, i jeśli wymuszanie jest włączone, odrzuci wstawienia wskazujące na nieistniejących autorów.

Klucze obce są domyślnie wyłączone

To najważniejszy fakt o kluczach obcych w SQLite i zaskakuje każdego: SQLite analizuje klauzule REFERENCES, ale ich nie wymusza, dopóki o to nie poprosisz. Powodem jest zgodność wsteczna: starsze bazy powstawały, zanim ta funkcja istniała.

Zobacz, co się dzieje bez wymuszania:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- autor 999 nie istnieje, ale SQLite bez problemu to przyjmuje:
INSERT INTO posts (title, author_id) VALUES ('Sierota', 999);

SELECT * FROM posts;

Osierocony wiersz trafił prosto do tabeli. Żeby dostać ochronę, której naprawdę chcesz, uruchamiaj PRAGMA foreign_keys = ON; na początku każdego połączenia:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Sierota', 999);

Teraz wstawienie kończy się błędem FOREIGN KEY constraint failed. Ta pragma działa na poziomie połączenia, a nie bazy: ustawienie nie jest zapisywane w pliku. Każda aplikacja, każda sesja CLI i każdy zestaw danych testowych musi ją ustawić. Większość kodu produkcyjnego uruchamia PRAGMA foreign_keys = ON; zaraz po otwarciu połączenia.

Czego naprawdę wymaga klauzula REFERENCES

Kolumna, do której się odwołujesz, musi być PRIMARY KEY albo mieć ograniczenie UNIQUE. Dzięki temu SQLite może zagwarantować jednoznaczne wyszukiwanie. Typy też powinny być zgodne: SQLite podchodzi do typów luźno, ale mieszanie ich to proszenie się o niespodzianki.

Klucz obcy można zapisać na dwa sposoby. Bezpośrednio przy kolumnie:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

Albo jako osobne ograniczenie na poziomie tabeli, co jest konieczne, gdy klucz obcy obejmuje kilka kolumn:

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Obie formy dają identyczne ograniczenia. Używaj tej, która lepiej się czyta w danej tabeli.

ON DELETE: co dzieje się z dziećmi

Gdy usuwasz wiersz nadrzędny, SQLite musi zdecydować, co zrobić z wierszami podrzędnymi, które na niego wskazują. Zasadę wybierasz przez ON DELETE:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Usunięcie Ady usunęło też oba jej posty. Dostępne opcje to:

CASCADE: usuń również dzieci. Dobre dla danych „posiadanych”, takich jak posty autora czy pozycje zamówienia.
SET NULL: wyzeruj kolumnę klucza obcego. Dobre, gdy dzieci mają przetrwać bez rodzica (np. komentarze usuniętego użytkownika stają się anonimowe).
SET DEFAULT: ustaw kolumnę klucza obcego na zadeklarowaną wartość domyślną.
RESTRICT: zablokuj usunięcie, jeśli istnieją jakiekolwiek dzieci. Błąd pojawia się od razu, w chwili wykonania instrukcji.
NO ACTION: wartość domyślna. W większości przypadków działa podobnie do RESTRICT (odkłada sprawdzenie do zatwierdzenia transakcji, ale wynik jest ten sam: nie możesz zostawić osieroconych dzieci).

ON UPDATE działa tak samo przy zmianach klucza rodzica, choć aktualizowanie kluczy głównych zdarza się rzadko.

Foreign key constraint failed: co to znaczy

Ten błąd zobaczysz w dwóch sytuacjach. Po pierwsze, przy wstawianiu lub aktualizacji dziecka z wartością, która nie ma pasującego rodzica:

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

Albo autor 999 nie istnieje, albo pomyliły się typy kolumn. Najpierw wstaw rodzica albo popraw wartość.

Po drugie, przy usuwaniu (lub aktualizacji) rodzica, który wciąż ma dzieci, gdy klucz obcy używa RESTRICT lub NO ACTION:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

Albo najpierw usuń dzieci, albo zmień klucz obcy na ON DELETE CASCADE/SET NULL, jeśli kaskada jest tym, czego naprawdę chcesz.

Istnieje też rzadszy kuzyn, FOREIGN KEY mismatch. Pojawia się, gdy kolumna, do której się odwołujesz, nie jest kluczem głównym ani unikalna albo liczba kolumn się nie zgadza. To błąd schematu, a nie danych.

Dodawanie kluczy obcych do istniejących tabel

ALTER TABLE w SQLite jest ograniczone: możesz dodać kolumnę z kluczem obcym, ale nie możesz dołożyć klucza obcego do kolumny, która już istnieje. Standardowe obejście to przebudowa tabeli ze zmianą nazwy:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Cześć', 1), ('Świat', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

Schemat działania: wyłącz wymuszanie, utwórz nową tabelę z potrzebnymi ograniczeniami, skopiuj dane, usuń starą tabelę, zmień nazwę. BEGIN/COMMIT zapewnia atomowość. Na końcu włącz wymuszanie z powrotem, a SQLite sprawdzi istniejące wiersze pod kątem nowych ograniczeń. Jeśli jakieś dane są niepoprawne, transakcja jest już zatwierdzona, więc w razie wątpliwości sprawdź je wcześniej.

Po migracji uruchom PRAGMA foreign_key_check;, żeby potwierdzić, że nie ma osieroconych wierszy.

Realistyczny schemat

Wszystko razem: mały schemat bloga z rodzicami, dziećmi i tabelą łączącą dla tagów w relacji wiele do wielu:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Zwróć uwagę na trzy rzeczy. author_id jest NOT NULL: każdy post musi mieć autora. Klucz obcy posts → authors działa kaskadowo, więc usunięcie autora czyści jego posty. Tabela łącząca post_tags ma kaskadę po obu stronach, więc usunięcie posta albo tagu automatycznie sprząta wiersze powiązań.

Nawyki, które oszczędzą ci kłopotów

Ustawiaj PRAGMA foreign_keys = ON; przy każdym połączeniu. Niech to będzie część procedury otwierania bazy w aplikacji, a nie coś, o czym trzeba pamiętać.
Dodaj indeks na kolumnie klucza obcego. SQLite automatycznie indeksuje klucz rodzica, ale nie dziecka, a ON DELETE CASCADE przy każdym usunięciu rodzica wyszukuje wiersze w tabeli podrzędnej.
Wybieraj ON DELETE świadomie. Domyślne NO ACTION jest bezpieczne, ale oznacza, że przy każdej próbie sprzątania trafisz na „constraint failed”. Zdecyduj, co ma się dziać, i zadeklaruj to.
Uruchamiaj PRAGMA foreign_key_check; po migracjach i masowych importach, żeby wyłapać osierocone wiersze, zanim staną się błędami.

Dalej: INNER JOIN

Klucze obce opisują relację, a złączenia pozwalają faktycznie wykonywać zapytania przez nią. Następna strona omawia INNER JOIN: łączenie wierszy z powiązanych tabel i pobieranie z każdej potrzebnych kolumn.

Najczęściej zadawane pytania

Jak utworzyć klucz obcy w SQLite?

Dodaj klauzulę REFERENCES other_table(column) do definicji kolumny w CREATE TABLE. Na przykład author_id INTEGER REFERENCES authors(id) sprawia, że author_id wskazuje na wiersz w authors. Kolumna, do której się odwołujesz, musi być PRIMARY KEY albo mieć ograniczenie UNIQUE.

Dlaczego klucze obce w SQLite nie są wymuszane?

SQLite analizuje deklaracje kluczy obcych, ale ich nie wymusza, dopóki tego nie włączysz. Uruchamiaj PRAGMA foreign_keys = ON; na początku każdego połączenia. To ustawienie dotyczy połączenia, a nie jest zapisywane w bazie, więc biblioteki i CLI muszą je ustawiać przy każdym połączeniu.

Co robi ON DELETE CASCADE w SQLite?

ON DELETE CASCADE każe SQLite automatycznie usuwać wiersze podrzędne, gdy usuwany jest ich rodzic. Inne opcje to RESTRICT (blokuje usunięcie), SET NULL (zeruje kolumnę klucza obcego), SET DEFAULT i NO ACTION (domyślna, w praktyce działa jak RESTRICT). Wybierz na podstawie tego, czy wiersze podrzędne mają sens bez rodzica.

Jak naprawić błąd 'foreign key constraint failed' w SQLite?

Ten błąd oznacza, że próbujesz wstawić lub zaktualizować wiersz, którego wartość klucza obcego nie pasuje do żadnego wiersza w tabeli nadrzędnej, albo usunąć rodzica, który wciąż ma dzieci. Najpierw sprawdź, czy wiersz, do którego się odwołujesz, istnieje, albo ustaw ON DELETE CASCADE, jeśli dzieci mają być usuwane automatycznie.

Powiązane zagadnienia