Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В реляционных базах данных, вернее, в реляционных СУБД есть множество приемов, которые позволяют обеспечить целостность данных, мы уже разобрались с ограничением уровня столбца и первичным ключом, который является одновременно и индексом таблицы базы данных, позволяющим значительно ускорить выборку данных из базы данных, и правилом, которое четко выполняет SQLite. В этой записи мы поговорим про еще одно ограничение уровня таблиц: внешний ключ или FOREIGN KEY.

Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3

Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3

Из этой записи вы узнаете как работает и для чего нужны внешние ключи в базах данных SQLite3. Познакомитесь с синтаксисом FROREIGN KEY в SQLite. А так же узнаете, как реализовать связи один ко многим и многие ко многим между таблицами базы данных под управлением SQLite при помощи внешних ключей. Но, стоит заметить, что вы сможете реализовать данные связи и без использования FOREIGN KEY, но в этом случае вы никак не обеспечите целостность данных в ваших таблицах.

Работа внешних ключей в базах данных SQLite: FOREIGEN KEY и REFERENCE в SQLite3

Внешний ключ или FOREIGN KEY – это ограничение уровня таблицы в реляционных базах данных, в том числе и в базе данных SQLite3. Внешние ключи определяют правила, по которым будут связаны таблицы в базах данных SQLite. Но, кроме того, что внешние ключи определяют то, как будут связаны таблицы в базах данных SQLite3, они еще нужны для обеспечения целостности данных в базах данных.

В SQLite внешний ключ объявляется при помощи конструкции FOREIGN KEY, а таблица, на которую ссылается внешний ключ указывается после ключевого слово REFERENCE. Обратите внимание: указывается не только таблица, но и столбец, на который идет ссылка. Ниже вы можете увидеть изображение, на котором показан синтаксис использования внешнего ключа в базах данных SQLite, вернее синтаксис конструкции REFERENCE.

Синтаксис ограничения внешнего ключа в базах данных SQLite3

Синтаксис ограничения внешнего ключа в базах данных SQLite3

Правила использования внешнего ключа не очень сложные, но давайте разберемся с тем, как реализован внешний ключ в SQLite3 и его конструкции: FOREIGEN KEY и REFEERENCE. Обратите внимание: когда вы связываете таблицы при помощи внешнего ключа одна таблица является родительской, а вторая таблица является дочерней. Внешний ключ всегда ссылается на родительскую таблиц, другими словами конструкция FOREIGN KEY и REFERENCE указывается в дочерней таблице.

Подведем итог: внешний ключ в базах данных SQLite необходим для реализации связей между таблицами. FOREIGEN KEY позволяет реализовывать связи между таблицами в базах данных. Конструкция REFERENCE используется для указания ссылки на родительскую таблицу. Внешний ключ обеспечивает целостность данных между двумя таблицами и необходим для нормализации базы данных. Вторая нормальная форма и третья нормальная форма не могут быть реализованы без внешнего ключа. Вернее будет сказать: мы можем организовать связи между таблицами без внешнего ключа, но проверка правил при этом выполняться не будет.

Давайте рассмотрим несколько практических примеров реализации внешнего ключа в базах данных SQLite. Мы реализуем два типа связи: связь один ко многим и связь многие ко многим.

Реализация связи один ко многим в базах данных SQLite. Пример связи один ко многим и FOREIGEN KEY в SQLite

Давайте реализуем связь один ко многим при помощи внешнего ключа, для этого воспользуемся конструкциями FORIGEN KEY и REFERENCE. Мы будем связывать при помощи внешнего ключа две таблицы: таблицу авторов и таблицу книг, поэтому давайте договоримся о допущение, что один автор может написать много книг, но у книги может быть только один автор.

Чтобы реализовать связь один ко многим, нам нужно воспользоваться конструкцией FOREIGEN KEY и REFERENCE при создании таблицы при помощи команды CREATE.

[php]

PRAGMA foreign_keys=on;

CREATE TABLE books (

Id INTEGER PRIMARY KEY,

title TEXT NOT NULL,

count_page INTEGER NOT NULL CHECK (count_page >0),

price REAL CHECK (price >0),

auth_id INTEGER NOT NULL,

FOREIGN KEY (auth_id) REFERENCES auth (id)

);

CREATE TABLE auth (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER  CHECK (age >16)

);

[/php]

