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

Привет, посетитель сайта 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Текст комментария: