Как использовать ограничения внешнего ключа MySQL

How Use Mysql Foreign Key Constraints



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

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

Некоторые важные особенности ограничения внешнего ключа объясняются ниже.







  • Тип данных внешнего ключа, который используется в дочерней таблице, должен совпадать с типом данных первичного ключа, который используется в родительской таблице для ссылки на внешний ключ.
  • На любой столбец индекса или несколько столбцов можно ссылаться как на внешний ключ только для таблицы InnoDB.
  • Привилегии ссылок или по крайней мере одна из привилегий операторов SELECT, INSERT, UPDATE и DELETE требуется для создания внешнего ключа.
  • Внешний ключ можно создать двумя способами. Один с помощью оператора CREATE, а другой с помощью оператора ALTER.

Предпосылка:

Перед созданием ограничения внешнего ключа необходимо создать базу данных и родительскую таблицу с первичным ключом. Предположим, что имя базы данных - ‘ библиотека ’И он содержит две родительские таблицы с именем‘ книги ' а также ' заемщик '. Установите соединение с сервером MySQL, используя MySQL client и запустите следующие операторы SQL, чтобы создать базу данных и таблицы.



СОЗДАЙТЕ БАЗА ДАННЫХ библиотека;
ИСПОЛЬЗОВАТЬ библиотека;

СОЗДАЙТЕ СТОЛ книги(
я бы INT НЕТ НУЛЕВОЙ АВТОМАТИЧЕСКОЕ ПРИРАЩЕНИЕ ,
заглавие варчар (пятьдесят) НЕТ НУЛЕВОЙ ,
автор варчар (пятьдесят) НЕТ НУЛЕВОЙ ,
издатель варчар (пятьдесят) НЕТ НУЛЕВОЙ ,
ОСНОВНОЙ КЛЮЧ (я бы)
) ДВИГАТЕЛЬ знак равно INNODB ;

СОЗДАЙТЕ СТОЛ заемщики(
я бы VARCHAR (пятьдесят) НЕТ НУЛЕВОЙ ,
имя варчар (пятьдесят) НЕТ НУЛЕВОЙ ,
адрес варчар (пятьдесят) НЕТ НУЛЕВОЙ ,
Эл. адрес варчар (пятьдесят) НЕТ НУЛЕВОЙ ,
ОСНОВНОЙ КЛЮЧ (я бы)
) ДВИГАТЕЛЬ знак равно INNODB ;



Определите ограничение внешнего ключа с помощью оператора CREATE

Создайте таблицу с именем ‘ book_borrow_info ‘С ограничениями внешнего ключа, выполнив следующую инструкцию. Здесь book_id поле это внешний ключ для этой таблицы, и каждое значение этого поля должно существовать в я бы поле книги стол. книги это родительская таблица и book_borrow_info это дочерняя таблица. Здесь также установлены два ограничения для внешнего ключа. Эти УДАЛИТЬ КАСКАД а также ОБНОВЛЕНИЕ КАСКАДА . Это означает, что если какой-либо первичный ключ будет удален или обновлен из родительской таблицы, то соответствующие записи, относящиеся к дочерней таблице, связанной с внешним ключом, будут удалены или внешний ключ будет обновлен.





СОЗДАЙТЕ СТОЛ book_borrow_info(
заимствовать_ид VARCHAR (пятьдесят),
book_id INT ,
заимствовать_дату ДАТА НЕТ НУЛЕВОЙ ,
Дата возврата ДАТА НЕТ НУЛЕВОЙ ,
положение дел VARCHAR (пятнадцать) НЕТ НУЛЕВОЙ ,
ПОКАЗАТЕЛЬ par_ind(book_id),
ОСНОВНОЙ КЛЮЧ (заимствовать_ид,заимствовать_дату),
ВНЕШНИЙ КЛЮЧ (book_id) ИСПОЛЬЗОВАННАЯ ЛИТЕРАТУРА книги(я бы)
НА УДАЛЯТЬ КАСКАД
НА ОБНОВИТЬ КАСКАД
) ДВИГАТЕЛЬ знак равно INNODB ;

