Внешний ключ представляет собой набор атрибутов в таблице , которая относится к первичному ключу другой таблицы. Внешний ключ связывает эти две таблицы. Еще один способ положить его: В контексте реляционных баз данных , внешний ключ представляет собой набор атрибутов субъекта к определенному виду ограничений включения зависимостей, в частности , ограничение , что кортежи , состоящие из внешнего ключа атрибутов в один отношениях , 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 ЦЕЛОЕ ПЕРВИЧНЫЙ КЛЮЧ , col2 СИМВОЛОВ варьируя ( 20 ), col3 INTEGER , COL4 INTEGER , ИНОСТРАННОЙ КЛЮЧ ( col3 , COL4 ) СПИСОК ЛИТЕРАТУРЫ parent_table ( col1 , col2 ) ON DELETE CASCADE )
Если внешний ключ представляет собой только один столбец, столбец можно пометить как таковой, используя следующий синтаксис:
CREATE TABLE child_table ( col1 ЦЕЛОЕ ПЕРВИЧНЫЙ КЛЮЧ , col2 СИМВОЛОВ VARYING ( 20 ), col3 ЦЕЛОЕ , COL4 INTEGER ЛИТЕРАТУРА parent_table ( col1 ) ON DELETE CASCADE )
Внешние ключи можно определить с помощью оператора хранимой процедуры .
sp_foreignkey child_table , parent_table , col3 , COL4
- 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 выполняет проверку перед попыткой выполнения оператора UPDATE или DELETE . Оба ссылочных действия действуют одинаково, если проверка ссылочной целостности не удалась: оператор UPDATE или DELETE приведет к ошибке.
Другими словами, когда оператор UPDATE или DELETE выполняется для указанной таблицы с использованием ссылочного действия NO ACTION, СУБД проверяет в конце выполнения оператора, что ни одно из ссылочных отношений не нарушено. Это отличается от RESTRICT, который изначально предполагает, что операция нарушит ограничение. При использовании NO ACTION триггеры или семантика самого оператора могут привести к конечному состоянию, в котором никакие отношения внешнего ключа не нарушаются к моменту окончательной проверки ограничения, что позволяет успешно завершить выполнение оператора.
УСТАНОВИТЬ NULL, УСТАНОВИТЬ ПО УМОЛЧАНИЮ
В общем, действия, предпринимаемые СУБД для SET NULL или SET DEFAULT, одинаковы как для ON DELETE, так и для ON UPDATE: значение затронутых ссылочных атрибутов изменяется на NULL для SET NULL и на указанное значение по умолчанию для SET DEFAULT. .
Триггеры
Ссылочные действия обычно реализуются как подразумеваемые триггеры (т. Е. Триггеры с именами, сгенерированными системой, часто скрытыми). Таким образом, они подвержены тем же ограничениям, что и определяемые пользователем триггеры, и может потребоваться изменить порядок их выполнения относительно других триггеров. считается; в некоторых случаях может возникнуть необходимость заменить ссылочное действие его эквивалентным пользовательским триггером, чтобы обеспечить надлежащий порядок выполнения или обойти ограничения мутирующей таблицы.
Другое важное ограничение возникает при изоляции транзакции : ваши изменения в строке могут быть не в состоянии полностью каскадно, потому что на строку ссылаются данные, которые ваша транзакция не может «видеть» и, следовательно, не может каскадироваться. Пример: пока ваша транзакция пытается изменить нумерацию учетной записи клиента, одновременная транзакция пытается создать новый счет для того же клиента; хотя правило CASCADE может исправить все строки счета-фактуры, которые может видеть ваша транзакция, чтобы они соответствовали перенумерованной строке клиента, оно не будет затрагивать другую транзакцию, чтобы исправить данные там; поскольку база данных не может гарантировать согласованность данных при фиксации двух транзакций, одна из них будет вынуждена откатиться (часто в порядке очереди).
CREATE TABLE счета ( acct_num INT , количество DECIMAL ( 10 , 2 ));CREATE TRIGGER ins_sum ПЕРЕД ВСТАВИТЬ НА счет ДЛЯ КАЖДОЙ СТРОКЕ SET @ сумма = @ сумма + NEW . сумма ;
Пример
В качестве первого примера для иллюстрации внешних ключей предположим, что в базе данных учетных записей есть таблица со счетами-фактурами, и каждый счет-фактура связан с конкретным поставщиком. Детали поставщика (например, имя и адрес) хранятся в отдельной таблице; каждому поставщику дается «номер поставщика» для его идентификации. Каждая запись счета-фактуры имеет атрибут, содержащий номер поставщика для этого счета-фактуры. Тогда «номер поставщика» является первичным ключом в таблице «Поставщик». Внешний ключ в таблице Invoice указывает на этот первичный ключ. Реляционная схема следующая. Первичные ключи выделены жирным шрифтом, а внешние ключи - курсивом.
Поставщик (Номер поставщика , имя, адрес) Счет-фактура ( InvoiceNumber , Text, SupplierNumber )
Соответствующий оператор языка определения данных выглядит следующим образом.
CREATE TABLE Supplier ( SupplierNumber INTEGER NOT NULL , имя VARCHAR ( 20 ) NOT NULL , адрес 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 ПРОВЕРКИ ( INVOICENUMBER > 0 ), Constraint supplier_fk FOREIGN KEY ( SupplierNumber ) Лит Поставщик ( SupplierNumber ) ON UPDATE CASCADE ON DELETE RESTRICT )
Смотрите также
Рекомендации
- Перейти ↑ Coronel, Carlos (2010). Системы баз данных: проектирование, внедрение и управление . Независимость KY: Юго-Западный / Cengage Learning. п. 65. ISBN 978-0-538-74884-1.
- ^ а б Эльмасри, Рамез (2011). Основы систем баз данных . Эддисон-Уэсли. стр. 73 -74. ISBN 978-0-13-608620-8.
- ^ Дата, CJ (1996). Справочник по стандарту SQL . Эддисон-Уэсли. п. 206. ISBN. 978-0201964264.
- ^ Шелдон, Роберт (2005). Начиная с MySQL . Джон Вили и сыновья. С. 119–122. ISBN 0-7645-7950-9.
- ^ «Основы баз данных - внешние ключи» . Проверено 13 марта 2010 .
- ^ MySQL AB (2006). Руководство администратора MySQL и справочник по языку . Самс Паблишинг. п. 40. ISBN 0-672-32870-4.
- ^ Пауэлл, Гэвин (2004). Oracle SQL: быстрый старт с примерами . Эльзевир. п. 11 . ASIN B008IU3AHY .
- ^ Маллинз, Крейг (2012). Руководство разработчика DB2 . IBM Press. ASIN B007Y6K9TK .
- ^ Шелдон, Роберт (2005). Начиная с MySQL . Джон Вили и сыновья. п. 156. ISBN. 0-7645-7950-9.
- ^ Гарсия-Молина, Гектор (2009). Системы баз данных: полная книга . Прентис Холл. стр. 93 -95. ISBN 978-0-13-187325-4.
Внешние ссылки
- Внешние ключи SQL-99
- Внешние ключи PostgreSQL
- Внешние ключи MySQL
- Первичные ключи FirebirdSQL
- Поддержка SQLite для внешних ключей
- Ограничение_таблицы Microsoft SQL 2012 (Transact-SQL)