CODEWITHSUNDEEP
mysqlforeign-keysmysql-workbench
galaxyAbstractor
galaxyAbstractor

Reputation: 557

MySQL Workbench ignores foreign keys

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.

Upvotes: 2

Views: 4940

Answers (3)

Jos Luijten
Jos Luijten

Reputation: 689

It looks to me like it has something to do with privileges in combination with the server_system_Variable: "foreign_key_check".

Usually Workbench is connected to a database using the Root user. This grands him all privileges. And enables him to set foreign_key_check=0 before all query executions and set it back to 1 when execution is over. (this is useful when refilling a table from a datadump and you don't want to care about the order the tables are refilled in).

Setting a global system variable runtime value normally requires the SYSTEM_VARIABLES_ADMIN or SUPER privilege. And it seems to work when you create a new user that dous'nt have all the privileges.

Upvotes: 0

Diego
Diego

Reputation: 669

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.

Upvotes: 1

Kermit
Kermit

Reputation: 34063

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)):

Upvotes: 0

Related Questions