Spec-Zone .ru
спецификации, руководства, описания, API
|
MySQL поддерживает внешние ключи, которые позволяют Вам перекрестно ссылаться на связанные данные через таблицы,
и ограничения
внешнего ключа, какая справка сохраняют эти данные распространения непротиворечивыми. Существенный
синтаксис для ограничительного определения внешнего ключа в a CREATE TABLE
или ALTER
TABLE
оператор похож на это:
[CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
, ...) REFERENCEStbl_name
(index_col_name
,...) [ON DELETEreference_option
] [ON UPDATEreference_option
]reference_option
: RESTRICT | CASCADE | SET NULL | NO ACTION
index_name
представляет ID внешнего ключа. Если дано, это игнорируется,
если индексирование для внешнего ключа определяется явно. Иначе, если MySQL создает индексирование для внешнего
ключа, он использует index_name
для имени индекса.
Определения внешних ключей подвергаются следующим условиям:
Отношения внешнего ключа включают родительскую таблицу,
которая содержит центральные значения данных, и дочернюю таблицу с
идентичными значениями, указывающими назад на ее родителя. FOREIGN KEY
пункт определяется в дочерней таблице. Родительские и дочерние таблицы должны использовать тот же самый
механизм хранения. Они не должны быть TEMPORARY
таблицы.
У соответствующих столбцов во внешнем ключе и ключе, на который ссылаются, должны быть подобные типы данных. Размер и знак целочисленных типов должны быть тем же самым. Длина строковых типов не должна быть тем же самым. Для недвоичных (символьных) строковых столбцов набор символов и сопоставление должны быть тем же самым.
MySQL требует, индексирует на внешних ключах и ключах, на которые ссылаются так,
чтобы проверки внешнего ключа могли быть быстрыми и не потребовать сканирования таблицы. В таблице
ссылки должно быть индексирование, где столбцы внешнего ключа перечисляются как первые столбцы в том же самом порядке. Такой индексировать
создается на таблице ссылки автоматически, если она не существует. Это индексирует, мог бы быть тихо
отброшен позже, если Вы создаете, другой индексирует, который может использоваться, чтобы осуществить
ограничение внешнего ключа. index_name
, если дано,
используется как описано ранее.
InnoDB
разрешает внешнему ключу ссылаться, любой
индексирует столбец или группу столбцов. Однако, в таблице, на которую ссылаются, должно быть
индексирование, где столбцы, на которые ссылаются, перечисляются как первые столбцы в том же самом порядке.
Индексируйте префиксы на столбцах внешнего ключа, не поддерживаются. Одно
последствие этого - это BLOB
и TEXT
столбцы не могут быть включены во внешний ключ, потому что
индексирует на тех столбцах, должен всегда включать длину префикса.
Если CONSTRAINT
пункт дается, symbol
symbol
значение, если использующийся, должно быть уникальным в базе данных. Копия symbol
приведет к ошибке, подобной: ОШИБКА 1022 (2300): не Может записать; копируйте ключ в таблице '#sql - 464_1'.
Если пункт не дается, или a symbol
не включается после CONSTRAINT
ключевое слово, имя для ограничения создается автоматически.
InnoDB
в
настоящий момент не поддерживает внешние ключи для таблиц с определяемым пользователем разделением. Это
включает и родительские и дочерние таблицы.
Этот раздел описывает, как справка внешних ключей гарантирует ссылочную целостность.
Для механизмов хранения, поддерживающих внешние ключи, MySQL отклоняет любого INSERT
или UPDATE
работа, которая пытается создать стоимость внешнего ключа в дочерней таблице, если есть не соответствующее
значение ключа-кандидата в родительской таблице.
Когда UPDATE
или DELETE
работа влияет на значение ключа в родительской таблице, у которой есть
соответствие строк в дочерней таблице, результат зависит от справочного действия,
определенного, используя ON UPDATE
и ON DELETE
подпункты FOREIGN KEY
пункт. MySQL поддерживает пять опций относительно действия,
которое будет взято, перечислено здесь:
CASCADE
: Удалите или обновите строку из родительской
таблицы, и автоматически удалите или обновите соответствующие строки в дочерней таблице. Оба ON DELETE CASCADE
и ON UPDATE CASCADE
поддерживаются. Между двумя таблицами не определяйте несколько ON UPDATE
CASCADE
пункты, которые действуют на тот же самый столбец в родительской таблице или в
дочерней таблице.
В настоящий момент каскадные действия внешнего ключа не активируют триггеры.
SET NULL
: Удалите или обновите строку из родительской
таблицы, и установите столбец внешнего ключа или столбцы в дочерней таблице к NULL
. Оба ON DELETE SET NULL
и ON UPDATE SET NULL
пункты поддерживаются.
Если Вы определяете a SET NULL
действие, удостоверьтесь,
что Вы не объявили столбцы в дочерней таблице как NOT NULL
.
RESTRICT
: Отклоняет работу удаления или обновления для
родительской таблицы. Определение RESTRICT
(или NO
ACTION
) то же самое как исключение ON DELETE
или ON UPDATE
пункт.
NO ACTION
: Ключевое слово от стандартного SQL. В MySQL,
эквивалентном RESTRICT
. MySQL Server отклоняет работу удаления или
обновления для родительской таблицы, если есть связанное значение внешнего ключа в таблице, на которую
ссылаются. Некоторые системы баз данных задержали проверки, и NO ACTION
задержанная проверка. В MySQL ограничения внешнего ключа сразу проверяются, таким образом, NO ACTION
то же самое как RESTRICT
.
SET DEFAULT
: Это действие распознается синтаксическим
анализатором MySQL, но InnoDB
табличные определения отклонений, содержащие ON DELETE SET DEFAULT
или ON UPDATE SET
DEFAULT
пункты.
Для ON DELETE
или ON UPDATE
это не определяется,
действие значения по умолчанию всегда RESTRICT
.
MySQL поддерживает ссылки внешнего ключа между одним столбцом и другим в пределах таблицы. (У столбца не может быть ссылки внешнего ключа на себя.) В этих случаях, "записи дочерней таблицы" действительно обращаются к зависимым записям в пределах той же самой таблицы.
Вот простой пример, который имеет отношение parent
и child
таблицы через внешний ключ единственного столбца:
CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB;
Более сложный пример тот, в который a product_order
у таблицы есть внешние ключи
для двух других таблиц. Ссылки внешнего ключа два столбца индексируют в product
таблица. Другие ссылки единственный столбец индексируют в customer
таблица:
CREATE TABLE product ( category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB;CREATE TABLE customer ( id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;CREATE TABLE product_order ( no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), INDEX (customer_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=INNODB;
Можно добавить новое ограничение внешнего ключа к существующей таблице при использовании ALTER TABLE
. Синтаксис, касающийся внешних ключей для этого оператора, показывают
здесь:
ALTER TABLEtbl_name
ADD [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
, ...) REFERENCEStbl_name
(index_col_name
,...) [ON DELETEreference_option
] [ON UPDATEreference_option
]
Внешний ключ может быть сам справочный (обращающийся к той же самой таблице). Когда Вы добавляете ограничение
внешнего ключа к табличному использованию ALTER
TABLE
, не забудьте создавать необходимое, индексирует
сначала.
Можно также использовать ALTER TABLE
отбрасывать внешние ключи, используя синтаксис, показанный здесь:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_symbol
;
Если FOREIGN KEY
пункт включенный a CONSTRAINT
назовите, когда Вы создали внешний ключ, можно обратиться к тому имени, чтобы отбросить внешний ключ. Иначе, fk_symbol
значение сгенерировано внутренне, когда внешний ключ
создается. Чтобы узнать символ оценивают, когда Вы хотите отбросить внешний ключ, используйте a SHOW CREATE TABLE
оператор, как показано здесь:
mysql>SHOW CREATE TABLE ibtest11c\G
*************************** 1. row *************************** Table: ibtest11cCreate Table: CREATE TABLE `ibtest11c` ( `A` int(11) NOT NULL auto_increment, `D` int(11) NOT NULL default '0', `B` varchar(200) NOT NULL default '', `C` varchar(175) default NULL, PRIMARY KEY (`A`,`D`,`B`), KEY `B` (`B`,`C`), KEY `C` (`C`), CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)REFERENCES `ibtest11a` (`A`, `D`)ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)REFERENCES `ibtest11a` (`B`, `C`)ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=INNODB CHARSET=latin11 row in set (0.01 sec)mysql>ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;
Невозможно добавить внешний ключ и отбросить внешний ключ в том же самом ALTER TABLE
оператор. Отдельные операторы требуется для каждой работы.
В MySQL 5.7 сервер запрещает, что изменения к столбцам внешнего ключа с потенциалом вызывают потерю ссылочной
целостности. Обходное решение должно использовать ALTER
TABLE ... DROP FOREIGN KEY
прежде, чем изменить определение столбца и ALTER TABLE ... ADD FOREIGN KEY
позже.
Таблица и идентификаторы столбца в a FOREIGN KEY ... REFERENCES ...
пункт может
быть заключен в кавычки в пределах обратных галочек (`
). Альтернативно, удваивают
кавычки ("
) может использоваться если ANSI_QUOTES
Режим SQL включается. Установка lower_case_table_names
системная переменная также принимается во внимание.
Можно просмотреть определения внешнего ключа дочерней таблицы как часть вывода SHOW CREATE TABLE
оператор:
SHOW CREATE TABLE tbl_name
;
Можно также получить информацию о внешних ключах, запрашивая INFORMATION_SCHEMA.KEY_COLUMN_USAGE
таблица.
Можно счесть информацию о внешних ключах используемой InnoDB
таблицы в INNODB_SYS_FOREIGN
и INNODB_SYS_FOREIGN_COLS
таблицы, также в INFORMATION_SCHEMA
база данных.
mysqldump производит корректные определения таблиц в файле дампа, включая внешние ключи для дочерних таблиц.
Облегчить перезагружать файлы дампа для таблиц, у которых есть отношения внешнего ключа, mysqldump автоматически, включает оператор в вывод дампа, чтобы
установить foreign_key_checks
к 0. Это избегает проблем с таблицами, имеющими необходимость быть перезагруженными в определенном порядке,
когда дамп перезагружается. Также возможно установить эту переменную вручную:
mysql>SET foreign_key_checks = 0;
mysql>SOURCE
mysql>dump_file_name
;SET foreign_key_checks = 1;
Это позволяет Вам импортировать таблицы в любом порядке, если файл дампа содержит таблицы, которые правильно не
упорядочиваются для внешних ключей. Это также ускоряет работу импорта. Установка foreign_key_checks
к 0 может также быть полезным для игнорирования
ограничений внешнего ключа во время LOAD
DATA
и ALTER TABLE
операции. Однако, даже если foreign_key_checks = 0
, MySQL не разрешает создание ограничения внешнего
ключа, где столбец ссылается на несоответствующий тип столбца. Кроме того, если у таблицы есть ограничения
внешнего ключа, ALTER TABLE
не может использоваться, чтобы изменить таблицу, чтобы
использовать другой механизм хранения. Чтобы изменить механизм хранения, следует отбросить любые ограничения
внешнего ключа сначала.
Невозможно выйти DROP TABLE
для таблицы, на которую ссылается a FOREIGN
KEY
ограничение, если Вы не делаете SET foreign_key_checks = 0
. Когда Вы
отбрасываете таблицу, любые ограничения, которые были определены в операторе, используемом, чтобы составить ту
таблицу, также отбрасываются.
Если Вы воссоздаете таблицу, которая была отброшена, у нее должно быть определение, которое соответствует
ограничениям внешнего ключа, ссылающимся на нее. У этого должны быть корректные имена столбцов и типы, и это
должно иметь, индексирует на ключах, на которые ссылаются, как утверждено ранее. Если они не удовлетворяются,
MySQL возвращает Ошибку 1005 и обращается к Ошибке 150 в сообщении об ошибке, что означает, что ограничение
внешнего ключа не было правильно сформировано. Точно так же, если ALTER TABLE
перестал работать из-за Ошибки 150, это означает, что определение
внешнего ключа было бы неправильно сформировано для измененной таблицы.
Для InnoDB
таблицы, можно получить подробное объяснение нового InnoDB
ошибка внешнего ключа в MySQL Server, проверяя вывод SHOW ENGINE INNODB STATUS
.
Для пользователей, знакомых со Стандартом SQL ANSI/ISO, пожалуйста, отметьте что никакой механизм
хранения, включая InnoDB
, распознает или осуществляет MATCH
пункт используется в ограничительных определениях ссылочной целостности. Использование явного MATCH
пункт не будет иметь указанного эффекта, и также вызывает ON DELETE
и ON UPDATE
пункты, которые будут
проигнорированы. По этим причинам, определяя MATCH
должен избежаться.
MATCH
пункт в стандарте SQL управляет как NULL
значения в составном объекте (многократный столбец) внешний ключ
обрабатываются, сравниваясь с первичным ключом. MySQL по существу реализует семантику, определенную MATCH SIMPLE
, которые разрешают внешнему ключу быть всеми или частично NULL
. В этом случае (дочерняя таблица) строке, содержащей такой внешний ключ,
разрешают быть вставленной, и не соответствует строки в (родительской) таблице, на которую ссылаются.
Возможно реализовать другую семантику, используя триггеры.
Дополнительно, MySQL требует, чтобы столбцы, на которые ссылаются, были индексированы по причинам
производительности. Однако, система не осуществляет требование что столбцы, на которые ссылаются, быть UNIQUE
или быть объявленным NOT NULL
. Обработка
ссылок внешнего ключа на групповые ключи или ключи, которые содержат NULL
значения не четко определены для операций такой как UPDATE
или DELETE CASCADE
. Вам советуют использовать внешние ключи та ссылка только
UNIQUE
(включая PRIMARY
) и NOT
NULL
ключи.
Кроме того MySQL не распознает или поддерживает "встроенный REFERENCES
спецификации" (как определено в стандарте SQL), где
ссылки определяются как часть спецификации столбца. MySQL принимает REFERENCES
пункты только когда определено как часть отдельного FOREIGN KEY
спецификация.
Для механизмов хранения, которые не поддерживают внешние ключи (такой как MyISAM
), MySQL Server анализирует и игнорирует спецификации внешнего
ключа.