Зачем нужны внешние ключи в базе данных

Зачем нужны внешние ключи в базе данных

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

Общий синтаксис установки внешнего ключа на уровне таблицы:

[CONSTRAINT имя_ограничения] FOREIGN KEY (столбец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE действие] [ON UPDATE действие]

Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE , которые задают действие при удалении и обновлении строки из главной таблицы соответственно.

Например, определим две таблицы и свяжем их посредством внешнего ключа:

CREATE TABLE Customers ( Id INT PRIMARY KEY AUTO_INCREMENT, Age INT, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Phone VARCHAR(20) NOT NULL UNIQUE ); CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

В данном случае определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Таблица Orders через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:

CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT orders_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:

• CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
• SET NULL : при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL . (В этом случае столбец внешнего ключа должен поддерживать установку NULL)
• RESTRICT : отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.
• NO ACTION : то же самое, что и RESTRICT .
• SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.

Каскадное удаление

Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция CASCADE :

CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE );

Подобным образом работает и выражение ON UPDATE CASCADE . При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Однако поскольку первичные ключи изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:

CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );

Ограничения первичных и внешних ключей

Первичные ключи и внешние ключи — это два типа ограничений, которые можно использовать для обеспечения целостности данных в таблицах SQL Server. Это важные объекты базы данных.

Ограничения первичного ключа

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

При указании ограничения первичного ключа для таблицы ядро СУБД обеспечивает уникальность данных путем автоматического создания уникального индекса для столбцов первичного ключа. Этот индекс также обеспечивает быстрый доступ к данным при использовании первичного ключа в запросах. Если ограничение первичного ключа задано более чем для одного столбца, то значения могут дублироваться в пределах одного столбца, но каждое сочетание значений всех столбцов в определении ограничения первичного ключа должно быть уникальным.

Как показано на следующем рисунке, столбцы ProductID и VendorID в таблице Purchasing.ProductVendor формируют составное ограничение первичного ключа для этой таблицы. При этом гарантируется, что каждая строка в таблице ProductVendor имеет уникальное сочетание значений ProductID и VendorID . Это предотвращает вставку повторяющихся строк.

• В таблице возможно наличие только одного ограничения по первичному ключу.
• Первичный ключ не может превышать 16 столбцов и общую длину ключа 900 байт.
• Индекс, созданный ограничением первичного ключа, не может привести к тому, что число индексов таблицы превышает 999 некластеризованных индексов и 1 кластеризованных индексов.
• Если кластеризованный или некластеризованный не указан для ограничения первичного ключа, кластеризованный используется, если в таблице нет кластеризованного индекса.
• Все столбцы с ограничением первичного ключа должны быть определены как не допускающие значения NULL. Если значение NULL не указано, все столбцы, участвующие в ограничении первичного ключа, имеют значение NULL.
• Если первичный ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку.

Foreign Key Constraints

Внешний ключ (FK) — это столбец или сочетание столбцов, которое применяется для принудительного установления связи между данными в двух таблицах с целью контроля данных, которые могут храниться в таблице внешнего ключа. Если один или несколько столбцов, в которых находится первичный ключ для одной таблицы, упоминается в одном или нескольких столбцах другой таблицы, то в ссылке внешнего ключа создается связь между двумя таблицами. Этот столбец становится внешним ключом во второй таблице.

Например, таблица имеет ссылку на внешний ключ к Sales.SalesPerson таблице, Sales.SalesOrderHeader так как между заказами на продажу и продавцами существует логическая связь. Столбец SalesPersonID в таблице SalesOrderHeader соответствует столбцу первичного ключа таблицы SalesPerson . Столбец SalesPersonID в таблице SalesOrderHeader является внешним ключом таблицы SalesPerson . Создав эту связь внешнего ключа, невозможно вставить значение SalesPersonID в SalesOrderHeader таблицу, если она еще не существует в SalesPerson таблице.

Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253. SQL Server 2016 (13.x) увеличивает ограничение числа других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящих ссылок), с 253 до 10 000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения:

