Иностранный ключ - Foreign key

Проще говоря, иностранный ключ представляет собой набор атрибутов в таблице, который ссылается на первичный ключ другой таблицы. Внешний ключ связывает эти две таблицы. Другими словами: в контексте реляционные базы данных, а иностранный ключ представляет собой набор атрибутов, на которые распространяются определенные ограничения зависимости включения, в частности ограничение, которое кортежи состоящий из внешнего ключа атрибуты в одной связь, R, также должны существовать в каком-то другом (не обязательно отдельном) отношении S, и, кроме того, эти атрибуты также должны быть кандидат ключ в С.[1][2][3] Проще говоря, внешний ключ - это набор атрибутов, которые Рекомендации ключ кандидата. Например, таблица с именем TEAM может иметь атрибут MEMBER_NAME, который представляет собой внешний ключ, ссылающийся на ключ-кандидат PERSON_NAME в таблице PERSON. Так как MEMBER_NAME является внешним ключом, любое значение, существующее как имя члена в TEAM, должно также существовать как имя человека в таблице PERSON; другими словами, каждый член КОМАНДЫ также является ЧЕЛОВЕКОМ.

Резюме

Таблица, содержащая внешний ключ, называется дочерней таблицей, а таблица, содержащая ключ-кандидат, называется ссылочной или родительской таблицей.[4] В реляционном моделировании и реализации базы данных ключ-кандидат - это набор из нуля или более атрибутов, значения которых гарантированно уникальны для каждого кортежа (строки) в отношении. Значение или комбинация значений ключевых атрибутов кандидата для любого кортежа не может быть дублирована для любого другого кортежа в этом отношении.

Поскольку целью внешнего ключа является идентификация конкретной строки таблицы, на которую имеется ссылка, обычно требуется, чтобы внешний ключ был равен ключу-кандидату в некоторой строке первичной таблицы или не имел значения ( НОЛЬ ценить.[2]). Это правило называется ограничение ссылочной целостности между двумя столами.[5]Поскольку нарушение этих ограничений может быть источником многих проблем с базой данных, большинство системы управления базами данных предоставить механизмы, гарантирующие, что каждый ненулевой внешний ключ соответствует строке указанной таблицы.[6][7][8]

Например, рассмотрим базу данных с двумя таблицами: таблица CUSTOMER, которая включает все данные о клиентах, и таблица ORDER, которая включает все заказы клиентов. Предположим, бизнес требует, чтобы каждый заказ относился к одному покупателю. Чтобы отразить это в базе данных, в таблицу ORDER добавляется столбец внешнего ключа (например, CUSTOMERID), который ссылается на первичный ключ КЛИЕНТА (например, ID). Поскольку первичный ключ таблицы должен быть уникальным, и поскольку CUSTOMERID содержит только значения из этого поля первичного ключа, мы можем предположить, что, когда он имеет значение, CUSTOMERID будет идентифицировать конкретного клиента, разместившего заказ. Однако этого больше нельзя предполагать, если таблица ORDER не обновляется при удалении строк таблицы CUSTOMER или изменении столбца ID, и работа с этими таблицами может стать более сложной. Многие базы данных реального мира обходят эту проблему путем «деактивации», а не физического удаления внешних ключей главной таблицы, или с помощью сложных программ обновления, которые изменяют все ссылки на внешний ключ, когда требуется изменение.

Внешние ключи играют важную роль в дизайн базы данных. Одна из важных частей проектирования базы данных - убедиться, что отношения между реальными сущностями отражаются в базе данных ссылками, используя внешние ключи для ссылки из одной таблицы в другую.[9]Еще одна важная часть проектирования базы данных - это нормализация базы данных, в котором таблицы разбиты на части, а внешние ключи позволяют их реконструировать.[10]

Несколько строк в ссылочной (или дочерней) таблице могут ссылаться на одну и ту же строку в ссылочной (или родительской) таблице. В этом случае связь между двумя таблицами называется отношения один ко многим между ссылочной таблицей и ссылочной таблицей.

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

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

Внешний ключ определяется как атрибут или набор атрибутов в отношении, значение которых соответствует первичному ключу в другом отношении. ограничение. Синтаксис для добавления такого ограничения к существующей таблице определен в SQL: 2003 как показано ниже. Отсутствие списка столбцов в предложении REFERENCES означает, что внешний ключ должен ссылаться на первичный ключ указанной таблицы. Аналогичным образом, внешние ключи могут быть определены как часть СОЗДАТЬ ТАБЛИЦУ Оператор SQL.

