Внешний ключ

— Внешний ключ это набор атрибутов в таблице , который ссылается на первичный ключ другой таблицы и связывает эти две таблицы. В контексте реляционных баз данных внешний ключ подчиняется ограничению зависимости включения , согласно которому кортежи, состоящие из атрибутов внешнего ключа в одном отношении , 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 TABLE SQL-заявление.

CREATE TABLE child_table (
  col1 INTEGER PRIMARY KEY,
  col2 CHARACTER VARYING(20),
  col3 INTEGER,
  col4 INTEGER,
  FOREIGN KEY(col3, col4) REFERENCES parent_table(col1, col2) ON DELETE CASCADE
)

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

CREATE TABLE child_table (
  col1 INTEGER PRIMARY KEY,
  col2 CHARACTER VARYING(20),
  col3 INTEGER,
  col4 INTEGER REFERENCES parent_table(col1) ON DELETE CASCADE
)

Внешние ключи можно определить с помощью оператора хранимой процедуры .

sp_foreignkey child_table, parent_table, col3, col4

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, amount DECIMAL(10,2));

CREATE TRIGGER ins_sum BEFORE INSERT ON account
     FOR EACH ROW SET @sum = @sum + NEW.amount;

Пример

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

 Supplier (SupplierNumber, Name, Address)
 Invoice (InvoiceNumber, Text, SupplierNumber)

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

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
)

См. также

Ссылки

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

Внешние ссылки

[1] Коронель, Карлос (2010). Системы баз данных: проектирование, внедрение и управление . Независимость Кентукки: Юго-западное / Cengage Learning. п. 65. ИСБН 978-0-538-74884-1 .

[elmasri-2] Jump up to: ^а ^б Эльмасри, Рамез (2011). Основы систем баз данных . Аддисон-Уэсли. стр. 73–74 . ISBN 978-0-13-608620-8 .

[3] Дата, CJ (1996). Руководство по стандарту SQL . Аддисон-Уэсли. п. 206. ИСБН 978-0201964264 .

[4] Шелдон, Роберт (2005). Начало работы с MySQL . Джон Уайли и сыновья. стр. 119–122. ISBN 0-7645-7950-9 .

[5] «Основы баз данных — внешние ключи» . Проверено 13 марта 2010 г.

[6] MySQL AB (2006). Руководство администратора MySQL и справочник по языку . Издательство Самс. п. 40. ИСБН 0-672-32870-4 .

[7] Пауэлл, Гэвин (2004). Oracle SQL: начало работы с примерами . Эльзевир. п. 11 . АСИН B008IU3AHY .

[8] Маллинз, Крейг (2012). Руководство разработчика DB2 . IBM Пресс. АСИН B007Y6K9TK .

[9] Шелдон, Роберт (2005). Начало работы с MySQL . Джон Уайли и сыновья. п. 156. ИСБН 0-7645-7950-9 .

[10] Гарсия-Молина, Гектор (2009). Системы баз данных: Полная книга . Прентис Холл. стр. 93–95 . ISBN 978-0-13-187325-4 .

[ 1 ]

[ 2 ]

[ 3 ]

[ 4 ]

[ 5 ]

[ 6 ]

[ 7 ]

[ 8 ]

[ 9 ]

[ 10 ]

v т и Системы управления базами данных
Типы	Объектно-ориентированный сравнение Реляционный список сравнение Ключ-значение Столбцово-ориентированный список Документоориентированный Ширококолоночный магазин График NoSQL НьюSQL В памяти список Мультимодель сравнение Облако База данных на основе блокчейна
Концепции	База данных КИСЛОТА Аксиомы Армстронга 12 правил Кодда Теорема CAP CRUD Нулевой Кандидатский ключ Внешний ключ Superkey Суррогатный ключ Уникальный ключ
Объекты	Связь стол столбец ряд Вид Сделка Журнал транзакций Курок Индекс Хранимая процедура Курсор Раздел
Компоненты	Управление параллелизмом Словарь данных JDBC XQJ ОДБК Язык запросов Оптимизатор запросов Система переписывания запросов План запроса
Функции	Администрация Оптимизация запросов Репликация Шардинг
Связанные темы	Модели баз данных Нормализация базы данных Хранение базы данных Распределенная база данных Система федеративных баз данных Ссылочная целостность Реляционная алгебра Реляционное исчисление Реляционная модель Объектно-реляционная база данных Обработка транзакций
Категория Контур ВикиПроект