Внешние ключи в DB Browser for SQLite

Сказ о том, как включить поддержку внешних ключей в популярной программе по редактированию SQLite баз данных.

Откроем программу DB Browser for SQLite.

Создадим новую базу данных, например, music.db:

Рисунок 1 — Создание новой базы данных

Не будем пока создавать таблицы:

Рисунок 2 — Отказ от создания таблицы

Включим поддержку внешних ключей. Не забудьте нажать на кнопку Save:

Рисунок 3 — Включение поддержки внешних ключей

Теперь покажем, как работают внешние ключи.

Создадим две таблицы artists (музыканты) и tracks (песни). Конструктор не будем использовать. Во второй таблице будем хранить номер музыканта как внешний ключ:

CREATE TABLE artists(
  _id    INTEGER PRIMARY KEY AUTOINCREMENT,
  name  TEXT
);

CREATE TABLE tracks(
  _id     INTEGER PRIMARY KEY AUTOINCREMENT,
  title   TEXT,
  id_artist INTEGER,
  FOREIGN KEY(id_artist) REFERENCES artists(_id)
);

Рисунок 4 — Запуск SQL скрипта

Рисунок 5 — Созданные таблицы

Опять же без конструктора закинем две записи о двух исполнителях:

INSERT INTO artists (name) VALUES ('Nightwish');
INSERT INTO artists (name) VALUES ('Scooter');

Рисунок 6 — Запуск SQL скрипта

Рисунок 7 — Созданные записи в таблицу

Попробуем добавить записи к таблице песню с правильным номером исполнителя:

INSERT INTO tracks (title, id_artist) VALUES ('Wishmaster', 1);

Запрос прошел успешно:

Рисунок 8 — Запуск SQL скрипта по вставке новой записи

А теперь попробуем добавить запись с несуществующим номером исполнителя:

INSERT INTO tracks (title, id_artist) VALUES ('To the Sky', 100);

А теперь запрос не пройдет из-за внешнего ключа:

Рисунок 9 — Ошибка при выполнении SQL скрипта

И в таблице tracks мы увидим только одну запись:

Рисунок 10 — Записи в таблице

Обратите внимание на то, что в SQLite можно добавлять внешние ключи через CREATE. В «полноценных» БД можно добавлять внешние ключи через ALTER. Но в SQLite это сделать нельзя:

ALTER TABLE child ADD CONSTRAINT fk_child_parent
                  FOREIGN KEY (parent_id)
                  REFERENCES parent(id);

И напоследок покажу, как сделать выборку песен из БД так, чтобы вместо номеров песен мы видели названия самих исполнителей.

Этот пример стандартного вызова SELECT, когда мы увидим песни с номерами исполнителей:

SELECT * FROM TRACKS;

Рисунок 11 — Результат работы команды SELECT

А вот пример кода, который вернет нам то, что нужно:

SELECT t._id, t.title, a.name AS a_name
FROM tracks t
LEFT JOIN artists a ON t.id_artist = a._id;

Рисунок 12 — Результат работы команды SELECT

Более упрощенный код запроса:

SELECT tracks._id, tracks.title, artists.name
FROM tracks
LEFT JOIN artists ON tracks.id_artist = artists._id;

Рисунок 13 — Результат работы команды SELECT

Рекомендую прочитать эту статью.