InnoDB | Внешние ключи и транзакции

Поддержка внешних ключей и транзакций — это основные отличия InnoDB от других типов таблиц MySQL. Эти механизмы служат для защиты целостности данных.

Прежде чем приступить к работе с типом таблиц InnoDB, стоит проверить, поддерживается ли он в Вашем MySQL. В PhpMyAdmin (PMA) переходим на вкладку "Сервер" -> раздел "Переменные и настройки сервера" и находим переменную "have innodb".
Если "have innodb" содержит значение DISABLED, поддержка InnoDB отключена. Нужно изменить настройки конфигурационного файла – my.cnf (обычно лежит в корне папки mysql). Если в Windows не получается открыть этот файл, можно воспользоваться программой Far. Находим конфигурационный файл, открываем его (клавишей F4), находим следующую строку: "skip-innodb", и проверяем чтобы она была закомментирована. Сохраняем конфигурационный файл (F2) и перезагружаем апач.

Указать тип можно при создании новой таблице, например:

CREATE TABLE users(
userid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
surname VARCHAR(80) NOT NULL,
PRIMARY KEY (userid)
) TYPE=InnoDB;

Можно задать и позже через PMA: вкладка "Операции" -> раздел "Тип таблицы", либо командой:

ALTER TABLE users TYPE=INNODB

Чтобы проще было понять, какие возможности предоставляет механизм внешних ключей, рассмотрим пример. Будем работать с тремя таблицами: пользователей, товаров и заказов. Каждая из таблиц имеет уникальные ключи: userid, prodid и ordid соответственно.

Состав первых двух таблиц понятен. Рассмотрим подробнее таблицу заказов. Каждая запись о заказе содержит в себе информацию о товаре (prodid) и пользователе (userid). Нам важно, чтобы эти значения были действительными. Предположим, пользователь заказал товар, а администратор его удалил, тогда запись о заказе стала "поломанной" (мы даже не узнаем, какой товар был заказан). Чтобы этого избежать, нужно задать правило, согласно которому, множество значений поля userid в таблице заказов (дочерней таблице) не будет превышать множества значений ключа userid в таблице пользователей (родительской таблице). Это правило реализует механизм внешних ключей.

CREATE TABLE users(
userid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
surname VARCHAR(80) NOT NULL,
PRIMARY KEY (userid)
) TYPE=InnoDB;

CREATE TABLE products(
prodid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
PRIMARY KEY (prodid)
) TYPE=InnoDB;

CREATE TABLE `orders`(
ordid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
userid INT(10) UNSIGNED NOT NULL,
prodid INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (ordid),
FOREIGN KEY (userid) REFERENCES users(userid)
ON UPDATE CASCADE
ON DELETE RESTRICT,
FOREIGN KEY (prodid) REFERENCES products(prodid)
ON UPDATE RESTRICT
ON DELETE CASCADE
) TYPE = InnoDB

FOREIGN KEY (`userid`) REFERENCES users(`userid`)

задаёт внешний ключ и буквально означает следующее: назначить полю userid из таблицы orders внешний ключ на поле userid из таблицы users.

Теперь мы не сможем создать запись в таблице orders со значениям полей userid или prodid, которых нет в родительских таблицах. А для операций редактирования и удаления мы можем задать особые правила:

CASCADE – означает распространение действий родительской таблицы на дочерние: то есть, если значение ключа в родительской таблице изменится, то оно автоматически (!без специальных запросов) изменится и в дочерних таблицах.

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

Заполним немного наши таблицы для эксперементов:

INSERT INTO users VALUES (1, 'гриша', 'распутин');
INSERT INTO users VALUES (2, 'мария', 'фёсова');
INSERT INTO users VALUES (3, 'камилла', 'саркар');
INSERT INTO users VALUES (4, 'федос', 'авретьев');

