Внешний ключ — это набор атрибутов в таблице , который ссылается на первичный ключ другой таблицы, связывая эти две таблицы. В контексте реляционных баз данных внешний ключ подчиняется ограничению зависимости включения , согласно которому кортежи, состоящие из атрибутов внешнего ключа в одном отношении , R, должны также существовать в некотором другом (не обязательно отдельном) отношении, S; кроме того, эти атрибуты также должны быть потенциальным ключом в S. [1] [2] [3]
Другими словами, внешний ключ — это набор атрибутов, ссылающихся на потенциальный ключ. Например, таблица с именем TEAM может иметь атрибут MEMBER_NAME, который является внешним ключом, ссылающимся на потенциальный ключ PERSON_NAME в таблице PERSON. Поскольку MEMBER_NAME — это внешний ключ, любое значение, существующее как имя члена в TEAM, должно также существовать как имя человека в таблице PERSON; другими словами, каждый член 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 TABLEоператора SQL.
СОЗДАТЬ ТАБЛИЦУ child_table ( col1 ЦЕЛОЕ ЧИСЛО ПЕРВИЧНЫЙ КЛЮЧ , col2 СИМВОЛ ПЕРЕМЕННАЯ ( 20 ), col3 ЦЕЛОЕ ЧИСЛО , col4 ЦЕЛОЕ ЧИСЛО , ВНЕШНИЙ КЛЮЧ ( col3 , col4 ) ССЫЛКИ parent_table ( col1 , col2 ) ПРИ КАСКАДЕ УДАЛЕНИЯ ) Если внешний ключ представляет собой только один столбец, столбец можно пометить как таковой, используя следующий синтаксис:
СОЗДАТЬ ТАБЛИЦУ child_table ( col1 ЦЕЛОЕ ЧИСЛО ПЕРВИЧНЫЙ КЛЮЧ , col2 ПЕРЕМЕННЫЙ СИМВОЛ ( 20 ), col3 ЦЕЛОЕ ЧИСЛО , col4 ЦЕЛОЕ ЧИСЛО ССЫЛКИ parent_table ( col1 ) ПРИ КАСКАДЕ УДАЛЕНИЯ ) Внешние ключи можно определить с помощью оператора хранимой процедуры .
sp_foreignkey child_table , parent_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 , amount DECIMAL ( 10 , 2 )); СОЗДАТЬ ТРИГГЕР ins_sum ПЕРЕД ВСТАВКОЙ В СЧЕТ ДЛЯ КАЖДОГО НАБОРА СТРОК @sum = @sum + NEW . amount ; В качестве первого примера для иллюстрации внешних ключей предположим, что в базе данных счетов есть таблица со счетами-фактурами, и каждый счет-фактура связан с определенным поставщиком. Данные о поставщике (например, имя и адрес) хранятся в отдельной таблице; каждому поставщику присваивается «номер поставщика» для его идентификации. Каждая запись счета-фактуры имеет атрибут, содержащий номер поставщика для этого счета-фактуры. Тогда «номер поставщика» является первичным ключом в таблице Supplier. Внешний ключ в таблице Invoice указывает на этот первичный ключ. Реляционная схема выглядит следующим образом. Первичные ключи выделены жирным шрифтом, а внешние ключи выделены курсивом.
Поставщик ( номер поставщика , имя, адрес) Счет-фактура ( НомерСчета-фактуры , Текст, НомерПоставщика )
Соответствующее утверждение языка определения данных выглядит следующим образом.
CREATE TABLE Supplier ( SupplierNumber INTEGER NOT NULL , Name VARCHAR ( 20 ) NOT NULL , Address VARCHAR ( 50 ) NOT NULL , CONSTRAINT supplier_pk PRIMARY KEY ( SupplierNumber ), CONSTRAINT number_value CHECK ( SupplierNumber > 0 ) ) CREATE TABLE Invoice ( InvoiceNumber INTEGER NOT NULL , Text VARCHAR ( 4096 ), SupplierNumber INTEGER NOT NULL , CONSTRAINT invoice_pk PRIMARY KEY ( InvoiceNumber ), CONSTRAINT inumber_value CHECK ( InvoiceNumber > 0 ), CONSTRAINT supplier_fk FOREIGN KEY ( SupplierNumber ) REFERENCES Supplier ( SupplierNumber ) ON UPDATE CASCADE ON DELETE RESTRICT )