Как использовать внешние ключи MySQL для более быстрой разработки баз данных

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

MySQL — это фантастическая база данных с открытым исходным кодом, используемая многими тысячами веб-приложений. Настольная система MyISAM по умолчанию является одной из самых простых и быстрых, но она не поддерживает внешние ключи (хотя она запланирована для версии 6 ).

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

Что такое иностранные ключи?

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

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

• employee : таблица сотрудников компании, в которой каждому участнику присвоен уникальный идентификатор
• заимствовано : таблица заимствованных книг. Каждая запись будет ссылаться на идентификатор сотрудника заемщика.

Мы определим отношение внешнего ключа между идентификатором сотрудника в обеих таблицах. Это обеспечивает пару преимуществ:

1. Невозможно ввести неверный идентификатор сотрудника в таблицу «заимствовано».
2. Изменения сотрудников обрабатываются MySQL автоматически.

Создание примера базы данных

Наш пример базы данных создается следующим образом:

CREATE DATABASE mydb;
USE mydb;

Теперь мы определим наши две таблицы. Обратите внимание, что InnoDB указан в качестве типа таблицы, и мы также добавим индекс для фамилии сотрудника.

 
CREATE TABLE employee (
	id smallint(5) unsigned NOT NULL,
	firstname varchar(30),
	lastname varchar(30),
	birthdate date,
	PRIMARY KEY (id),
	KEY idx_lastname (lastname)
) ENGINE=InnoDB;

CREATE TABLE borrowed (
	ref int(10) unsigned NOT NULL auto_increment,
	employeeid smallint(5) unsigned NOT NULL,
	book varchar(50),
	PRIMARY KEY (ref)
) ENGINE=InnoDB;

Теперь мы можем указать наш внешний ключ (это может быть обработано в операторе CREATE TABLE, но здесь это показано отдельно):

 
ALTER TABLE borrowed 
ADD CONSTRAINT FK_borrowed 
FOREIGN KEY (employeeid) REFERENCES employee(id) 
ON UPDATE CASCADE
ON DELETE CASCADE;

Это говорит MySQL, что мы хотим изменить заимствованную таблицу, добавив ограничение под названием «FK_borrowed». Столбец employeeid будет ссылаться на столбец id в таблице employee — другими словами, сотрудник должен существовать, прежде чем он сможет одолжить книгу.

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

Добавление данных таблицы

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

сотрудник:

заимствованы:

 
SELECT book FROM borrowed 
JOIN employee ON employee.id=borrowed.employeeid 
WHERE employee.lastname='Smith';

 
UPDATE employee SET id=22 WHERE id=2;

 
DELETE FROM employee WHERE id=1;