使用MySQL的外键奇怪的问题 [英] Weird problem with Foreign Keys using MySQL

问题描述

我在MySQL方面遇到一个关于外键的怪异问题.

我正在尝试创建一些多对多关系，所以我拿着关系ID表... yada-yada-yada ...

我正在使用MySQL Workbench，并且拥有一个非常好的数据库模型，该模型使用外键.

当我将模型导出为一系列SQL查询时，该软件选择在表中将外键表示为CONSTRAINTS，这看起来非常合法，查询似乎正常运行，但是每个人都应该满意.您会期望数据库具有以下行为:
如果插入的行包含原始表中不存在的键值，则查询应失败.但是，插入成功.

经过一番网络研究，我想出了另一种解决问题的方法(使用ALTER TABLE< table_name> ADD FOREIGN KEY ...语法)，但是我不喜欢这样做，因为我很懒，数据库模型在项目期间可能会发生很大变化，这将成为烦人的开销...

有人知道这两种方法有什么区别吗?

这是两个查询，第一个应该起作用但不起作用，第二个可以起作用但很烦人:

I''m having a really weird problem with MySQL concerning Foreign Keys.

I''m trying to create some many-to-many relationships, so I''m holding relational id tables... yada-yada-yada...

I''m using MySQL Workbench, and I possess a really nice Database Model, which uses Foreign Keys.

When I''m exporting the model as a sequence of SQL queries, the software chooses to express the Foreign Keys as CONSTRAINTS, within the table, seems very legit, the queries seem to work properly and everybody should be happy, however... You would expect the following behavior from the database:
If inserting a row which contains key-values which do not exist in the original table, the query should fail. However, the insertion succeds.

After some web research, I came up with an alternative method to work things out (using ALTER TABLE <table_name> ADD FOREIGN KEY... syntax), but I don''t like that as I''m lazy, and the database model would probably change quite a lot during the project, and that would become an annoying overhead...

Does anybody know what''s the difference between the two methods?

Here are two queries, the first should work but doesn''t, the second works but is annoying:

CREATE TABLE IF NOT EXISTS <relational_table>
... column definitions, primary key, etc. ...

  INDEX <index_name> (<fk_column> ASC) ,
  CONSTRAINT <fk_constraint>
    FOREIGN KEY (<fk_column> )
    REFERENCES <original_table> (<original_column>)
    ON DELETE CASCADE
    ON UPDATE CASCADE
... rest of the declaration, Engine is InnoDB ...

---------------------------------------------------------------------------------

ALTER TABLE <relational_table> ADD FOREIGN KEY (`<fk_column>` )
    REFERENCES  <original_table>(`<original_column>` )
    ON DELETE CASCADE
    ON UPDATE CASCADE

推荐答案

您的表引擎是InnoDB吗? InnoDB使您的FOREIGN KEY约束生效.否则将插入innodb外键.所以您更改或不确保您的引擎是innoDB.

变更表不能确保外键.如果您的表已经是innodb，则无需更改引擎，只需确保已添加外键约束.

Is your table engine is InnoDB? InnoDB allows you FOREIGN KEY constraint to be affective. Other then innodb foreign key will be inserted anyway. so you alter or not make sure your engine is innoDB.

Altering table does not ensure foreign key. if your table is already innodb then no need to alter engine just need to ensure that foreign key constraint is added.

这篇关于使用MySQL的外键奇怪的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！