foreign keys disappear

andreim · Post by **andreim** » Mon 29 Aug 2005 15:12

I added a foreign key contstraint to a table (I was getting Error 150, so I added an index to the column and after that I was able to add the FK).
Then I added a new record to the table, no problems so far. However, when I got back to edit the table, I noticed that all FK contstraints were gone. Any idea why?

Thank you

Duke · Post by **Duke** » Tue 30 Aug 2005 05:45

1. Check that both of your tables have InnoDb type.
2. Create foreign key for a table.
3. Issue SHOW CREATE TABLE for the table with foreign key.
If MySQL shows that there are no FK, then trouble can be connected with MySQL server itself.

andreim · Post by **andreim** » Tue 30 Aug 2005 19:04

I issued SHOW CREATE TABLE and I DID see constraints in place, as the matter of fact three identical constraints - two from the day before and one from now. However, in VS no constraints are shown neither in Design nor in Text view. Bug?

Duke · Post by **Duke** » Wed 31 Aug 2005 08:21

It seems like a bug. Please, tell us the MySQL server version and specify DDL for those two tables you are connecting with foreign key.

andreim · Post by **andreim** » Wed 31 Aug 2005 15:06

1. DDL for table accounts2 that was just created, saved, but the edit tab in VS is still on:

CREATE TABLE cms.accounts2 (
account_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
accounttype_id INT(11) UNSIGNED,
opendate DATE,
INDEX indexaccounttype_id (accounttype_id),
PRIMARY KEY (account_id),
CONSTRAINT FKaccounttype_id FOREIGN KEY (accounttype_id)
REFERENCES accounttypes(accounttype_id)
)
TYPE=InnoDB

2. DDL for the same table after I closed the tab and reopened it (right click-> Edit Table):

CREATE TABLE cms.accounts2 (
account_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
accounttype_id INT(11) UNSIGNED,
opendate DATE,
INDEX indexaccounttype_id (accounttype_id),
PRIMARY KEY (account_id)
)
TYPE=InnoDB
AUTO_INCREMENT=1

3. DDL for the second table

CREATE TABLE cms.accounttypes (
accounttype_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
accounttype VARCHAR(20) NOT NULL,
PRIMARY KEY (accounttype_id)
)
TYPE=InnoDB
AUTO_INCREMENT=5

4. mysql version is: 4.0.12

5. As I mentioned earlier, SHOW CREATE TABLE reflects the foreign key in it.

Thank you.

Duke · Post by **Duke** » Thu 08 Sep 2005 10:00

The problem is that MySQL server can reference foreign key by name only from version 4.0.18. Thus, you cannot actually drop foreign key from table on your server.
So MySQL Developer will properly work with older MySQL server versions.