webew
Войти » Регистрация
 
MySQL/MariaDB

Внешние ключи

25 марта 2008, 14:18
Автор: bur
Что это за зверь интересно?
И в каких случаях имеет смысл их применять?
Добавить комментарий

1234ru

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

Внешние ключи на данный момент работают в InnoDB со всеми вытекающими последствиями, хотя в будущем (кажется, с 6.0) обещают их сделать для всех ENGINE'ов.

Суть работы внешнего ключа можно пояснить на таком примере (в книжке MySQL Certification Guide в главе про внешние ключи также есть поясняющий пример). Пусть у тебя есть таблица покупателей

CREATE TABLE customers (
    userid INT NOT NULL UNIQUE KEY,
    name VARCHAR(255) /* и т.п. */
) ENGINE = InnoDB;


и таблица заказов, где фигурирует id покупателя (т.е. связь по id).

CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cid INT /* id покупателя */
     /* и т.д. */
) ENGINE = InnoDB;


Ты, например, в силу каких-то причин решил удалить некоторых покупателей и не хочешь, чтобы в таблице заказов болтались записи удаленных покупателей.
Можно добиться этого различными способами, один из которых - создание внешних ключей. С помощью внешнего ключа можно связать колонку в таблице customers c колонкой в таблице orders - в данном случае осмысленным будет связать customers.id и orders.cid - так, чтобы при изменении данных в столбце customers.id автоматически обновлялись данные в столбце orders.cid:

ALTER TABLE orders
ADD FOREIGN KEY (cid) /* тут имя столбца дочерней таблицы */
REFERENCES customers(userid) /* тут имя родительской таблицы и за ним имя столбца в скобках */
-- дальше идёт указание того, что делать; можно указать несколько альтернатив
ON UPDATE CASCADE /* для UPDATE и DELETE действие указывается отдельно */
ON DELETE CASCADE ;


CASCADE - значит, повторить действие с родительским столбцом и для дочернего (не наоборот!!), т.е. при UPDATE'ах родительского столбца то же произойдет и со всеми записями для дочернего, если там есть такое значение (т.е. customers.userid изменился с 1 на 3; тогда и orders.cid изменится с 1 на 3 во всех записях, которые содержали значение 1). Если ON DELETE также стоит CASCADE, то при удалении соотв. записи в родительской таблицы удалятся все в дочерней (если удалить из customers запись с userid = 4, то все записи в orders с cid = 4 также удалятся).

Еще можно:
RESTRICT - запретить изменять/удалять записи в родительской таблице
SET NULL - при изменении/удалении записей в родительской таблице выставлять значение соотв. столбца в NULL
NO ACTION - то же самое, что RESTRICT.

Если при создании внешнего ключа для указание действия для какого-либо из событий (ON UPDATE или ON DELETE) опущено, то для этого события ключ автоматически выставляется в RESTRICT.

Также важным следствием создания внешних ключей является то, что НЕЛЬЗЯ вставить в дочернюю таблицу записи, соотв. значения столбцов внешнего ключа которых не содержатся в родительской. Это нужно учитывать в логике приложений (т.е., например, вставлять сначала в родительскую, потом - в дочернюю).
И, в случае необходимости, удалять придется сначала дочернюю таблицу, а потом - родительскую (или же сначала снять ключ).


Более подробную информацию можно найти здесь:

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
То, что не убивает нас, делает нас инвалидами.
25.03.2008, 20:55
Ответить

bur

Отлично объяснил, спасибо! ;-)
26.03.2008, 16:39
Ответить
© 2008—2017 webew.ru, связаться: x собака webew.ru
Сайт использует Flede и соответствует стандартам WAI-WCAG 1.0 на уровне A.
Rambler's Top100

Реклама: