不能在MySQL中工作的外键:为什么我可以插入一个不在外文列中的值?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (17)

我在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));

这就创建了我想要的表(尽管一个“描述操作”命令告诉我外键是MUL类型的键,我不知道这意味着什么)。但是,当我试图输入一个Q_身份证或U_在“问题”或“用户”表中不存在的ID,MySQL仍然允许这些值。

我做错什么了?如何防止具有外键的表接受无效数据?

如果我加上TYPE=InnoDB最后,我得到了一个错误:

错误1005(HY000):无法创建表‘./QUERS/actions.frm’(errno:150)

为什么会发生这种事?

提问于
用户回答回答于

我知道这个帖子很久以前就打开了,但是我现在发布这条消息给将来的用户,他们会寻找答案。我在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;
用户回答回答于

我猜想你的默认存储引擎是MyISAM,它忽略了外键约束。它静默地接受外键的声明,但不存储约束或随后强制执行约束。

但是,它确实在为外键声明的列上隐式创建索引。在MySQL中“KEY“是”的同义词。INDEX“。这就是描述输出中显示的内容:索引,而不是约束。

现在可以将无效的值插入到表中,因为没有约束。要获得强制引用完整性的约束,必须使用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犯了一个很大的错误静默忽略外键约束声明。没有错误或警告说存储引擎不支持它们。

顺便说一下,检查约束也是如此。与MySQL一起使用的存储引擎不支持检查约束,但SQL解析器接受这些约束时没有任何抱怨。

由于无法理解外键约束,所以在无法创建InnoDB表时会出现errno 150问题。可以通过以下网站获得更多信息:

SHOW ENGINE INNODB STATUS;

InnoDB外键的一些要求:

  • 引用表也必须是InnoDB。
  • 引用表必须有索引和主键。
  • FK列和引用的PK列的SQL数据类型必须相同。例如,int不匹配BIGINT或int无符号。

可以更改包含数据的表的存储引擎:

ALTER TABLE actions ENGINE=InnoDB;

这实际上是将整个MyISAM表复制到InnoDB表,然后一旦成功,它就会删除MyISAM表,并将新InnoDB表重命名为前一个MyISAM表的名称。这被称为“表重构”,根据表中的数据数量,这可能是很费时的。在ALTERTABLE过程中会发生表重构,即使在某些情况下似乎没有必要。

扫码关注云+社区