外键在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到最后,我收到一个错误:

ERROR 1005(HY000):无法创建表'./quotes/actions.frm'(错误号:150)

为什么会这样？

更新2

我被告知使用功能外键强制数据完整性很重要,但InnoDB也不应该与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解析器接受它们没有任何抱怨.

当它无法创建InnoDB表时会出现errno 150问题,因为它无法理解外键约束.您可以通过以下方式获得更多信息:

SHOW ENGINE INNODB STATUS;

InnoDB外键的一些要求:

引用表也必须是InnoDB.

引用表必须具有索引和主键.

FK列和引用的PK列的SQL数据类型必须相同.例如,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.

我知道这个帖子很久以前就被打开了,但是我发布这条消息给未来的用户,他们会寻找答案.我在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"为我带来了魔力.

我希望这适用于其他人.祝你好运.

只是为了节省其他时间的头痛我已经过了 - 当giraffa触及时,确保@FOREIGN_KEY_CHECKS设置为1.

SELECT @@ FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS = 1