Внешний ключ, не работающий в MySQL: Почему я могу ВСТАВИТЬ значение, которое не находится в чужом столбце?

Я создал таблицу в MySQL:

CREATE TABLE actions ( A_id int NOT NULL AUTO_INCREMENT,
type ENUM('rate','report','submit','edit','delete') NOT NULL,
Q_id int NOT NULL,
U_id int NOT NULL,
date DATE NOT NULL,
time TIME NOT NULL,
rate tinyint(1),
PRIMARY KEY (A_id),
CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id),
CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id));

Это создало таблицу, которую я хотел просто отлично (хотя команда "DESCRIBE actions;" показала мне, что внешние ключи были ключами типа MUL, и я не уверен, что это значит). Однако, когда я пытаюсь ввести Q_id или U_id, которого нет в таблицах вопросов или пользователей, MySQL все еще допускает эти значения.

Что я сделал неправильно? Как предотвратить запрет таблицы с внешним ключом принимать недопустимые данные?

ОБНОВЛЕНИЕ 1

Если я добавлю TYPE=InnoDB в конец, я получаю сообщение об ошибке:

ОШИБКА 1005 (HY000): невозможно создать таблицу. /quotes/actions.frm '(errno: 150)

Почему это может случиться?

ОБНОВЛЕНИЕ 2

Мне сказали, что важно обеспечить целостность данных с помощью функциональных внешних ключей, но также и то, что InnoDB не следует использовать с MySQL. Что вы рекомендуете?

Ответ 1

Я бы предположил, что ваш механизм хранения по умолчанию - MyISAM, который игнорирует ограничения внешнего ключа. Он молча принимает объявление внешнего ключа, но не сохраняет ограничение или принуждает его впоследствии.

Однако он неявно создает индекс для столбцов, которые вы указали для внешнего ключа. В MySQL "KEY" является синонимом "INDEX". Это то, что показано на выходе DESCRIBE: индекс, но не ограничение.

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

CREATE TABLE actions (
  A_id int NOT NULL AUTO_INCREMENT,
  ...
  CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id),
  CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id)
) ENGINE=InnoDB;

Я всегда думал, что большая ошибка в части MySQL - молча игнорировать объявления ограничений внешнего ключа. Там нет ошибки или предупреждения, что механизм хранения не поддерживает их.

То же самое верно и для ограничений CHECK. Механизм хранения, используемый с MySQL, не поддерживает ограничения CHECK, но парсер SQL принимает их без жалобы.

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

SHOW ENGINE INNODB STATUS;

Некоторые требования к внешним ключам InnoDB:

Ссылка на таблицу также должна быть InnoDB.
Таблица ссылок должна иметь индекс и первичный ключ.
Типы данных SQL столбца FK и столбец PK, указанные в PK, должны быть одинаковыми. Например, INT не соответствует BIGINT или INT UNSIGNED.

Вы можете изменить механизм хранения таблицы, в которой есть данные:

ALTER TABLE actions ENGINE=InnoDB;

Это эффективно копирует всю таблицу MyISAM в таблицу InnoDB, а затем, когда это удается, она удаляет таблицу MyISAM и переименовывает новую таблицу InnoDB в имя прежней таблицы MyISAM. Это называется "реструктуризацией таблицы", и это может занять много времени, в зависимости от того, сколько данных находится в таблице. Реструктурирование таблицы происходит во время ALTER TABLE, даже в некоторых случаях, когда это может показаться ненужным.

Повторите свое обновление 2:

Мне сказали, что важно обеспечить целостность данных с помощью функциональных внешних ключей, но также и то, что InnoDB не следует использовать с MySQL. Что вы рекомендуете?

Вы говорили об этом? Это абсолютно неверно. InnoDB имеет лучшую производительность, чем MyISAM (хотя InnoDB больше внимания уделяет настройке конфигурации), InnoDB поддерживает атомные изменения, транзакции, внешние ключи, а InnoDB намного более устойчив к повреждению данных при сбое.

Если вы не используете старую, неподдерживаемую версию MySQL (5.0 или более раннюю версию), вы должны использовать InnoDB как ваш выбор для хранения по умолчанию и использовать MyISAM только в том случае, если вы можете продемонстрировать определенную рабочую нагрузку, которая приносит пользу MyISAM.

Ответ 2

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

Родительская таблица:

CREATE TABLE NameSubject (
  Autonumber INT NOT NULL AUTO_INCREMENT,
  NameorSubject nvarchar(255),
  PRIMARY KEY (Autonumber)
 ) ENGINE=InnoDB;

Таблица детей:

CREATE TABLE Volumes (
  Autonumber INT NOT NULL,
  Volume INT,
  Pages nvarchar(50),
  Reel int,
  Illustrations bit,
  SSMA_TimeStamp timestamp,
  Foreign KEY (Autonumber) references NameSubject(Autonumber)
  ON  update cascade 
)engine=innodb;

"ON update cascade" сделал магию для меня.

Я надеюсь, что это сработает для других. Удачи.

Ответ 3

Просто, чтобы сохранить другие часовые головные боли, которые я испытывал, - как касается жирафа, убедитесь, что значение @FOREIGN_KEY_CHECKS установлено равным 1.

SELECT @@FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS = 1

Ответ 4

Я нашел следующую статью. У меня нет времени проверить это, но может быть полезно:

http://forums.mysql.com/read.php?22,19755,43805

Автор, Эдвин Дандо, говорит:

обе таблицы должны быть INNODB. Поле внешнего ключа должно иметь индекс на нем. Фиксированное ключевое поле и связанное с ним поле должны быть одного типа (я использую только целое число), а после часов боли они должны быть НЕПРАВИЛЬНЫ.