• Превышение 253 ссылок на внешние ключи поддерживается только для операций DML DELETE. Операции UPDATE и MERGE не поддерживаются.
• Таблица со ссылкой внешнего ключа на саму себя по-прежнему ограничена 253 ссылками на внешние ключи.
• Более 253 ссылок на внешний ключ в настоящее время недоступны для индексов columnstore, оптимизированных для памяти таблиц, Stretch Database или секционированных таблиц внешнего ключа.

Важно! Stretch Database устарел в SQL Server 2022 (16.x) и База данных SQL Azure. Эта функция будет удалена в будущей версии ядро СУБД. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

Индексы в ограничениях внешнего ключа

В отличие от ограничений первичного ключа, создание ограничения внешнего ключа не автоматически создает соответствующий индекс. Тем не менее, часто возникает необходимость создания индекса для внешнего ключа вручную по следующим причинам:

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

Ссылочная целостность

Главная задача ограничения внешнего ключа состоит в управлении данными, которые могут быть сохранены в таблице внешнего ключа, но это ограничение контролирует также изменение данных в таблице первичного ключа. Например, при удалении строки для менеджера по продажам из таблицы Sales.SalesPerson , идентификатор которого используется в заказах на продажу в таблице Sales.SalesOrderHeader , ссылочная целостность двух таблиц будет нарушена. Заказы на продажу удаленного менеджера в таблице SalesOrderHeader станут недействительными без связи с данными в таблице SalesPerson .

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

Каскадная ссылочная целостность

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

NO ACTION
Ядро СУБД вызывает ошибку, а действие удаления или обновления строки в родительской таблице откатывается.

CASCADE
Соответствующие строки обновляются или удаляются из ссылающейся таблицы, если данная строка обновляется или удаляется из родительской таблицы. КАСКАД нельзя указать, если столбец метки времени является частью внешнего ключа или ключа, на который ссылается ссылка. ON DELETE CASCADE нельзя указать для таблицы с триггером INSTEAD OF DELETE. ON UPDATE CASCADE нельзя указать для таблиц с триггерами INSTEAD OF UPDATE.

SET NULL
Всем значениям, составляющим внешний ключ, присваивается значение NULL, когда обновляется или удаляется соответствующая строка в родительской таблице. Для выполнения этого ограничения внешние ключевые столбцы должны допускать значения NULL. Невозможно указать для таблиц с триггерами INSTEAD OF UPDATE.

SET DEFAULT
Все значения, составляющие внешний ключ, при удалении или обновлении соответствующей строки родительской таблицы устанавливаются в значение по умолчанию. Для выполнения этого ограничения все внешние ключевые столбцы должны иметь определения по умолчанию. Если столбец имеет значение NULL, и нет явного набора значений по умолчанию, NULL становится неявным значением по умолчанию столбца. Невозможно указать для таблиц с триггерами INSTEAD OF UPDATE.

Ключевые слова CASCADE, SET NULL, SET DEFAULT и NO ACTION можно сочетать в таблицах, имеющих взаимные ссылочные связи. Если ядро СУБД обнаруживает NO ACTION, он останавливается и откатывает связанные действия CASCADE, SET NULL и SET DEFAULT. Если инструкция DELETE вызывает сочетание действий CASCADE, SET NULL, SET DEFAULT и NO ACTION, все действия CASCADE, SET NULL и SET DEFAULT применяются перед ядро СУБД проверка для любых действий NO ACTION.

Триггеры и каскадные ссылочные действия

Каскадные ссылочные действия запускают триггеры AFTER UPDATE или AFTER DELETE следующим образом:

• Все каскадные ссылочные действия, прямо вызванные исходными инструкциями DELETE или UPDATE, выполняются первыми.
• Если есть какие-либо триггеры AFTER, определенные для измененных таблиц, эти триггеры запускаются после выполнения всех каскадных действий. Эти триггеры запускаются в порядке, обратном каскадным действиям. Если в одной таблице существует несколько триггеров, они запускаются в случайном порядке, если для таблицы не существует выделенный первый или последний триггер. Этот порядок определяется процедурой sp_settriggerorder.
• Если последовательности каскадных действий происходят из таблицы, которая была непосредственной целью действий DELETE или UPDATE, порядок запуска триггеров этими последовательностями действий не определен. Однако одна последовательность действий всегда запускает все свои триггеры до того, как это начнет делать следующая.
• Триггер AFTER таблицы, являвшейся непосредственной целью действий DELETE или UPDATE, запускается вне зависимости от того, были ли изменены хоть какие-нибудь строки. В этом случае ни на какие другие таблицы каскадирование не влияет.
• Если один из предыдущих триггеров выполняет операции DELETE или UPDATE над другими таблицами, эти операции могут вызывать собственные последовательности каскадных действий. Эти вторичные последовательности действий обрабатываются для каждой операции DELETE или UPDATE после выполнения всех триггеров первичных последовательностей действий. Этот процесс может рекурсивно повторяться для последующих операций DELETE или UPDATE.
• Выполнение операций CREATE, ALTER, DELETE или других операций языка DDL внутри триггеров может привести к запуску триггеров DDL. Это может привести к дальнейшим операциям DELETE или UPDATE, которые начнут дополнительные последовательности каскадных действий и запустят свои триггеры.
• Если в любой конкретной последовательности каскадных ссылочных действий произойдет ошибка, в этой последовательности не будут запущены никакие триггеры AFTER, а для операций DELETE или UPDATE, создаваемых этой последовательностью, будет выполнен откат.
• Таблица с триггером INSTEAD OF не может также содержать предложение REFERENCES, указывающее каскадное действие. Однако триггер AFTER целевой таблицы каскадного действия может выполнить инструкцию INSERT, UPDATE или DELETE для другой таблицы или представления, которое запустит триггер INSTEAD OF для этого объекта.

Следующие шаги

В следующей таблице перечислены общие задачи, связанные с ограничениями первичного ключа и внешнего ключа.

Задача	Статья
Описывает, как создать первичный ключ.	Создание первичных ключей
Описывает, как удалить первичный ключ.	Удаление первичных ключей
Описывает, как изменить первичный ключ.	Изменение первичных ключей
Описывается создание связей внешнего ключа	Создание связей по внешнему ключу
Описывает, как изменить связи внешнего ключа.	Изменение связей по внешнему ключу
Описывает, как удалить связи внешнего ключа.	Удаление связей по внешнему ключу
Описывает, как просматривать свойства внешнего ключа.	Просмотр свойств внешнего ключа
Описывает, как отключить ограничения внешнего ключа для репликации.	Отключение ограничений внешнего ключа для репликации
Описывает, как отключить ограничения внешнего ключа на время выполнения инструкций INSERT и UPDATE.	Отключение ограничений внешнего ключа для инструкций INSERT и UPDATE

FOREIGN KEY в SQL

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

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

SQL поддерживает ссылочную целостность с ограничением FOREIGN KEY. Эта функция должна ограничивать значения, которые можно ввести в базу данных, чтобы заставить внешний ключ и родительский ключ соответствовать принципу ссылочной целостности. Одно из действий ограничения FOREIGN KEY — это отбрасывание значений для полей, ограниченных как внешний ключ, который еще не представлен в родительском ключе. Это ограничение также воздействует на способность изменять или удалять значения родительского ключа.