INSERT INTO products VALUES (1, 'кофта');
INSERT INTO products VALUES (2, 'маечка');
INSERT INTO products VALUES (3, 'джинсы');
INSERT INTO products VALUES (4, 'юбка');

INSERT INTO orders VALUES (1, 1, 1);
INSERT INTO orders VALUES (2, 1, 3);
INSERT INTO orders VALUES (3, 2, 2);
INSERT INTO orders VALUES (4, 3, 3);

Итого: Гриша заказал кофту и джинсы, Мария маечку и Камилла также джинсы. Федос ничего не заказывал.

Эксперемент №1. Попробуем внести в таблицу orders запись с номером пользователя, отсутствующим в таблице users:

INSERT INTO orders (userid, prodid) VALUES (11, 2)

Если мы все правильно сделали, MySQL выдаст ошибку.

#1452 - Cannot add or update a child row: a foreign key constraint fails (`innodb/orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE)

Эксперемент №2. Изменим значение ключа в таблице пользователей с правилом ON UPDATE CASCADE и убедимся, что таблица заказов сама изменила значение соответствующего поля userid.

UPDATE users SET userid=55 WHERE userid=1 LIMIT 1;

Эксперемент №3. ON DELETE CASCADE - эта опция означает каскадное удаление: то есть если будет удалена строка из родительсткой таблицы, то автоматически будут удалены строки с соответсвующим внешним ключом из дочерней таблицы. Для нашего случая это означает, что мы исключаем ситуацию покупки товара, которого нет в базе данных: если удалится товар, то и удалятся записи о заказе соответвующего товара. Например, удалим запись из таблицы товаров с ключом prodid=3. Поскольку внешний ключ prodid в таблице заказов использует правило ON DELETE CASCADE, это значит, что автоматически с записью из таблицы products, удалятся записи в таблице orders, в которых поле prodid=3.

DELETE FROM products WHERE prodid=3;

Эксперемент №4. Попробуем обновить значение ключа в таблице товаров, на который ссылается внешний ключ prodid из таблицы orders с правилом ON UPDATE RESTRICT и убедимся, что MySQL выдаст нам ошибку о невозможности завершения этого запроса:

UPDATE products SET prodid=55 WHERE prodid=3 LIMIT 1

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`innodb/orders`, CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`prodid`) REFERENCES `products` (`prodid`) ON DELETE CASCADE)

Эксперемент №5. Если мы попробуем удалить запись из таблице пользователей, значение ключа которого используется в таблице заказов (то есть мы хотим удалить товар, который есть в списках заказа пользователей), причём на внешний ключ которой наложено правило ON DELETE RESTRICT, MySQL выдаст нам ошибку.

DELETE FROM users WHERE userid=1 LIMIT 1

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`innodb/orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE)

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

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

Обычно нам потребовалось бы три запроса к БД, чтобы создать аккаунт. Предположим первые два запроса выполнились удачно, а при третьем запросе нарушилась связь с БД и он "провалился". Таким образом у нас создался "поломаный" аккаунт, без записи в таблице прав. Какие могут быть последствия, можно себе представить.

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

DELETE FROM orders WHERE userid=1;
DELETE FROM orders WHERE userid1=4;

Сначала удалится запись в таблице заказов с пользователем с порядковым номером 1, а затем MySQL выдаст ошибку, потому что в таблице заказов нет столбца с именем "userid1"

#1054 - Unknown column 'userid1' in 'where clause'

Попробуем заключить эти запросы в транзакцию:

START TRANSACTION;
DELETE FROM orders WHERE userid=1;
DELETE FROM orders WHERE userid1=4;
COMMIT;

MySQL по-прежднему выдал ошибку о некорректном названии столбца, вот только из таблицы заказов он не удалил заказы пользователя с порядковым номером 1.

(!) Хочу обратить внимание, что запросы отменятся только в случае ошибки, а не в случае, если количество отработанных строк будет равно нулю.
Источник scabbiaza.net