MySQL Workbench忽略外键 [英] MySQL Workbench ignores foreign keys

问题描述

在处理MySQL Workbench中的SQL编辑器时，我偶然发现了一些奇怪的事情，其中​​执行似乎忽略了外键约束。下面是一个例子：

  create database testdb; 
 
使用testdb; 
 
创建表t1（
`test` INT，
 PRIMARY KEY（`test`）
）ENGINE = InnoDB; 
 
创建表t2（
`test1` INT，
`test2` INT，
 FOREIGN KEY（`test2`）REFERENCES t1（test），
 PRIMARY KEY（`test1`）
）ENGINE = InnoDB; 
 
插入t1值（1）; 
 
插入t2值（1,1）; 
插入t2值（2,2）; 
  

在这个例子中， insert into t2 values（2,2）; 应该失败，因为t1中没有行列测试是2。

我已经在phpMyAdmin中测试过了，给出了一个外键约束被违反的错误，但是在MySQL Workbench中它并没有给出错误，而是被插入到表中（我已经用phpMyAdmin检查过了）。

这对我来说并不是什么大问题，因为我可以使用不同的客户端来输入SQL，但是我对它的工作原理感兴趣，因为在我对外键的理解中，值是需要存在的被引用的表。

MySQL版本是5.5.16，引擎是InnoDB。

解决方案<我建议你更新工作台，我只有当我使用我的Mac，创建没有约束的表，但如果你运行生成的SQL将正确创建一切，我用我的窗口创建一个nd它工作完美，然后经过很多测试，我更新我的Mac上的工作台版本，现在一切工作完美。

I have stumbled upon something weird when dealing with the SQL editor in MySQL Workbench where the execution seems to ignore foreign key constraints. Here's an example:

create database testdb;

use testdb;

create table t1 (
 `test` INT,
 PRIMARY KEY (`test`)
) ENGINE = InnoDB;

create table t2 (
`test1` INT,
`test2` INT,
 FOREIGN KEY (`test2`) REFERENCES t1(test),
PRIMARY KEY (`test1`)
) ENGINE = InnoDB;

insert into t1 values (1);

insert into t2 values (1,1);
insert into t2 values (2,2);

In this example, insert into t2 values (2,2); ought to fail, as there is no row in t1 where column test is 2.

I've tested in phpMyAdmin, at it correctly fails and gives an error that the foreign key constraint is violated, but in MySQL Workbench it doesn't give an error, and it is inserted into the table (I've checked with phpMyAdmin).

It's not a big problem to me as I can just use a different client to input the SQL in, but I'm interested in why this works, as in my understanding of foreign keys is that the value needs to exist in the referenced table.

MySQL version is 5.5.16, engine is InnoDB.

解决方案

I recommend you to update workbench, i was having the same problem only when i use my mac, the tables where created without constraints but if you run the sql generated it will create everything correctly, i use my windows to create and it worked perfect, then after a lot of test i update the workbench version on my mac and now everything is working perfect.

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