Bug #66297 Error Code: 1064 on "ADD CONSTRAINT" function
Submitted: 10 Aug 2012 6:52 Modified: 16 Jul 2014 6:42
Reporter: Steve Tse Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S1 (Critical)
Version:5.2.41 OS:Windows (Microsoft Windows 7 Ultimate Edition Service Pack 1 (build 7601), 64-bit)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[10 Aug 2012 6:52] Steve Tse
Description:
 When calling 
ALTER TABLE 't1' ADD CONSTRAINT 'ibfk_1' FOREIGN KEY ( 'id1' ) REFERENCES 't2' ('id1'); 

failed with error:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ADD CONSTRAINT 'ibfk_1' FOREIGN KEY ('id1') REFERENCES 't' at line 1 

But when I used Workbench 5.2.40 CE, it can be done successfully.

----- Developer Notes -----

MySQL Workbench CE for Windows version 5.2.41  revision 9724
Configuration Directory: C:\Users\stse.CHARMTOP\AppData\Roaming\MySQL\Workbench
Data Directory: C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.41
Cairo Version: 1.8.8
OS: Microsoft Windows 7 Ultimate Edition Service Pack 1 (build 7601), 64-bit
CPU: 2x Pentium(R) Dual-Core  CPU      E5200  @ 2.50GHz, 4.0 GiB RAM
Active video adapter (0): mv video hook driver2
Installed video RAM: 0 MB
Current video mode: 1920 x 1200 x 4294967296 colors
Used bit depth: 32
Driver version: 6.0.1.0
Installed display drivers: NULL
Current user language: 中文 (繁體,香港特別行政區)
MySQL Version: 5.5.25a

How to repeat:
SQL Script:
CREATE TABLE IF NOT EXISTS `t1` (
  `id1` int(10) NOT NULL,
  `account_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `company_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `taxable` int(1) NOT NULL DEFAULT '1',
  `deleted` int(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `account_number` (`account_number`),
  KEY `id1` (`id1`),
  KEY `deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `t2` (   `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `id1` int(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id1`), KEY `first_name` (`first_name`), KEY `last_name` (`last_name`), KEY `email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `t1` ADD CONSTRAINT `ibfk_1` FOREIGN KEY (`id1`) REFERENCES `t2` (`id1`);

Steps to Repeat
1. Install 5.2.40 CE and run the script above, the process is successful.
2. Install 5.2.41 CE and run the script above, the error is shown.
[10 Aug 2012 13:08] Valeriy Kravchuk
I can not repeat this with 5.2.41 on Windows. Can you try to drop tables before running your script? 

Probably table already has same constraint, or other table definition is inherited.
[14 Aug 2012 9:05] Steve Tse
I have tried to drop table and drop database, and then run the sql but no luck.  Instead I tried to create a Foreign Key constraint via GUI, it works.  So I copied the same script from the GUI and tried it again, there is no way to make it work in script.
[22 Aug 2012 2:31] Steve Tse
Anyone have idea?
[31 Aug 2012 1:15] MySQL Verification Team
Are you tried version 5.2.42 ?. Thanks.
[1 Oct 2012 1:00] Bugs System
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".
[15 Apr 2014 9:10] Vladimir Tisma
I have this in MySQL Workbench 5.2.47 on Ubuntu.

Running from any other SQL client but Workbench is successful.
[16 Jun 2014 6:42] MySQL Verification Team
I could not repeat this issue on latest GA version.
Also, version 5.2.47 is old and many bugs were fixed since. Please
upgrade to current version 6.1.6, try with it and inform us if problem still
exists.

http://dev.mysql.com/downloads/workbench/
[17 Jul 2014 1:00] Bugs System
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".