В чем практическая польза FOREIGN KEY в таблицах MySQL?

157

28 марта 2019, 22:10

Не понимаю в чем практическая польза FOREIGN KEY для таблиц. Например, у меня есть три таблицы:

Users {user_id, name, surname}
Orders {order_id, user_id, invoice_id}
Invoices {invoice_id, summ}

Логично использовать user_id и invoice_id как внешние ключи из соответствующих таблиц Users и Invoices. На сколько я понимаю, назначение user_id и invoice_id внешними ключами не позволит мне сделать ошибку и добавить в таблицу Orders заказ с несуществующими значениями user_id и invoice_id.

На это практическая польза FOREIGN KEY заканчивается? У меня по этому поводу сомнения.

Как еще я должен использовать FOREIGN KEY?

Я могу представить, если бы у меня в таблице Orders было поле name, значение которого автоматически обновляется, если изменяется его значение в родительской таблице. В этом бы была польза, но мне кажется не верно создавать дублирующиеся поля в разных таблицах... Поэтому я пытаюсь понять, как используются FOREIGN KEY?

Answer 1

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

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

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

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

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

Пример: есть две таблицы users и emails:

CREATE TABLE users (
  user_id int(11) NOT NULL AUTO_INCREMENT,
  user_name varchar(50) DEFAULT NULL,
  PRIMARY KEY (user_id)
)
CREATE TABLE sys.emails (
  email_id int(11) NOT NULL AUTO_INCREMENT,
  email_address varchar(100) NOT NULL,
  user_id int(11) NOT NULL,
  PRIMARY KEY (email_id)
)

Между ними есть зависимость в виде users.user_id и emails.user_id, и мы хотим что в поле emails.user_id не смогло записываться ничего кроме значения с таблицы users столбец user_id, для этого и создаем ограничение в виде внешнего ключа:

ALTER TABLE emails 
  ADD FOREIGN KEY (user_id) 
  REFERENCES users (user_id) 
  ON DELETE CASCADE 
  ON UPDATE NO ACTION;

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

Answer 2

В MySQL 3.23.44 и выше в таблицах InnoDB осуществляется проверка ограничений целостности внешних ключей

Фактически для соединения двух таблиц внешние ключи не нужны.

Единственное, что MySQL в настоящее время не осуществляет (в типах таблиц, отличных от InnoDB), это проверку (CHECK) что ключи, которые вы используете, действительно существуют в таблице(ах) на которые вы ссылаетесь, и не удаляет автоматически записи из таблиц с определением внешних ключей.