Devart Forums

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.

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.

Thx for reply but :

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

It's like bugged...

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

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

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

SHOW ENGINE INNODB STATUS

I have :

Type : InnoDB
Name and status are empty

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.

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 ?

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.

Ok thank you !

David

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