СОЗДАЙТЕ СТОЛ table_name (   я бы    ЦЕЛОЕ  НАЧАЛЬНЫЙ КЛЮЧ,   col2  ПЕРСОНАЖ РАЗЛИЧНЫЕ(20),   col3  ЦЕЛОЕ,   ...   ИНОСТРАННЫЙ КЛЮЧ(col3)      РЕКОМЕНДАЦИИ other_table(key_col) НА УДАЛИТЬ КАСКАД,   ... )

Если внешний ключ представляет собой только один столбец, столбец можно пометить как таковой, используя следующий синтаксис:

ИЗМЕНИТЬ СТОЛ <стол идентификатор>   ДОБАВИТЬ [ ОГРАНИЧЕНИЕ <ограничение идентификатор> ]      ИНОСТРАННЫЙ КЛЮЧ ( <столбец выражение> {, <столбец выражение>}... )      РЕКОМЕНДАЦИИ <стол идентификатор> [ ( <столбец выражение> {, <столбец выражение>}... ) ]      [ НА ОБНОВИТЬ <ссылочный действие> ]      [ НА УДАЛИТЬ <ссылочный действие> ]

Внешние ключи можно определить с помощью хранимая процедура утверждение.[требуется дальнейшее объяснение ]

sp_foreignkey имя вкладки, pktabname, col1 [, col2] ...  [, col8]
СОЗДАЙТЕ СТОЛ table_name (   я бы    ЦЕЛОЕ  НАЧАЛЬНЫЙ КЛЮЧ,   col2  ПЕРСОНАЖ РАЗЛИЧНЫЕ(20),   col3  ЦЕЛОЕ РЕКОМЕНДАЦИИ other_table(имя_столбца),   ... )
  • имя вкладки: имя таблицы или представления, содержащего определяемый внешний ключ.
  • pktabname: имя таблицы или представления, имеющего первичный ключ, к которому применяется внешний ключ. Первичный ключ уже должен быть определен.
  • col1: имя первого столбца, составляющего внешний ключ. Внешний ключ должен иметь как минимум один столбец и может иметь максимум восемь столбцов.

Ссылочные действия

Основная статья: Ограничение распространения

Поскольку система управления базами данных применяет ссылочные ограничения, он должен гарантировать целостность данных, если строки в ссылочной таблице должны быть удалены (или обновлены). Если зависимые строки в ссылочных таблицах все еще существуют, эти ссылки необходимо учитывать. 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 выполняет проверку перед попыткой выполнения ОБНОВИТЬ или же УДАЛИТЬ утверждение. Оба ссылочных действия действуют одинаково, если проверка ссылочной целостности не удалась: оператор UPDATE или DELETE приведет к ошибке.

Другими словами, когда оператор UPDATE или DELETE выполняется для указанной таблицы с использованием ссылочного действия NO ACTION, СУБД проверяет в конце выполнения оператора, что ни одно из ссылочных отношений не нарушено. Это отличается от RESTRICT, который изначально предполагает, что операция нарушит ограничение. Используя NO ACTION, триггеры или семантика самого оператора может привести к конечному состоянию, в котором никакие отношения внешнего ключа не нарушаются к моменту окончательной проверки ограничения, что позволяет успешно завершить выполнение инструкции.

УСТАНОВИТЬ ПО УМОЛЧАНИЮ, УСТАНОВИТЬ NULL

В целом действия, предпринятые СУБД для SET NULL или SET DEFAULT то же самое для ON DELETE или ON UPDATE: значение затронутых ссылочных атрибутов изменяется на NULL для SET NULL и на заданное значение по умолчанию для SET DEFAULT.

Триггеры

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

Еще одно важное ограничение появляется с изоляция транзакции: ваши изменения в строке могут быть не в состоянии полностью каскадно, потому что на строку ссылаются данные, которые ваша транзакция не может «видеть», и, следовательно, не может каскадироваться. Пример: пока ваша транзакция пытается изменить нумерацию учетной записи клиента, одновременная транзакция пытается создать новый счет для того же клиента; хотя правило CASCADE может исправить все строки счета-фактуры, которые может видеть ваша транзакция, чтобы они соответствовали перенумерованной строке клиента, оно не будет затрагивать другую транзакцию, чтобы исправить там данные; поскольку база данных не может гарантировать согласованность данных при фиксации двух транзакций, одна из них будет вынуждена откатиться (часто в порядке очереди).

