Bug with Foreign Key ?

shadeoner · Post by **shadeoner** » Thu 09 Feb 2012 12:27

Hi,

I just want to create foreign key between 2 innodb tables, and have this message each time I press ok.

http://imageshack.us/photo/my-images/68 ... 32219.jpg/

What's wrong ? Before I can easily make it.

Im on the 5.0.63 version.

alexa · Post by **alexa** » Thu 09 Feb 2012 16:46

Please refer to the 'Why Relation Cannot Be Created?' topic in the Help documentation of dbForge Studio for MySQL for details.

Please let us know if you're still experiencing problems.

shadeoner · Post by **shadeoner** » Thu 09 Feb 2012 19:23

Thx for reply but :

- both tables are Innodb
- they have exact same type
- im on MySQL > 5.0

It's like bugged...

alexa · Post by **alexa** » Fri 10 Feb 2012 09:01

Do the tables have primary keys added?
Could you please send us the CREATE TABLE statements of these two tables?

shadeoner · Post by **shadeoner** » Fri 10 Feb 2012 09:06

alexa wrote:Do the tables have primary keys added?
Could you please send us the CREATE TABLE statements of these two tables?

Sure here it is :

CREATE TABLE T_CAS(
cas_id BIGINT(20) NOT NULL AUTO_INCREMENT,
cas_titre VARCHAR(255) NOT NULL,
cas_texte_avant TEXT DEFAULT NULL,
cas_texte_apres TEXT DEFAULT NULL,
cas_date_creation INT(11) NOT NULL,
tpc_id BIGINT(20) NOT NULL,
PRIMARY KEY (cas_id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE TR_TYPE_CAS_TPC(
tpc_id BIGINT(20) NOT NULL AUTO_INCREMENT,
tpc_titre VARCHAR(255) NOT NULL,
tpc_texte TEXT DEFAULT NULL,
tpc_date_creation INT(11) NOT NULL,
PRIMARY KEY (tpc_id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;

I want to make the tpc_id column in the T_CAS table a foreign key

Thank you,

David

alexa · Post by **alexa** » Fri 10 Feb 2012 09:55

Could you please execute the following query right after getting the error message and provide the result:

SHOW ENGINE INNODB STATUS

shadeoner · Post by **shadeoner** » Fri 10 Feb 2012 10:54

I have :

Type : InnoDB
Name and status are empty

alexa · Post by **alexa** » Fri 10 Feb 2012 11:02

Please try to add the query provided in the previous message right at the end of the query that adds the foreign key and send us the result once again.

shadeoner · Post by **shadeoner** » Fri 10 Feb 2012 11:21

I did this :

CREATE TABLE bellesdents.T_CAS(
cas_id BIGINT(20) NOT NULL AUTO_INCREMENT,
cas_titre VARCHAR(255) NOT NULL,
cas_texte_avant TEXT DEFAULT NULL,
cas_texte_apres TEXT DEFAULT NULL,
cas_date_creation INT(11) NOT NULL,
tpc_id BIGINT(20) NOT NULL,
PRIMARY KEY (cas_id),
CONSTRAINT FK_T_CAS_TR_TYPE_CAS_TPC_tpc_id FOREIGN KEY (tpc_id)
REFERENCES bellesdents.TR_TYPE_CAS_TPC (tpc_id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;

SHOW ENGINE INNODB STATUS

Right ?

alexa · Post by **alexa** » Fri 10 Feb 2012 14:36

The problem appears not to be connected with dbForge Studio for MySQL but with the MySQL server itself, so we suggest you to search for an answer in the internet since we provide technical support only on our products.

shadeoner · Post by **shadeoner** » Fri 10 Feb 2012 15:11

Ok thank you !

David

michabbb · Post by **michabbb** » Thu 16 Feb 2012 22:50

hi david,

i had the same error and went crazy too, because all seems to be okay.
i can say, this is not a dbforge bug, its mysql server related.
i don´t know if this is a bug or a bad timing between creating, deleting and alter tables in a wrong way and mysql stores internally some old infos, like foreign keys.

in my case the following procedure helped:

- delete the both tables i want to connect to each other
- dump the whole db
- drop database
- maybe restart mysql
- dump db (your backup) back to mysql
- create your tables again
- create your foreign keys

best regards,
michael