MySQL Workbench ignores foreign keys

Question

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.

Answer 1

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.

Answer 2

I would recommend creating the tables separately from the foreign key constraints. In my experience with Workbench, these are the things to keep in mind with fk constraints:

• Both tables must be InnoDB
• The data type must match exactly (including default value)

• Make sure that a foreign key doesn't already exist.

  SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE()

• The referenced key must be a unique key

• Check the Output on Workbench for errors

I would try creating your FK constraint as follows:

CREATE TABLE t1 (
 `test` INT,
 PRIMARY KEY (`test`)
) ENGINE = InnoDB;

INSERT INTO t1 VALUES (5);

CREATE TABLE t2 (
`test1` INT,
`test2` INT,
PRIMARY KEY (`test1`)
) ENGINE = InnoDB;

ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (test2) REFERENCES t1 (test);

Then we can test:

INSERT INTO t2 VALUES (1, 4);

Cannot add or update a child row: a foreign key constraint fails .... CONSTRAINT t2_ibfk_1 FOREIGN KEY (test2) REFERENCES t1 (test)):