Ответ 5

проблема, скорее всего, что вопросы .p_id и users.p_id не определены как INT NOT NULL. для работы с внешними ключами определение столбцов с обеих сторон внешнего ключа должно точно совпадать, за исключением auto_increment и default.

Ответ 6

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

Я только столкнулся с "Не могу создать таблицу" в случае, когда я пытаюсь создать ограничение внешнего ключа, где мой локальный столбец отличается от внешнего столбца.

Ответ 7

Я думаю, что некоторые из тех, кто сталкивается с этой проблемой, могут начать с некоторых примеров баз данных, представленных на веб-сайте ORACLE для MYSQL (например, sakila DB). Не забудьте "снова включить ограничения внешнего ключа" в конце вашего script (например, в начале sakila DB script они отключены)

SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE, SQL_MODE='TRADITIONAL';

создайте свои таблицы здесь

то не забывайте об этом:

SET [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;

Ответ 8

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

Если вы объявляете свои внешние ключи как таковые

id  INTEGER UNSIGNED    REFERENCES    A_Table(id)

Затем внешний ключ, кажется, игнорируется, чтобы принудительно использовать ограничение без (видимо) использования любой из команд SET, используйте следующее объявление.

id  INTEGER UNSIGNED,
CONSTRAINT fk_id  FOREIGN KEY (id)  REFERENCES A_Table(id)

Этот путь решил проблему для меня. Не уверен, почему, как многие говорят, первая декларация является лишь сокращением ко второму варианту.

Ответ 9

Этот ответ сэкономил бы мне много времени, если бы я увидел его в первую очередь. Попробуйте выполнить следующие три шага, которые я заказал по частоте ошибок новичка:

(1) Измените таблицу, чтобы быть InnodDB, добавив "ENGINE = InnoDB" в свои инструкции "CREATE TABLE".

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

(2) Убедитесь, что ограничения внешнего ключа на самом деле проверяются, выполняя команду "SET foreign_key_checks = 'ON'"

(3) Добавить "ON UPDATE CASCADE" к объявлению вашего внешнего ключа.

Примечание. Убедитесь, что каскадирование - это поведение, которое вы хотите. Существуют и другие варианты...

Ответ 10

Ну, я думаю, что так или иначе опция "Пропустить создание FORIEN KEYS" проверена, это может произойти в разделе "Параметры" процесса "Форвардная инженерия".

Ответ 11

Ссылки KEY (autonumber) Foreign NameSubject (Autonumber) Обновление каскада обновлений ) Двигатель = InnoDB;

Я тоже использовал ON update cascade.... его работы

Ограничения InnoDB и FOREIGN KEY В этом разделе описываются различия в обработке движка InnoDB для внешних ключей по сравнению с сервером MySQL.

Определения внешнего ключа Определения внешних ключей для таблиц InnoDB подчиняются следующим условиям:

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

В настоящее время InnoDB не поддерживает внешние ключи для таблиц с пользовательским разделением. Это означает, что ни одна пользовательская таблица InnoDB не может содержать ссылки на внешние ключи или столбцы, на которые ссылаются внешние ключи.

InnoDB позволяет ограничению внешнего ключа ссылаться на не уникальный ключ. Это расширение InnoDB для стандартного SQL.

Ссылочные действия Ссылочные действия для внешних ключей таблиц InnoDB подчиняются следующим условиям:

В то время как SET DEFAULT разрешен сервером MySQL, он отклоняется как недействительный InnoDB. Операторы CREATE TABLE и ALTER TABLE, использующие этот раздел, не допускаются для таблиц InnoDB.

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

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

Если ON UPDATE CASCADE или ON UPDATE SET NULL рекурсирует для обновления той же таблицы, которую он ранее обновлял во время каскада, он действует как RESTRICT. Это означает, что вы не можете использовать self-referential ON UPDATE CASCADE или ON UPDATE SET NULL. Это делается для предотвращения бесконечных циклов, возникающих в результате каскадных обновлений. С другой стороны, возможен самозадачи ON DELETE SET NULL, а также самореферентный ON DELETE CASCADE. Каскадные операции могут быть не более чем на 15 уровней.

Как и MySQL в целом, в инструкции SQL, которая вставляет, удаляет или обновляет многие строки, InnoDB последовательно проверяет ограничения UNIQUE и FOREIGN KEY. При проверке внешнего ключа InnoDB устанавливает общие блокировки на уровне строк на дочерние или родительские записи, на которые он должен смотреть. InnoDB немедленно проверяет ограничения внешнего ключа; проверка не переносится на транзакцию. Согласно стандарту SQL, поведение по умолчанию должно быть отсроченным. То есть, ограничения проверяются только после обработки всего SQL-оператора. До тех пор, пока InnoDB не проверит проверку отложенных ограничений, некоторые вещи будут невозможны, например, удаление записи, которая ссылается на себя с использованием внешнего ключа.

Использование внешнего ключа и информация об ошибке Вы можете получить общую информацию о внешних ключах и их использовании из запроса таблицы INFORMATION_SCHEMA.KEY_COLUMN_USAGE. См. Также раздел 13.1.17.2 "Использование ограничений FOREIGN KEY".

В дополнение к SHOW ERRORS в случае ошибки внешнего ключа с использованием таблиц InnoDB (как правило, Ошибка 150 на сервере MySQL) вы можете получить подробное объяснение самой последней ошибки внешнего ключа InnoDB, проверив вывод SHOW СОСТОЯНИЕ ДВИГАТЕЛЯ INNODB.