Здесь нам нужно дать пояснение к том, как мы создали внешний ключ для базы данных. Во-первых, в SQLite3 по умолчанию отключена поддержка внешних ключей, команда PRAGMA позволяет включить внешние ключи в базах данных SQLite. Во-вторых, помимо внешнего ключа наши таблицы имеют ограничения уровня столбца. Третье, столбец который ссылается и столбец, на который ссылается, должны иметь одинаковый тип данных, это закон реляционных баз данных.

Если столбцы будут иметь разные типы данных внешний ключ не будет работать. Скорее всего, другие СУБД вам даже не дадут возможность создать такой внешний ключ, но SQLite имеет динамическую типизацию данных, поэтому внешний ключ будет создан.

Далее: конструкция FOREIGEN KEY объявляет о том, что столбец auth_id является ссылкой, а конструкция REFERENCE указывает, что столбец auth_id является ссылкой на столбец id таблицы auth. Таким нехитрым образом мы реализовали связь один ко многим в базе данных SQLite при помощи внешнего ключа.

Давайте теперь добавим строки в таблицу auth нашей базы данных, это можно сделать командой INSERT:

[php]

INSERT INTO auth (id, name, age)

VALUES (1, ‘Джек Лондон’, 40);

INSERT INTO auth (id, name, age)

VALUES (2, ‘Лев Толстой’, 82);

[/php]

Добавлять данные при создании внешнего ключа и использовании FOREIGN KEY в нашем случае удобнее сперва в ту таблицу, на которую идет ссылка. Теперь добавим строки в таблицу books и укажем значения для нашего внешнего ключа.

[php]

INSERT INTO books (id, title, count_page, price, auth_id)

VALUES (1, ‘Белый клык’, 287, 300.00, 1);

INSERT INTO books (id, title, count_page, price, auth_id)

VALUES (2, ‘Война и мир’, 806, 780.00, 2);

[/php]

Поскольку связь один ко многим, по нашей договоренности: один автор может написать много книг, добавим еще одно произведение Джека Лондона:

[php]

INSERT INTO books (id, title, count_page, price, auth_id)

VALUES (3, ‘Зов предков’, 121, 160.00, 1);

[/php]

А теперь давайте посмотрим, что нам дает связь один ко многим, реализованная благодаря внешнему ключу, и как действует ограничение FOREIGEN KEY.

[php]

INSERT INTO books (id, title, count_page, price, auth_id)

VALUES (4, ‘Белый клык’, 121, 160.00, 3);

[/php]

Этот SQL запрос INSERT не будет выполнен в SQLite3, поскольку действует ограничение внешнего ключа, мы получим ошибку: Error: FOREIGN KEY constraint failed. В таблице справочнике с авторами нет значения id = 3, SQLite это проверил и сказал нам, что мы не правы, что у  него есть FOREIGN KEY, который ничего не знает про автора с id = 3. Так обеспечивается целостность данных и так мы избавляемся от аномалии добавления данных в базах данных.

В нашем случае, если мы попробуем передать внешнему ключу значение NULL, то строка в таблицу не добавится, так как действует ограничение уровня столбца NOT NULL, но если бы этого ограничения не было, SQLite спокойно и честно добавил значение NULL, как ссылку в таблицу books, это может нарушить целостность ваших данных, поэтому будьте аккуратны, но это нельзя считать ошибкой, например, есть книги, автор которых неизвестен.

Попробуем изменить данные в таблицах, связанных при помощи внешнего ключа. Для этого воспользуемся командой UPDATE:

[php]

UPDATE books SET auth_id = 4 WHERE title = 'Белый клык';

[/php]

SQLite не дала нам изменить значение auth_id на 4, так как в таблице auth нет авторов с id = 4 и это благодаря наличию внешнего ключа. Ошибка в SQLite будет содержать следующий текст: Error: FOREIGN KEY constraint failed. В дальнейшем мы рассмотрим каскадное обновление данных при помощи команды ON UPDATE.

Нам осталось использовать последнюю команду из группы манипуляции данными, чтобы убедиться, что внешние ключи в SQLite действительно обеспечивают целостность данных в базе данных. Попробуем удалить данные из таблицы, для этого есть команда DELETE.

[php]

DELETE FROM auth WHERE name = 'Лев Толстой';

[/php]

Сделать у нас это не получится, потому что сработает ограничение внешнего ключа, мы не можем удалить данные из таблицы справочника авторов, пока на них ссылаются значения из таблицы книг. Ошибка будет следующей: Error: FOREIGN KEY constraint failed.

Чтобы удалить данные из справочника, мы сперва должны удалить данные из таблицы которая ссылается на справочник, давайте это реализуем.

[php]

DELETE FROM books WHERE auth_id = 2;