Теперь запустите следующие операторы SQL, чтобы вставить несколько записей в обе таблицы. Первый оператор INSERT вставит четыре записи в книги стол. Четыре значения я бы поле книги table будет 1, 2, 3 и 4 для атрибута автоинкремента. Второй оператор INSERT вставит четыре записи в book_borrow_info на основе я бы значение книги стол.



ВСТАВЛЯТЬ В книги ЦЕННОСТИ
( НУЛЕВОЙ , 'Убить пересмешника', 'Харпер Ли', 'Гранд Сентрал Паблишинг'),
( НУЛЕВОЙ , 'Сто лет одиночества', 'Гарсиа Маркес', 'Лютфи Озкок'),
( НУЛЕВОЙ , 'Путешествие в Индию', 'Форстер, Э.М.', 'Библиотека изображений BBC Hulton'),
( НУЛЕВОЙ , 'Человек невидимка', 'Ральф Эллисон', 'Британская энциклопедия, Inc.');

ВСТАВЛЯТЬ В book_borrow_info ЦЕННОСТИ
('123490', 1, '2020-02-15', '2020-02-25', 'Возвращено'),
('157643', 2, '2020-03-31', '2020-03-10', 'В ожидании'),
('174562', 4, '2020-04-04', '2020-04-24', 'Заимствовано'),
('146788', 3, '2020-04-10', '2020-01-20', 'Заимствовано');

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

ВСТАВЛЯТЬ В book_borrow_info ЦЕННОСТИ
('195684', 10, '2020-04-15', '2020-04-30', 'Возвращено');

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

УДАЛЯТЬ ИЗ книги КУДА я бызнак равно 4;
ВЫБРАТЬ * из книги;
ВЫБРАТЬ * из book_borrow_info;

Определите ограничение внешнего ключа с помощью оператора ALTER

Сначала вставьте несколько записей в заемщики table, и эта таблица будет определена как родительская в следующем ВОЗРАСТ утверждение.

ВСТАВЛЯТЬ В заемщики ЦЕННОСТИ
('123490', Патрик Вуд, '34 West Street LANCASTER LA14 9ZH ', '[электронная почта защищена]'),
('157643', 'Эзра Мартин', '10 The Grove BIRMINGHAM B98 1EU ', '[электронная почта защищена]'),
('174562', 'Джон Иннес Арчи', '55 Main Road LIVERPOOL L2 3OD ', '[электронная почта защищена]'),
('146788', 'Фредерик Хэнсон', '85 Highfield Road SHREWSBURY SY46 3ME ', '[электронная почта защищена]');

Выполните следующее ВОЗРАСТ оператор для установки другого ограничения внешнего ключа для book_borrow_info таблица, чтобы установить отношения с заемщики стол. Здесь, заимствовать_ид определяется как внешний ключ для book_borrow_info стол.

ИЗМЕНИТЬ ТАБЛИЦУ book_borrow_info ДОБАВИТЬ ОГРАНИЧЕНИЕ fk_borrower
ВНЕШНИЙ КЛЮЧ(заимствовать_ид)СПРАВОЧНИКИ заемщиков(я бы)НА УДАЛЕНИЕ КАСКАДА ПРИ ОГРАНИЧЕНИИ ОБНОВЛЕНИЯ;

Теперь вставьте запись в book_borrow_info с действительным заимствовать_ид ценность, которая существует в я бы поле заемщики стол. 157643 значение существует в таблице заемщиков, и следующий оператор INSERT будет успешно выполнен.

ВСТАВЛЯТЬ В book_borrow_info ЦЕННОСТИ
('157643', 1, '2020-03-10', '2020-03-20', 'Возвращено');

Следующая инструкция INSERT сгенерирует сообщение об ошибке, поскольку значение id 195680 не существует в таблице заемщиков.

ВСТАВЛЯТЬ В book_borrow_info ЦЕННОСТИ
('195680', 1, '2020-04-15', '2020-04-30', 'Возвращено');

Заключение:

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