MySQL Bugs: #56818: Create Foreign Key just creates Index

Bug #56818	Create Foreign Key just creates Index
Submitted:	16 Sep 2010 12:07	Modified:	22 Nov 2011 6:36
Reporter:	Frank Schulten	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.32	OS:	Windows (XP 32Bit)
Assigned to:		CPU Architecture:	Any
Tags:	foreign key

Description:
MySQL Workbench running on Windows, remotely connected to a Linux DB.

Server default DB is InnoDB. Via SQL Editor I try to alter a table and to add a foreign key.

The resulting SQL Statement just creates an Index on the source coulumn:

ALTER TABLE `TEST`.`TEST2` 
ADD INDEX `FK_T1` (`refTEST1` ASC);

I can create the foreign keys by Hand by altering the SQL and adding the constraint manually. Had the same problem with 5.2.26 of Workbench.

How to repeat:
Create a table TEST1:
CREATE TABLE "TEST1" (
  "idTEST1" int(11) NOT NULL,
  PRIMARY KEY  ("idTEST1");

Create a table TEST2:

CREATE TABLE "TEST2" (
  "idTEST2" int(11) NOT NULL,
  "refTEST1" int(11) default NULL,
  PRIMARY KEY  ("idTEST2")
);

Try to add a foreign key on TEST2.refTEST1 with Alter Table select TEST1 referenced column idTEST1. Resulting SQL is only:

ALTER TABLE `test`.`TEST2` 
ADD INDEX `FK_TEST1` (`refTEST1` ASC) ;

Settings in workbench for Foreign key

Attachment: PictureFKSettings.jpg (image/pjpeg, text), 55.68 KiB.

What version of MySQL server you are working with? I've got the following SQL generated when working with 5.1.50:

ALTER TABLE `test`.`test2` 
  ADD CONSTRAINT `fk1`
  FOREIGN KEY (`refTEST1` )
  REFERENCES `test`.`test1` (`idTEST1` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `fk1` (`refTEST1` ASC) ;

Its a 5.0.26 Server running on redhat linux.

Did you specify the default storage engine in your model/tables to be InnoDB?
WB does not change how it works dependong on the target DB's  default storage engine. You have to explicitly specify it in your model.

Even if I set it to Inno DB through "Alter Table" it always shows "Server default" (which is Inno DB) and behaves like described. How do I set it then "explicitly"? Should the work bench not read what Engine I have and behave acoordingly?

Not sure if related, but while I was investigating this bug I found bug #57830, in the server.

Frank: can you paste the output of show create table TEST1 and same for TEST2?

I get exactly what you see if my tables are MyISAM:

ALTER TABLE `isam`.`tt` 
ADD INDEX `xx` (`t` ASC) ;

mysql> show create table tt;
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| tt    | CREATE TABLE `tt` (
  `a` int(11) NOT NULL,
  `t` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You mean how I created the tables?

delimiter $$

CREATE TABLE "TEST1" (
  "idTEST1" int(11) NOT NULL,
  PRIMARY KEY  ("idTEST1")
)$$

CREATE TABLE "TEST2" (
  "idTEST2" int(11) NOT NULL,
  "refTEST1" int(11) default NULL,
  PRIMARY KEY  ("idTEST2")
)$$

Then tried to add a foreign key as described.

Server Infomation from Workbench:

Name:    Linux
Host:    host:3306
Server:  MySQL
Version: 5.0.26
User:    XXX

Standard engine is innodb.

No, connect to the MySQL server either with the command line client or the workbench SQL Editor and then execute:

SHOW CREATE TABLE <yourtable name> and then paste the output here. Or just run SHOW TABLE STATUS FROM <your database containing the created tables> from the cmdline client or WB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have this same issue, and after discussion with akojima on irc, it has been tracked down to the tables being MyISAM and converted to InnoDB and foreign keys being created at the same time.

E.g

make a MyISAM table
synchronise to db/create script

convert table to InnoDB and add FK's
syncronise/create alter script

FK indexes are created as regular indexes, not as foreign indexes.

Workaround

make MyISAM table
sync
convert table to InnoDB
sync
create FK's
sync.

Hope this helps

Verified just as described in the last comment while working with MySQL server 5.1.54. The following code is generated:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

ALTER TABLE `mydb`.`table2` ENGINE = InnoDB , ADD COLUMN `table1_idtable1` INT(11) NOT NULL  AFTER `fk` 
, ADD INDEX `fk_table2_table1` (`table1_idtable1` ASC) ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Fixed as of 5.2.36:
+        The <guibutton>Foreign Keys</guibutton> tab of the
+        <guimenu>Alter Table</guimenu> wizard only created an index,
+        without the foreign key.