DELETE FROM auth WHERE name = 'Лев Толстой';

[/php]

Теперь мы удалили упоминание об авторе в таблице книг, а затем смогли удалить данные об авторе из таблицы auth, по-другому удалить данные из справочника мы не сможем: сперва удаляем данные из ссылающейся таблицы, а потом удаляем данные из таблицы, на которую ссылаемся – это правило внешнего ключа, которое обеспечивает целостность данных и защищает нас от аномалии удаления данных.

Про команду SELECT и то, как она работает с таблицами, которые используют внешние ключи, мы поговорим отдельно, так как это большая и требующая некоторых дополнительных усилий тема.

Сейчас нам важно понимать, что конструкция FOREIGEN KEY REFERENCE организует связь между таблицами базы данных, а также то, что внешний ключ очень тесно связан с понятием нормализации данных в базе данных. FOREIGEN KEY – это ограничения, которые заставляют SQLite проверять выполнение набора определенных правил, которые позволяют избежать аномалии модификации, удаления и обновления данных в таблицах базах данных.

Реализация связи многие ко многим в базах данных SQLite3

В принципе, мы уже сказали всё, что можно о внешних ключах в базах данных SQLite и разобрали особенности работы FOREIGEN KEY и REFERENCE в SQLite3. Раньше мы намеренно упрощали примеры, когда говорили про третью нормальную форму и когда разговаривали про особенности внешних ключей.

Давайте теперь исправим это упущение и будем считать, что одну книгу может написать много авторов и один автор может написать много книг. Реализуем связь многие ко многим в базах данных SQLite3 при помощи внешнего ключа.

Вы, наверное, помните, что для реализации связи многие ко многим нам необходимо создавать третью таблицу, ее можно назвать результирующей, а можно назвать и промежуточной. Мы назовем эту таблицу auth_book.

[php]

PRAGMA foreign_keys=on;

CREATE TABLE books (

Id INTEGER PRIMARY KEY,

title TEXT NOT NULL,

count_page INTEGER NOT NULL CHECK (count_page >0),

price REAL CHECK (price >0)

);

CREATE TABLE auth (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER  CHECK (age >16)

);

CREATE TABLE auth_book (

auth_id INTEGER NOT NULL,

books_id INTEGER NOT NULL,

FOREIGN KEY (auth_id) REFERENCES auth (id)

FOREIGN KEY (books_id) REFERENCES books (id)

);

[/php]

Никаких особых хитростей при реализации связи в базах данных SQLite, да и любых других реляционных базах данных нет. Мы создали третью таблицу, в которой есть два столбца и оба этих столбца являются внешними ключами. Столбец auth_id является ссылкой на таблицу авторов, столбец books_id – это ссылка на таблицу книг. Добавляем данные в таблицы командой INSERT.

[php]

— Сперва добавим несколько строк в таблицу книг

INSERT INTO books (id, title, count_page, price)

VALUES (1, ‘Белый клык’, 287, 300.00);

INSERT INTO books (id, title, count_page, price)

VALUES (2, ‘Война и мир’, 806, 780.00);

— Затем добавим несколько авторов

INSERT INTO auth (id, name, age)

VALUES (1, ‘Джек Лондон’, 40);

INSERT INTO auth (id, name, age)

VALUES (2, ‘Лев Толстой’, 82);

— Мы добавили книги и авторов из предыдущего примера

— Давайте добавим новую книгу

INSERT INTO books (id, title, count_page, price)

VALUES (3, ’12 стульев’, 516, 480.00);

— И добавим авторов, так 12 стульев была написана в соавторстве

— Затем добавим несколько авторов

INSERT INTO auth (id, name, age)

VALUES (3, ‘Илья Ильф’, 39);

INSERT INTO auth (id, name, age)

VALUES (4, ‘Евгений Петров’, 38);

[/php]

Мы подготовили таблицы, которые, на первый взгляд никак не связаны между собой, теперь давайте свяжем их связью многие ко многим, добавив нужные значения в столбцы FOREIGEN KEY таблицы auth_book.

[php]

— Связываем таблицы auth и book

— для этого наполним данными результирующую таблицу

INSERT INTO auth_book (auth_id, books_id)

VALUES (1, 1);

INSERT INTO auth_book (auth_id, books_id)

VALUES (2, 2);

INSERT INTO auth_book (auth_id, books_id)

VALUES (3, 3);

INSERT INTO auth_book (auth_id, books_id)

VALUES (4, 3);

[/php]