Ограничение FOREIGN KEY используется в команде CREATE TABLE (или ALTER TABLE (предназначена для модификации структуры таблицы), содержащей поле, которое объявлено внешним ключом. Родительскому ключу дается имя, на которое имеется ссылка внутри ограничения FOREIGN KEY.

Подобно большинству ограничений, оно может быть ограничением таблицы или столбца, в форме таблицы позволяющей использовать многочисленные поля как один внешний ключ. Синтаксис ограничения таблицы FOREIGN KEY: FOREIGN KEY REFERENCES [ ] Первый список столбцов — это список из одного или более столбцов таблицы, которые отделены запятыми и будут созданы или изменены этой командой. Pktable — это таблица содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой. Второй список столбцов — это список столбцов, которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы, т.е.:

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

FOREIGN KEY SQL. Пример 1

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Kod_spec integer NOT NULL, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), FOREIGN KEY (Kod_spec) REFERENCES Spec (Kod_spec) );

При использовании ALTER TABLE вместо CREATE TABLE, для применения ограничения FOREIGN KEY, значения, указываемые во внешнем ключе и родительском ключе, должны быть в состоянии ссылочной целостности. Иначе команда будет отклонена.

Используя ограничение FOREIGN KEY таблицы или столбца, можно не указывать список столбцов родительского ключа, если родительский ключ имеет ограничение PRIMARY KEY. Естественно, в случае ключей со многими полями, порядок столбцов во внешних и первичных ключах должен совпадать, и, в любом случае, принцип совместимости между двумя ключами все еще применим.

FOREIGN KEY. Пример 2

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), Kod_spec integer REFERENCES Spec );

Поддержание ссылочной целостности требует некоторых ограничений на значения, которые могут быть представлены в полях, объявленных как внешний ключ и родительский ключ. Родительский ключ должен быть структурен, чтобы гарантировать, что каждое значение внешнего ключа будет соответствовать одной указанной строке. Это означает, что он (ключ) должен быть уникальным и не содержать никаких пустых значений(NULL).

Этого не достаточно для родительского ключа в случае выполнения такого требования, как при объявлении внешнего ключа. SQL должен быть уверен, что двойные значения или пустые значения (NULL) не были введены в родительский ключ. Следовательно необходимо убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобие ограничения NOT NULL.

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

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

FOREIGN KEY. Пример 3

CREATE TABLE payment ( sh_payout integer, sh_eml integer, date_payout date, summ_payout real, FOREIGN KEY (sh_eml) REFERENCES k_sotr2 (eid) );

В данном примере FOREIGN KEY столбец sh_eml связывается со столбцом eid из таблицы k_sotr2.

Основные ключевые слова, используемые в статье:

foreign key, foreign key mysql, foreign key oracle, foreign key sql, foreign key references

Внешние ключи — что это такое и зачем они нужны в базе данных

Внешние ключи — что это такое и зачем они нужны в базе данных.

Внешний ключ — одно из возможных ограничений реляционной СУБД, служит явного контроля того, что значение на которое ссылается данный столбец, реально присутствует в таблице, на которую ссылаются (см. пример).

• Log in to post comments
• 1911 reads

Fri, 01/22/2021 — 19:50

Внешний ключ

Бен Форта, «SQL, 10 минут на урок», страница 222:

Внешний ключ — это столбец одной таблицы, значениякоторого совпадают со значениями столбца, являющегося первичным ключом другой таблицы.

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(ID), AMOUNT double, PRIMARY KEY (ID) ); insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values( 1, "Mike",25,"Yellow street",20000); insert into CUSTOMERS(ID, NAME, AGE, ADDRESS,SALARY) values( 2, "Jhon",26,"Green street",15000); insert into ORDERS (ID, DATE,CUSTOMER_ID, AMOUNT ) values( 1,20081023103722,1,2); insert into ORDERS (ID, DATE,CUSTOMER_ID, AMOUNT ) values( 2,20081023103722,2,2); select * from CUSTOMERS WHERE * from ORDERS WHERE > Запустить код: https://paiza.io/en/languages/mysql 

• Log in to post comments