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

Я предполагаю, что ваш механизм хранения по умолчанию - 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 должны быть идентичными. Например, INT не соответствует BIGINT или INT UNSIGNED.

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

ALTER TABLE actions ENGINE=InnoDB;

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

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

I'm told that it's important to enforce data integrity with functional foreign keys, but also that InnoDB should not be used with MySQL. What do you recommend?

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

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

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

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

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

both tables must be INNODB. The foreign key field must have an index on it. The foeign key field and the field being referenced must be of the same type (I only use integer) and, after hours of pain, they must be UNSIGNED.

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

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

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

Я знаю, что эта ветка была открыта давно, но я отправляю это сообщение для будущих пользователей, которые будут искать ответ. У меня была такая же проблема с внешним ключом в 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" сотворил для меня чудо.

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

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

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

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

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

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

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

ВЫБРАТЬ @@ FOREIGN_KEY_CHECKS

УСТАНОВИТЬ FOREIGN_KEY_CHECKS = 1

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

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

id  INTEGER UNSIGNED    REFERENCES    A_Table(id)

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

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

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

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

(1) Измените таблицу на InnodDB, добавив «ENGINE = InnoDB» к вашим операторам «CREATE TABLE».

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

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

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

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

Что ж, я предполагаю, что как-то отмечена опция «Пропустить создание ИНОСТРАННЫХ КЛЮЧЕЙ», это может произойти в разделе «Опции» процесса «Форвард-инжиниринг».

IGM 444