Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.
Часть 11.4: Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В реляционных базах данных, вернее, в реляционных СУБД есть множество приемов, которые позволяют обеспечить целостность данных, мы уже разобрались с ограничением уровня столбца и первичным ключом, который является одновременно и индексом таблицы базы данных, позволяющим значительно ускорить выборку данных из базы данных, и правилом, которое четко выполняет SQLite. В этой записи мы поговорим про еще одно ограничение уровня таблиц: внешний ключ или FOREIGN KEY.
Внешние ключи в базах данных 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 и его конструкции: FOREIGEN KEY и REFEERENCE. Обратите внимание: когда вы связываете таблицы при помощи внешнего ключа одна таблица является родительской, а вторая таблица является дочерней. Внешний ключ всегда ссылается на родительскую таблиц, другими словами конструкция FOREIGN KEY и REFERENCE указывается в дочерней таблице.
Подведем итог: внешний ключ в базах данных SQLite необходим для реализации связей между таблицами. FOREIGEN KEY позволяет реализовывать связи между таблицами в базах данных. Конструкция REFERENCE используется для указания ссылки на родительскую таблицу. Внешний ключ обеспечивает целостность данных между двумя таблицами и необходим для нормализации базы данных. Вторая нормальная форма и третья нормальная форма не могут быть реализованы без внешнего ключа. Вернее будет сказать: мы можем организовать связи между таблицами без внешнего ключа, но проверка правил при этом выполняться не будет.
Давайте рассмотрим несколько практических примеров реализации внешнего ключа в базах данных SQLite. Мы реализуем два типа связи: связь один ко многим и связь многие ко многим.
Реализация связи один ко многим в базах данных SQLite. Пример связи один ко многим и FOREIGEN KEY в SQLite
Давайте реализуем связь один ко многим при помощи внешнего ключа, для этого воспользуемся конструкциями FORIGEN KEY и REFERENCE. Мы будем связывать при помощи внешнего ключа две таблицы: таблицу авторов и таблицу книг, поэтому давайте договоримся о допущение, что один автор может написать много книг, но у книги может быть только один автор.
Чтобы реализовать связь один ко многим, нам нужно воспользоваться конструкцией FOREIGEN KEY и REFERENCE при создании таблицы при помощи команды CREATE.
Sysadminium
База знаний системного администратора
Первичный и внешний ключ SQL
Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.
Теория
Первичный ключ это одно или несколько полей в таблице. Он необходим для уникальной идентификации любой строки. Первичный ключ накладывает некоторые ограничения:
К первичному ключу предъявляют следующее требование:
Первичный ключ может быть:
Я сам не имею большого опыта работы с SQL, но в книгах пишут что лучше использовать естественный первичный ключ. Почему именно так, я пока ответить не смогу.
Связь между таблицами
Первостепенная задача первичного ключа – это уникальная идентификация каждой строки. Но первичный ключ может решить ещё одну задачу. В базе данных есть возможность связывания нескольких таблиц. Для такой связи используют первичный и внешний ключ sql. В одной из таблиц создают внешний ключ, который ссылается на поля другой таблицы. Но внешний ключ не может ссылаться на любые поля другой таблицы, а может ссылаться только на определённые:
Например, у вас есть таблица “Ученики” (pupils) и выглядит она следующим образом:
| ФИО full_name | Возраст age | Класс class |
| Иванов Иван Иванович | 15 | 9А |
| Сумкин Фёдор Андреевич | 15 | 9А |
| Петров Алексей Николаевич | 14 | 8Б |
| Булгаков Александр Геннадьевич | 14 | 8Б |
Таблица pupils
И есть таблица “Успеваемость” (evaluations):
| Предмет item | ФИО full_name | Оценка evaluation |
| Русский язык | Иванов Иван Иванович | 4 |
| Русский язык | Петров Алексей Николаевич | 5 |
| Математика | Булгаков Александр Геннадьевич | 3 |
| Литература | Сумкин Фёдор Андреевич | 5 |
Таблица evaluations
В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице “Успеваемость” не может содержаться ФИО, которого нет в таблице “ Ученики“. Ведь нельзя поставить ученику оценку, которого не существует.
Первичным ключом в нашем случае может выступать поле “ФИО” в таблице “ Ученики“. А внешним ключом будет “ФИО” в таблице “Успеваемость“. При этом, если мы удаляем запись о каком-то ученике из таблицы “Ученики“, то все его оценки тоже должны удалиться из таблицы “Успеваемость“.
Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:
Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.
Практика
Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:
Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.
Вывод команды \d нам показал, что у нас в таблице есть первичный ключ. А также первичный ключ сделал два ограничения:
Индекс в свою очередь наложил ещё одно ограничение – записи в поле full_name должны быть уникальны.
Следующим шагом создадим таблицу evaluations:
В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.
Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.
Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.
Заполнение таблиц и работа с ними
Заполним таблицу “pupils“:
Заполним таблицу “evaluations“:
А теперь попробуем поставить оценку не существующему ученику:
Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.
Теперь удалим какого-нибудь ученика из таблицы pupils:
И посмотрим на строки в таблице evaluations:
Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.
Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:
Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.
Составной первичный ключ
Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.
Давайте удалим наши таблички и создадим их заново, но теперь создадим их используя составной первичный ключ:
Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.
Теперь посмотрим на структуры этих таблиц:
Первичный ключ в таблице pupils уже состоит из двух полей, поэтому внешний ключ ссылается на эти два поля.
Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:
И также по второй таблице:
Удаление таблиц
Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:
Поэтому удалим наши таблицы в следующем порядке:
Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:
Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.
Создание связи в уже существующих таблицах
Выше я постоянно создавал первичный и внешний ключи при создании таблицы. Но их можно создавать и для существующих таблиц.
Вначале удалим оставшуюся таблицу:
И сделаем таблицы без ключей:
Теперь создадим первичный ключ в таблице pupils:
И создадим внешний ключ в таблице evaluations:
Посмотрим что у нас получилось:
В этой статье я рассказал про первичный и внешний ключ sql. А также продемонстрировал, как можно создать связанные между собой таблицы и как создать связь между уже существующими таблицами. Вы узнали, какие ограничения накладывает первичный ключ и какие задачи он решает. И вдобавок, какие требования предъявляются к нему. Вместе с тем я показал вам как работать с составным первичным ключом.
Дополнительно про первичный и внешний ключ sql можете почитать тут.
Создание связей по внешнему ключу
В этой статье описывается, как создать связи внешнего ключа в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL. Связь создается между двумя таблицами, чтобы связать строки одной таблицы со строками другой.
Разрешения
Создание новой таблицы с внешним ключом требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.
Создание внешнего ключа в существующей таблице требует разрешения ALTER на таблицу.
Пределы и ограничения
Ограничение внешнего ключа не обязательно должно быть связано только с ограничением первичного ключа в другой таблице. Внешние ключи также могут быть определены, чтобы ссылаться на столбцы ограничения UNIQUE в другой таблице.
Если столбцу, имеющему ограничение внешнего ключа, задается значение, отличное от NULL, такое же значение должно существовать и в указываемом столбце. В противном случае будет возвращено сообщение о нарушении внешнего ключа. Для обеспечения проверки всех значений сложного ограничения внешнего ключа задайте параметр NOT NULL для всех столбцов, участвующих в индексе.
Ограничения FOREIGN KEY могут ссылаться только на таблицы в пределах той же базы данных на том же сервере. Межбазовую ссылочную целостность необходимо реализовать посредством триггеров. Дополнительные сведения см. в статье об инструкции CREATE TRIGGER.
Ограничения FOREIGN KEY могут ссылаться на другие столбцы той же таблицы и считаются ссылками на себя.
Ограничение FOREIGN KEY, определенное на уровне столбцов, может содержать только один ссылочный столбец. Этот столбец должен принадлежать к тому же типу данных, что и столбец, для которого определяется ограничение.
Ограничение FOREIGN KEY, определенное на уровне таблицы, должно содержать такое же число ссылочных столбцов, какое содержится в списке столбцов в ограничении. Тип данных каждого ссылочного столбца должен также совпадать с типом соответствующего столбца в списке столбцов.
Компонент Database Engine не имеет предопределенного ограничения на число ограничений FOREIGN KEY, которые могут содержаться в таблице, ссылающейся на другие таблицы. Компонент Database Engine также не ограничивает число ограничений FOREIGN KEY, принадлежащих другим таблицам, которые ссылаются на определенную таблицу. Но фактическое количество используемых ограничений FOREIGN KEY ограничивается конфигурацией оборудования, базы данных и приложения. Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253. SQL Server 2016 (13.x); и последующие версии увеличивает ограничение на количество других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящие ссылки), с 253 до 10 000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения:
Ограничения FOREIGN KEY не применяются к временным таблицам.
Если внешний ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку. Дополнительные сведения об определяемых пользователем типах данных CLR см. в разделе Определяемые пользователем типы данных CLR.
Создание связи по внешнему ключу в конструкторе таблиц
Использование SQL Server Management Studio
В обозревателе объектов щелкните правой кнопкой мыши таблицу, которая будет содержать внешний ключ для связи, и выберите пункт Конструктор.
В меню конструктора таблиц выберите Связи.
В диалоговом окне Связи внешнего ключа нажмите кнопку Добавить.
Выберите нужную связь в списке Выбранные связи.
Выберите Спецификация таблиц и столбцов в сетке справа и нажмите кнопку с многоточием ( … ) справа от свойства.
В диалоговом окне Таблицы и столбы в раскрывающемся списке Первичный ключ выберите таблицу, которая будет находиться на стороне первичного ключа связи.
В сетке внизу выберите столбцы, составляющие первичный ключ таблицы. В соседней ячейке сетки справа от каждого столбца выберите соответствующий столбец внешнего ключа таблицы внешнего ключа.
Закройте окно конструктора таблиц и сохраните внесенные изменения, чтобы изменения связи внешнего ключа вступили в силу.
SQLite foreign key
В заданиях 11 раздела создается база данных с двумя таблицами: dhcp и switches.
Таблица dhcp (показана часть записей):
| mac | ip | vlan | interface | switch |
|---|---|---|---|---|
| 00:07:BC:3F:A6:50 | 10.1.10.6 | 10 | FastEthernet0/3 | sw1 |
| 00:09:BC:3F:A6:50 | 192.168.10 | 1 | FastEthernet0/7 | sw1 |
| 00:A9:BB:3D:D6:58 | 10.1.10.20 | 10 | FastEthernet0/7 | sw2 |
| 00:B4:A3:3E:5B:69 | 10.1.5.20 | 5 | FastEthernet0/5 | sw2 |
| 00:E9:BC:3F:A6:50 | 100.1.1.6 | 3 | FastEthernet0/2 | sw3 |
| hostname | location |
|---|---|
| sw1 | London, 21 New Globe Walk |
| sw2 | London, 21 New Globe Walk |
| sw3 | London, 21 New Globe Walk |
По смыслу заданий столбец switch в таблице dhcp указывает на коммутатор, на котором была найдена запись. При этом, в таблице switches указан соответствующий коммутатор и его расположение. То есть, между таблицами есть некая связь.
В SQL есть возможность описывать подобные связи между таблицами. Например, можно сделать так, что при удалении коммутатора из таблицы switches будут удаляться все записи таблицы dhcp, у которых в столбце switch указан этот коммутатор.
Связь между таблицами создается с помощью внешнего ключа (foreign key).
Внешний ключ указывает каким образом связаны таблицы в базе данных, а также контролирует изменение данных.
Пример создания внешнего ключа (файл dhcp_snooping_schema_ver1.sql):
При такой настройке столбец switch в таблице dhcp является внешним ключом. Он указывает на столбец hostname в таблице switches.
Как правило, внешний ключ ссылается на primary key, но может также ссылаться на поле с ограничением UNIQUE.
Таблица switches, в данном случае, называется родительской по отношению к таблице dhcp.
Подготовка
Для того чтобы посмотреть на внешний ключ в действии, надо добавить данные в таблицы.
Для начала надо создать таблицы:
После выполнения команд выше, надо заполнить таблицы. Это можно сделать импортировав данные из подготовленных CSV файлов:
SQLite Foreign Key Support
Overview
В этом документе описывается поддержка ограничений внешнего ключа SQL, представленная в SQLite версии 3.6.19 (2009-10-14).
Первый раздел представляет концепцию внешнего ключа SQL на примере и определяет терминологию, используемую в оставшейся части документа. В разделе 2 описаны шаги, которые должно предпринять приложение, чтобы включить ограничения внешнего ключа в SQLite (по умолчанию он отключен). В следующем разделе, разделе 3, описаны индексы, которые пользователь должен создать, чтобы использовать ограничения внешнего ключа, и индексы, которые должны быть созданы для эффективного функционирования ограничений внешнего ключа. В разделе 4 описаны расширенные функции, связанные с внешним ключом, поддерживаемые SQLite, а в разделе 5 описывается способ улучшения команд ALTER и DROP TABLE для поддержки ограничений внешнего ключа. Наконец, в разделе 6 перечислены недостающие функции и ограничения текущей реализации.
1.Введение в ограничения по иностранным ключам
Иностранные ключевые ограничения SQL используются для обеспечения соблюдения «существующих» отношений между таблицами.Например,рассмотрим схему БД,созданную с помощью следующих команд SQL:
Одним из решений является добавление ограничения по внешнему ключу SQL в схему базы данных,чтобы усилить связь между artist and track Стол.Для этого определение иностранного ключа может быть добавлено путем изменения формулировки декларации track таблицу ниже:
Таким образом,ограничение накладывается на SQLite.Попытка вставить строку в track таблица,которая не соответствует ни одной строке в artist таблица будет неудачной,также как и попытка удалить строку из таблицы artist таблица,когда существуют зависимые строки в track Таблица Существует одно исключение:если столбец с посторонним ключом в track таблица NULL,тогда нет соответствующей записи в artist требуется столик.Выражение в SQL,это означает,что для каждой строки в track Таблица,следующее выражение оценивается как истинное:
Следующая сессия командной строки SQLite иллюстрирует эффект ограничения по внешнему ключу,добавленному к track table:
Как и следовало ожидать,невозможно манипулировать базой данных до состояния,нарушающего ограничение по внешнему ключу,удаляя или обновляя строки в artist И стол тоже:
SQLite использует следующую терминологию:
Ключ ребенка является столбец или набор столбцов в таблице ребенка, которые сдерживается ограничением внешнего ключа и которые удерживают положение ЛИТЕРАТУРЫ.
Ограничение постороннего ключа удовлетворяется,если для каждой строки в дочерней таблице либо один или несколько столбцов дочернего ключа NULL,либо существует строка в родительской таблице,для которой каждый столбец родительского ключа содержит значение,равное значению в связанном с ним столбце дочернего ключа.
При сравнении текстовых значений всегда используется последовательность сортировки, связанная с родительским ключевым столбцом.
2.Поддержка иностранных ключей
Совет: Если команда PRAGMA foreign_keys не возвращает данных вместо одной строки, содержащей «0» или «1», то используемая вами версия SQLite не поддерживает внешние ключи (либо потому, что она старше 3.6.19, либо потому что он был скомпилирован с определением SQLITE_OMIT_FOREIGN_KEY или SQLITE_OMIT_TRIGGER ).
Невозможно включить или отключить ограничения внешнего ключа в середине транзакции с несколькими операторами (когда SQLite не находится в режиме автоматической фиксации ). Попытка сделать это не вернет ошибку; это просто не действует.
3.Требуемые и предполагаемые индексы базы данных
Обычно родительский ключ ограничения по внешнему ключу является первичным ключом в родительской таблице.Если они не являются первичным ключом,то столбцы родительского ключа должны совместно подчиняться ограничению UNIQUE или иметь UNIQUE-индекс.Если столбцы родительского ключа имеют UNIQUE-индекс,то этот индекс должен использовать последовательности колонок,которые заданы в операторе CREATE TABLE для родительской таблицы.Например,
Если схема БД содержит ошибки с посторонними ключами,для идентификации которых требуется просмотреть более одного определения таблиц,то при создании таблиц эти ошибки не обнаруживаются.Вместо этого такие ошибки не позволяют приложению подготовить SQL-запросы,которые изменяют содержимое дочерних или родительских таблиц таким образом,что в них используются посторонние ключи.Ошибки,сообщаемые при изменении содержимого,являются «DML-ошибками»,а ошибки,сообщаемые при изменении схемы,являются «DDL-ошибками».Другими словами,неправильно настроенные ограничения по внешним ключам,требующие рассмотрения как дочерних,так и родительских таблиц,являются ошибками DML.Англоязычное сообщение об ошибках DML с иностранными ключами обычно является «несоответствием иностранного ключа»,но может быть и «нет такой таблицы»,если родительская таблица не существует.Ошибки DML иностранного ключа сообщаются,если:
Последняя приведенная выше пуля проиллюстрирована следующим образом:
Напротив, если ошибки внешнего ключа можно распознать, просто взглянув на определение дочерней таблицы и не обращаясь к определению родительской таблицы, то оператор CREATE TABLE для дочерней таблицы завершится ошибкой. Поскольку ошибка возникает во время изменения схемы, это ошибка DDL. Об ошибках DDL внешнего ключа сообщается независимо от того, включены ли ограничения внешнего ключа при создании таблицы.
Если этот SELECT возвращает какие-то строки вообще,то SQLite приходит к выводу,что удаление строки из родительской таблицы нарушит ограничение постороннего ключа и возвращает ошибку.Аналогичные запросы могут быть выполнены,если содержимое родительского ключа изменено или в родительскую таблицу вставлена новая строка.Если эти запросы не могут использовать индекс,они вынуждены выполнять линейное сканирование всей дочерней таблицы.В нетривиальной базе данных это может оказаться непомерно дорогостоящим.
Таким образом,в большинстве реальных систем индекс должен быть создан на дочерних столбцах каждого внешнего ключа-ограничения.Индекс дочернего ключа не обязательно должен быть (и,как правило,не будет)индексом UNIQUE.Возвращаясь к примеру в разделе 1,можно получить полную схему базы данных для эффективной реализации ограничения постороннего ключа:
В приведенном выше блоке для создания ограничения по внешнему ключу используется сокращенная форма.Прикрепление «Переопределения
. Обратитесь к документации CREATE TABLE для получения дополнительных сведений.
4.Усовершенствованные функции ограничения по внешнему ключу
4.1.Ограничения на композитный внешний ключ
Композитное ограничение постороннего ключа-это ограничение,при котором и дочерний,и родительский ключи являются композитными ключами.Например,рассмотрим следующую схему базы данных:
В этой системе каждая запись в таблице композиций должна быть сопоставлена с записью в таблице альбомов с той же комбинацией исполнителя и альбома.
Родительский и детский ключи должны иметь одинаковую кардинальность.В SQLite,если любой из столбцов дочернего ключа (в данном случае songartist и songalbum)имеет NULL,то нет необходимости в соответствующей строке в родительской таблице.
4.2.Отсроченные ограничения в отношении иностранного ключа
Каждое внешнее ключевое ограничение в SQLite классифицируется как немедленное или отложенное.По умолчанию внешние ключевые ограничения являются немедленными.Все представленные на данный момент примеры иностранных ключей относятся к классу непосредственных внешних ограничений.
Если текущий оператор не находится внутри явной транзакции (блок BEGIN / COMMIT / ROLLBACK ), то неявная транзакция фиксируется, как только оператор завершает выполнение. В этом случае отложенные ограничения ведут себя так же, как немедленные ограничения.
Чтобы обозначить иностранный ключ как отложенный,его декларация должна включать следующее положение:
Следующий пример иллюстрирует эффект использования отложенного иностранного ключа.
4.3.Действия по УДАЛЕНИЮ и ОБНОВЛЕНИЮ
Иностранный ключ ON DELETE и пункты ON UPDATE используются для настройки действий,которые происходят при удалении строк из родительской таблицы (ON DELETE),или изменении значений родительского ключа существующих строк (ON UPDATE).Одно ограничение по внешнему ключу может иметь различные настройки действий,которые выполняются при удалении строк из родительской таблицы (ON DELETE и ON UPDATE).Действия внешнего ключа похожи на триггеры по многим параметрам.
Действие ON DELETE и ON UPDATE,связанное с каждым внешним ключом в базе данных SQLite,является одним из действий «NO ACTION»,»RESTRICT»,»SET NULL»,»SET DEFAULT» или «CASCADE».Если действие явно не указано,по умолчанию оно имеет значение «NO ACTION».
БЕЗ ДЕЙСТВИЙ : настройка «НЕТ ДЕЙСТВИЯ» означает только следующее: когда родительский ключ изменяется или удаляется из базы данных, никаких специальных действий не предпринимается.
ОГРАНИЧЕНИЕ : действие «ОГРАНИЧИТЬ» означает, что приложению запрещено удалять (для ON DELETE RESTRICT) или изменять (для ON UPDATE RESTRICT) родительский ключ, если существует один или несколько связанных с ним дочерних ключей. Разница между эффектом действия RESTRICT и обычным принудительным ограничением внешнего ключа заключается в том, что обработка действия RESTRICT происходит, как только поле обновляется, а не в конце текущего оператора, как при немедленном ограничении, или в конце. текущей транзакции, как если бы это было с отложенным ограничением. Даже если ограничение внешнего ключа, к которому он прикреплен, отложено, настройка действия RESTRICT заставляет SQLite немедленно возвращать ошибку, если родительский ключ с зависимыми дочерними ключами удален или изменен.
SET NULL : если настроенным действием является «SET NULL», то при удалении родительского ключа (для ON DELETE SET NULL) или изменении (для ON UPDATE SET NULL) столбцы дочернего ключа всех строк в дочерней таблице, которые были сопоставлены родительскому ключу задаются значения SQL NULL.
УСТАНОВИТЬ ПО УМОЛЧАНИЮ : Действия «УСТАНОВИТЬ ПО УМОЛЧАНИЮ» аналогичны «УСТАНОВИТЬ НУЛЬ», за исключением того, что каждый столбец дочернего ключа настроен так, чтобы содержать значение столбца по умолчанию вместо ПУСТОГО. Обратитесь к документации CREATE TABLE для получения подробной информации о том, как значения по умолчанию присваиваются столбцам таблицы.
КАСКАД : действие «КАСКАД» распространяет операцию удаления или обновления родительского ключа на каждый зависимый дочерний ключ. Для действия «ON DELETE CASCADE» это означает, что каждая строка в дочерней таблице, которая была связана с удаленной родительской строкой, также удаляется. Для действия «ON UPDATE CASCADE» это означает, что значения, хранящиеся в каждом зависимом дочернем ключе, изменяются для соответствия новым значениям родительского ключа.
Например,добавление пункта «ON UPDATE CASCADE» к внешнему ключу,как показано ниже,улучшает примерную схему из раздела 1,чтобы позволить пользователю обновить колонку artistid (родительский ключ ограничения внешнего ключа)без нарушения ссылочной целостности:
Настройка действия ON UPDATE или ON DELETE не означает,что иностранный ключ не должен быть удовлетворен.Например,если действие «ON DELETE SET DEFAULT» сконфигурировано,но в родительской таблице нет строки,соответствующей значениям по умолчанию столбцов дочерних ключей,то удаление родительского ключа при наличии зависимых дочерних ключей все равно приводит к нарушению внешнего ключа.Например:
Всякий раз,когда строка в родительской таблице ограничения постороннего ключа удаляется,или когда изменяются значения,хранящиеся в столбце или столбцах родительского ключа,логическая последовательность событий:
Существует одно важное отличие между действием постороннего ключа ON UPDATE и триггерами SQL.Действие ON UPDATE выполняется только в том случае,если значения родительского ключа изменены таким образом,что новые значения родительского ключа не совпадают со старыми.Например:
5.Команды CREATE,ALTER и DROP TABLE
Команда ALTER TABLE работает по-разному в двух отношениях, когда включены ограничения внешнего ключа:
Если ограничения внешнего ключа включены, когда она подготовлена, команда DROP TABLE выполняет неявное УДАЛЕНИЕ, чтобы удалить все строки из таблицы перед ее удалением. Неявное DELETE не вызывает срабатывания каких-либо триггеров SQL, но может вызывать действия внешнего ключа или нарушения ограничений. Если непосредственное ограничение внешнего ключа нарушается, оператор DROP TABLE завершается ошибкой и таблица не удаляется. Если ограничение отложенного внешнего ключа нарушается, то при попытке пользователя зафиксировать транзакцию выдается сообщение об ошибке, если в этот момент все еще существуют нарушения ограничения внешнего ключа. Любые ошибки «несоответствия внешнего ключа», обнаруженные как часть неявного DELETE, игнорируются.
Эти усовершенствования команд ALTER TABLE и DROP TABLE предназначены для того, чтобы гарантировать, что их нельзя использовать для создания базы данных, содержащей нарушения внешнего ключа, по крайней мере, пока включены ограничения внешнего ключа. Однако есть одно исключение из этого правила. Если родительский ключ не подчиняется ограничениям PRIMARY KEY или UNIQUE, созданным как часть определения родительской таблицы, но подчиняется ограничению UNIQUE в силу индекса, созданного с помощью CREATE INDEXкоманда, то дочерняя таблица может быть заполнена, не вызывая ошибки «несоответствие внешнего ключа». Если индекс UNIQUE удаляется из схемы базы данных, тогда удаляется и сама родительская таблица, об ошибках не сообщается. Однако база данных может оставаться в состоянии, когда дочерняя таблица ограничения внешнего ключа содержит строки, которые не относятся к какой-либо строке родительской таблицы. Этого случая можно избежать, если все родительские ключи в схеме базы данных ограничены ограничениями PRIMARY KEY или UNIQUE, добавленными как часть определения родительской таблицы, а не внешними индексами UNIQUE.
Свойства команд DROP TABLE и ALTER TABLE, описанные выше, применяются только в том случае, если включены внешние ключи. Если пользователь считает их нежелательными, то обходной путь заключается в использовании PRAGMA foreign_keys для отключения ограничений внешнего ключа перед выполнением команды DROP или ALTER TABLE. Конечно, хотя ограничения внешнего ключа отключены, ничто не мешает пользователю нарушить ограничения внешнего ключа и тем самым создать внутренне противоречивую базу данных.
6.Пределы и неподдерживаемые функции
В этом разделе перечислены некоторые ограничения и опущенные функции,которые нигде не упоминаются.
Предложение MATCH не поддерживается. Согласно SQL92, предложение MATCH может быть присоединено к определению составного внешнего ключа, чтобы изменить способ обработки значений NULL, которые встречаются в дочерних ключах. Если указано «MATCH SIMPLE», то не требуется, чтобы дочерний ключ соответствовал какой-либо строке родительской таблицы, если одно или несколько значений дочернего ключа равны NULL. Если указано «MATCH FULL», то если любое из значений дочернего ключа равно NULL, соответствующая строка в родительской таблице не требуется, но все значения дочерних ключей должны быть NULL. Наконец, если ограничение внешнего ключа объявлено как «MATCH PARTIAL» и одно из значений дочернего ключа равно NULL, должна существовать хотя бы одна строка в родительской таблице, для которой значения дочернего ключа, отличные от NULL, соответствуют значениям родительского ключа.
SQLite разбирает MATCH-опции (т.е.не сообщает об ошибке синтаксиса,если она указана),но не обеспечивает ее выполнение.Все ограничения внешних ключей в SQLite обрабатываются так,как если бы был указан MATCH-шаблон.
Нет поддержки переключения ограничений между отложенным и немедленным режимами. Многие системы позволяют пользователю переключать отдельные ограничения внешнего ключа между отложенным и немедленным режимами во время выполнения (например, с помощью команды Oracle «SET CONSTRAINT»). SQLite не поддерживает это. В SQLite ограничение внешнего ключа постоянно помечается как отложенное или немедленное при создании.
Ограничение рекурсии для действий внешнего ключа. Параметры SQLITE_MAX_TRIGGER_DEPTH и SQLITE_LIMIT_TRIGGER_DEPTH определяют максимально допустимую глубину рекурсии программы триггера. Для целей этих ограничений действия внешнего ключа считаются программами-триггерами. Параметр PRAGMA recursive_triggers не влияет на работу действий внешнего ключа. Отключить рекурсивные действия внешнего ключа невозможно.