Хочу обратить ваше внимание на то, что SQLite и слышать не слышала про Льва Толстого, Джека Лондона и Ильфа с Петровым, она лишь проверяет работоспособность ссылок, ответственность за наполнение таблиц лежит на операторе. При таком подходе вы легко можете сказать, что Джек Лондон написал Войну и мир:

[php]

— Говорим SQLite о том, что Джек Лондон написал Войну и мир

INSERT INTO auth_book (auth_id, books_id)

VALUES (1, 2);

[/php]

С точки логики SQLite никакой ошибки нет, с точки зрения человека – ошибка есть. Внешние ключи никак не могут проверить смысл, который заложен в данных, они лишь проверяют и ограничивают логику работы базы данных. Но не все так страшно, когда мы начнем работать с триггерами, мы посмотрим, как при помощи триггера сделать так, чтобы результирующая таблица при организации связи многие ко многим с помощью FOREIGN KEY заполнялась автоматически, это будет дополнительным уровнем обеспечения целостности данных в базах данных.

Немного о логике внешних ключей в SQLite

Когда мы рассматривали третью и вторую нормальные формы, мы ничего не упоминали про внешние ключи и про то, что они реализуют связи между таблицами. Тогда мы говорили, что справочник является дочерней таблицей, а вот таблица со ссылками (со столбцом FOREIGEN KEY) является родительской. С одной стороны, мы правильно говорим, до тех пор, пока не сталкиваемся со связью многие ко многим, ведь нельзя же результирующую таблицу называть родительской…

Поэтому в базах данных SQLite разработчики четко обозначили терминологию:

  1. Родительская таблица — это таблица, на которую ссылается внешний ключ (FOREIGN KEY).
  2. Дочерняя таблица — это таблица, к которой применяется ограничение внешнего ключа.
  3. Родительский ключ — это один или несколько столбцов родительской таблицы, на которые ссылается внешний ключ (FOREIGN KEY).
  4. Дочерний ключ — это один или несколько столбцов дочерней таблицы, значения которых ограничиваются внешним ключом и которые перечисляются после ключевой фразы REFERENCES.

На самом деле, вам нужно выбрать ту терминологию, которую вам легче воспринимать, мне легче воспринимать так: таблица справочник – дочерняя таблица, таблица, которая ссылается на справочник – родительская таблица.

Важно, чтобы другие вас понимали, благо, язык SQL универсальный и все понимают, что FOREIGN KEY – это внешний ключ, который обеспечивает целостность данных. FOREIGN KEY, как и первичный ключ или PRIMARY KEY, имеет синтаксис, который позволяет записывать данное ограничение, как ограничение уровня столбца.

Возможно, эти записи вам покажутся интересными


Выберете удобный для себя способ, чтобы оставить комментарий

This article has 5 comments

  1. shifu Reply

    Все очень подробно объяснено, большое спасибо

  2. Владимир Reply

    не совсем понял для чего создавать внешний ключ через FOREIGN KEY логика кода добавления и удаления записей никак не меняется если бы я просто добавил столбец с значением некого символа для связи. Мы имеем две разные таблицы никак не связанный между собой кроме условно заполняемого поля которое нужно заполнять в родительской таблице отдельно Update. Зачем тогда именно парится создавать внешний ключ если по сути точно так же могу создать отдельный столбец он у меня будет сохранять первичный ключ второй условно дочерней таблицы и при добавлении в выбранную запись заполнять столбце значением из условно родительской, с внешним ключом мне придется делать все тоже самое только почему то еще дополнительно этот столбец будет числится внешним ключом. Я к тому что не увидел из статьи в чем смысл отдельно парится и именовать внешним ключом через FOREIGN KEY столбец, логика кода абсолютно одинакова если бы я сделал это просто в ручную

    • Кирилл Reply

      Владимир, фактически, единственная задача внешних ключей — это поддержание ссылочной целостности БД. Например, вы изменили значение ключа в родительской таблице, это же значение у вас изменилось в дочерней. Например, у вас есть две таблицы: КЛИЕНТЫ и ЗАКАЗЫ. Условно говоря, имея внешний ключ, СУБД будет понимать какой заказ какому клиенту принадлежит и заботиться о том, чтобы поддерживать эту целостность. Без внешнего ключа эту задачу придется выполнять вам самостоятельно: либо средствами какого-то внешнего скрипта, либо самому запоминать что и кто делал.

  3. Алексей Reply

    Очень понятно, спасибо за труд!

    • Кирилл Reply

      Спасибо за отзыв!

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Loading Disqus Comments ...