Внешний ключ — это набор атрибутов в таблице , который ссылается на первичный ключ другой таблицы и связывает эти две таблицы. В контексте реляционных баз данных внешний ключ подчиняется ограничению зависимости включения , согласно которому кортежи , состоящие из атрибутов внешнего ключа в одном отношении , R, также должны существовать в каком-то другом (не обязательно отличном) отношении, S; более того, эти атрибуты также должны быть возможными ключами в S. [1] [2] [3]
Другими словами, внешний ключ — это набор атрибутов, ссылающихся на потенциальный ключ. Например, таблица TEAM может иметь атрибут MEMBER_NAME, который является внешним ключом, ссылающимся на потенциальный ключ PERSON_NAME в таблице PERSON. Поскольку MEMBER_NAME является внешним ключом, любое значение, существующее как имя участника в TEAM, должно также существовать как имя человека в таблице PERSON; другими словами, каждый член КОМАНДЫ – это еще и ЧЕЛОВЕК.
Важные моменты, на которые следует обратить внимание: -
Таблица, содержащая внешний ключ, называется дочерней таблицей, а таблица, содержащая потенциальный ключ, называется ссылочной или родительской таблицей. [4] В реляционном моделировании и реализации базы данных потенциальный ключ представляет собой набор из нуля или более атрибутов, значения которых гарантированно уникальны для каждого кортежа (строки) в отношении. Значение или комбинация значений атрибутов-кандидатов ключа для любого кортежа не могут быть дублированы для любого другого кортежа в этом отношении.
Поскольку целью внешнего ключа является идентификация конкретной строки ссылочной таблицы, обычно требуется, чтобы внешний ключ был равен ключу-кандидату в некоторой строке первичной таблицы или не имел значения (значение NULL . [ 2] ). Это правило называется ограничением ссылочной целостности между двумя таблицами. [5] Поскольку нарушения этих ограничений могут быть источником многих проблем с базами данных, большинство систем управления базами данных предоставляют механизмы, гарантирующие, что каждый ненулевой внешний ключ соответствует строке таблицы, на которую ссылаются. [6] [7] [8]
Например, рассмотрим базу данных с двумя таблицами: таблица CUSTOMER, включающая все данные о клиентах, и таблица ORDER, включающая все заказы клиентов. Предположим, бизнес требует, чтобы каждый заказ относился к одному клиенту. Чтобы отразить это в базе данных, в таблицу ORDER добавляется столбец внешнего ключа (например, CUSTOMERID), который ссылается на первичный ключ CUSTOMER (например, ID). Поскольку первичный ключ таблицы должен быть уникальным и поскольку CUSTOMERID содержит только значения из этого поля первичного ключа, мы можем предположить, что, если у него есть значение, CUSTOMERID будет идентифицировать конкретного клиента, разместившего заказ. Однако этого больше нельзя предполагать, если таблица ORDER не обновляется при удалении строк таблицы CUSTOMER или изменении столбца ID, и работа с этими таблицами может стать более сложной. Многие реальные базы данных решают эту проблему путем «инактивации», а не физического удаления внешних ключей главной таблицы или с помощью сложных программ обновления, которые изменяют все ссылки на внешний ключ, когда требуется изменение.
Внешние ключи играют важную роль при проектировании базы данных . Одной из важных частей проектирования базы данных является обеспечение того, чтобы отношения между реальными объектами отражались в базе данных посредством ссылок с использованием внешних ключей для ссылки из одной таблицы в другую. [9] Другой важной частью проектирования базы данных является нормализация базы данных , при которой таблицы разбиваются на части, а внешние ключи позволяют их реконструировать. [10]
Несколько строк в ссылочной (или дочерней) таблице могут ссылаться на одну и ту же строку в ссылочной (или родительской) таблице. В этом случае связь между двумя таблицами называется связью «один ко многим» между ссылающейся таблицей и ссылочной таблицей.
Кроме того, дочерняя и родительская таблица могут фактически быть одной и той же таблицей, т. е. внешний ключ ссылается на одну и ту же таблицу. Такой внешний ключ известен в SQL:2003 как самоссылающийся или рекурсивный внешний ключ. В системах управления базами данных это часто достигается путем связывания первой и второй ссылки на одну и ту же таблицу.
Таблица может иметь несколько внешних ключей, и каждый внешний ключ может иметь отдельную родительскую таблицу. Каждый внешний ключ применяется системой базы данных независимо . Следовательно, каскадные связи между таблицами могут быть установлены с использованием внешних ключей.
Внешний ключ определяется как атрибут или набор атрибутов в отношении, значения которых соответствуют первичному ключу в другом отношении. Синтаксис добавления такого ограничения в существующую таблицу определен в SQL:2003 , как показано ниже. Отсутствие списка столбцов в предложении REFERENCESозначает, что внешний ключ должен ссылаться на первичный ключ указанной таблицы. Аналогично, внешние ключи могут быть определены как часть оператора CREATE TABLESQL.
CREATE TABLE child_table ( col1 INTEGER PRIMARY KEY , col2 CHARACTER VARYING ( 20 ), col3 INTEGER , col4 INTEGER , FOREIGN KEY ( col3 , col4 ) ССЫЛКИ родительская_таблица ( col1 , col2 ) ПРИ УДАЛЕНИИ КАСКАДА ) Если внешний ключ представляет собой только один столбец, столбец можно пометить как таковой, используя следующий синтаксис:
CREATE TABLE child_table ( col1 INTEGER PRIMARY KEY , col2 CHARACTER VARYING ( 20 ), col3 INTEGER , col4 INTEGER REFERENCES родительская_таблица ( col1 ) ПРИ УДАЛЕНИИ КАСКАДА ) Внешние ключи можно определить с помощью оператора хранимой процедуры .
sp_foreignkey child_table , родительская_таблица , col3 , col4 Поскольку система управления базой данных применяет ссылочные ограничения, она должна гарантировать целостность данных, если строки в таблице, на которую есть ссылка, должны быть удалены (или обновлены). Если зависимые строки в ссылающихся таблицах все еще существуют, эти ссылки необходимо учитывать. SQL:2003 определяет 5 различных ссылочных действий , которые должны выполняться в таких случаях:
Всякий раз, когда строки в родительской (ссылочной) таблице удаляются (или обновляются), соответствующие строки дочерней (ссылающейся) таблицы с соответствующим столбцом внешнего ключа также будут удалены (или обновлены). Это называется каскадным удалением (или обновлением).
Значение не может быть обновлено или удалено, если в ссылающейся или дочерней таблице существует строка, которая ссылается на значение в ссылочной таблице.
Аналогично, строку нельзя удалить, пока на нее есть ссылка из ссылающейся или дочерней таблицы.
Чтобы лучше понять RESTRICT (и CASCADE), возможно, будет полезно заметить следующую разницу, которая может быть не сразу очевидна. Ссылочное действие CASCADE изменяет «поведение» самой (дочерней) таблицы, в которой используется слово CASCADE. Например, ON DELETE CASCADE фактически говорит: «Когда указанная строка удаляется из другой таблицы (главной таблицы), удалите также и из меня ». Однако ссылочное действие RESTRICT изменяет «поведение» главной таблицы, а не дочерней таблицы, хотя слово RESTRICT появляется в дочерней таблице, а не в главной таблице! Итак, ON DELETE RESTRICT эффективно говорит: «Когда кто-то пытается удалить строку из другой таблицы (главной таблицы), предотвратите удаление из этой другой таблицы (и, конечно, также не удаляйте ее у меня, но это не главное). здесь)."
RESTRICT не поддерживается Microsoft SQL 2012 и более ранними версиями.
NO ACTION и RESTRICT очень похожи. Основное различие между NO ACTION и RESTRICT заключается в том, что при NO ACTION проверка ссылочной целостности выполняется после попытки изменить таблицу. RESTRICT выполняет проверку перед попыткой выполнения инструкции UPDATE или DELETE . Оба ссылочных действия действуют одинаково, если проверка ссылочной целостности не удалась: оператор UPDATE или DELETE приведет к ошибке.
Другими словами, когда оператор UPDATE или DELETE выполняется над ссылочной таблицей с использованием ссылочного действия NO ACTION, СУБД проверяет в конце выполнения оператора, что ни одно из ссылочных отношений не нарушено. Это отличается от RESTRICT, который изначально предполагает, что операция нарушит ограничение. При использовании NO ACTION триггеры или семантика самого оператора могут привести к конечному состоянию, в котором никакие отношения внешнего ключа не будут нарушены к моменту окончательной проверки ограничения, что позволяет оператору успешно завершиться.
В общем, действие, выполняемое СУБД для SET NULL или SET DEFAULT, одинаково как для ON DELETE, так и для ON UPDATE: значение затронутых ссылочных атрибутов изменяется на NULL для SET NULL и на указанное значение по умолчанию для SET DEFAULT. .
Ссылочные действия обычно реализуются как подразумеваемые триггеры (т. е. триггеры с именами, генерируемыми системой, часто скрытыми). Таким образом, на них распространяются те же ограничения, что и на пользовательские триггеры, и может потребоваться изменить порядок их выполнения относительно других триггеров. обдуманный; в некоторых случаях может возникнуть необходимость заменить ссылочное действие эквивалентным определяемым пользователем триггером, чтобы обеспечить правильный порядок выполнения или обойти ограничения таблицы изменений.
Еще одно важное ограничение возникает при изоляции транзакций : ваши изменения в строке могут не иметь возможности полностью каскадироваться, поскольку на строку ссылаются данные, которые ваша транзакция не может «видеть» и, следовательно, не может каскадировать. Пример: пока ваша транзакция пытается изменить номер счета клиента, одновременная транзакция пытается создать новый счет для того же клиента; хотя правило CASCADE может исправить все строки счетов, которые может видеть ваша транзакция, чтобы обеспечить их соответствие строке с перенумерованным клиентом, оно не будет обращаться к другой транзакции для исправления данных там; поскольку база данных не может гарантировать согласованность данных при фиксации двух транзакций, одна из них будет вынуждена откатиться (часто в порядке очереди).
CREATE TABLE account ( acct_num INT , сумма DECIMAL ( 10 , 2 )); СОЗДАЙТЕ ТРИГГЕР ins_sum ПЕРЕД INSERT ON account ДЛЯ КАЖДОЙ СТРОКИ SET @ sum = @ sum + NEW . количество ; В качестве первого примера, иллюстрирующего внешние ключи, предположим, что в базе данных учетных записей есть таблица со счетами-фактурами, и каждый счет-фактура связан с конкретным поставщиком. Сведения о поставщике (например, имя и адрес) хранятся в отдельной таблице; каждому поставщику присваивается «номер поставщика» для его идентификации. Каждая запись счета имеет атрибут, содержащий номер поставщика для этого счета. Тогда «номер поставщика» является первичным ключом в таблице поставщиков. Внешний ключ в таблице счетов указывает на этот первичный ключ. Реляционная схема следующая. Первичные ключи выделены жирным шрифтом, внешние ключи — курсивом.
Поставщик ( НомерПоставщика , Имя, Адрес) Счет-фактура ( Номер счета-фактуры , Текст, Номер поставщика )
Соответствующее утверждение языка определения данных выглядит следующим образом.
СОЗДАТЬ ТАБЛИЦУ Поставщик ( SupplierNumber INTEGER NOT NULL , Имя VARCHAR ( 20 ) NOT NULL , Адрес VARCHAR ( 50 ) NOT NULL , CONSTRAINT поставщик_pk ПЕРВИЧНЫЙ КЛЮЧ ( SupplierNumber ), ОГРАНИЧЕНИЕ число_значения CHECK ( SupplierNumber > 0 ) ) CREATE TABLE Invoice ( InvoiceNumber INTEGER NOT NULL , Text VARCHAR ( 4096 ), SuppliesNumber INTEGER NOT NULL , CONSTRAINT bill_pk PRIMARY KEY ( InvoiceNumber ), CONSTRAINT inumber_value CHECK ( InvoiceNumber > 0 ), CONSTRAINT поставщик_fk FOREIGN KEY ( SupplierNumber ) ССЫЛКИ Поставщик ( Номер поставщика ) ВКЛ. ОБНОВЛЕНИЕ КАСКАДА ПРИ УДАЛЕНИИ ОГРАНИЧЕНИЯ )