СОЗДАЙТЕ СТОЛ учетная запись (acct_num INT, количество ДЕСЯТИЧНЫЙ(10,2));СОЗДАЙТЕ СПУСКОВОЙ КРЮЧОК ins_sum ПЕРЕД ВСТАВЛЯТЬ НА учетная запись     ЗА КАЖДЫЙ РЯД НАБОР @сумма = @сумма + НОВЫЙ.количество;

Пример

В качестве первого примера для иллюстрации внешних ключей предположим, что в базе данных учетных записей есть таблица со счетами-фактурами, и каждый счет-фактура связан с определенным поставщиком. Детали поставщика (например, имя и адрес) хранятся в отдельной таблице; каждому поставщику дается «номер поставщика» для его идентификации. Каждая запись счета-фактуры имеет атрибут, содержащий номер поставщика для этого счета-фактуры. Тогда «номер поставщика» является первичным ключом в таблице «Поставщик». Внешний ключ в таблице Invoices указывает на этот первичный ключ. Реляционная схема следующая. Первичные ключи выделены жирным шрифтом, а внешние ключи - курсивом.

  Поставщик ( Номер поставщика, Имя, адрес, тип) Счета ( Номер счета, Номер поставщика, Текст)

Соответствующие Язык определения данных Заявление выглядит следующим образом.

  СОЗДАЙТЕ СТОЛ Поставщик (     Номер поставщика  ЦЕЛОЕ НЕТ НОЛЬ,     Имя            VARCHAR(20) НЕТ НОЛЬ,     Адрес         VARCHAR(50) НЕТ НОЛЬ,     Тип            VARCHAR(10),     ОГРАНИЧЕНИЕ поставщик_пк НАЧАЛЬНЫЙ КЛЮЧ(Номер поставщика),     ОГРАНИЧЕНИЕ number_value ПРОВЕРИТЬ (Номер поставщика > 0) )  СОЗДАЙТЕ СТОЛ Счета (     Номер счета   ЦЕЛОЕ НЕТ НОЛЬ,     Номер поставщика  ЦЕЛОЕ НЕТ НОЛЬ,     Текст            VARCHAR(4096),     ОГРАНИЧЕНИЕ invoice_pk НАЧАЛЬНЫЙ КЛЮЧ(Номер счета),     ОГРАНИЧЕНИЕ inumber_value ПРОВЕРИТЬ (Номер счета > 0),     ОГРАНИЧЕНИЕ supplier_fk ИНОСТРАННЫЙ КЛЮЧ(Номер поставщика)        РЕКОМЕНДАЦИИ Поставщик(Номер поставщика)        НА ОБНОВИТЬ КАСКАД НА УДАЛИТЬ ОГРАНИЧИВАТЬ )

Смотрите также

Рекомендации

  1. ^ Коронель, Карлос (2010). Системы баз данных: проектирование, внедрение и управление. Независимость KY: Юго-Западный / Cengage Learning. п. 65. ISBN  978-0-538-74884-1.
  2. ^ а б Эльмасри, Рамез (2011). Основы систем баз данных. Эддисон-Уэсли. стр.73 –74. ISBN  978-0-13-608620-8.
  3. ^ Дата, К. Дж. (1996). Руководство по стандарту SQL. Эддисон-Уэсли. п. 206. ISBN  978-0201964264.
  4. ^ Шелдон, Роберт (2005). Начиная с MySQL. Джон Вили и сыновья. С. 119–122. ISBN  0-7645-7950-9.
  5. ^ «Основы базы данных - внешние ключи». Получено 2010-03-13.
  6. ^ MySQL AB (2006). Руководство администратора MySQL и справочник по языку. Самс Паблишинг. п. 40. ISBN  0-672-32870-4.
  7. ^ Пауэлл, Гэвин (2004). Oracle SQL: быстрый старт с примерами. Эльзевир. п.11. КАК В  B008IU3AHY.
  8. ^ Маллинз, Крейг (2012). Руководство разработчика DB2. IBM Press. КАК В  B007Y6K9TK.
  9. ^ Шелдон, Роберт (2005). Начиная с MySQL. Джон Вили и сыновья. п. 156. ISBN  0-7645-7950-9.
  10. ^ Гарсия-Молина, Гектор (2009). Системы баз данных: полная книга. Прентис Холл. стр.93 –95. ISBN  978-0-13-187325-4.

внешняя ссылка