This gist documents a problem with MySQL Workbench, which may be related to an SQL parser bug in MySQL, I'm not 100% certain yet.
This is repeatable with MySQL 5.5.25 on Windows 8, I have not tested this on other versions or environments yet.
First, create a database and populate it with two tables for testing:
DROP DATABASE IF EXISTS `test`;
USE `test`;
DROP TABLE IF EXISTS `person`;
CREATE TABLE IF NOT EXISTS `person` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(128) DEFAULT NULL COMMENT 'This Person''s unique, private e-mail address - this is used when logging in.',
`password_salt` char(40) DEFAULT NULL COMMENT '40-character string used as salt when computing password_hash.',
`password_hash` char(40) DEFAULT NULL COMMENT '40-character SHA1 of the user''s password and password_salt.',
`first_name` varchar(64) DEFAULT NULL,
`last_name` varchar(64) DEFAULT NULL,
`region_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A Person is any individual known to the CHESS organization.';
DROP TABLE IF EXISTS `region`;
CREATE TABLE IF NOT EXISTS `region` (
`id` int(10) unsigned NOT NULL,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Now run the following ALTER TABLE statement:
ALTER TABLE `test`.`person`
CHANGE COLUMN `password_hash` `password_hash` CHAR(40) NULL DEFAULT NULL COMMENT '40-character SHA1 of the user\'s password and password_salt.' ,
ADD CONSTRAINT `person_region_fk`
FOREIGN KEY (`region_id` )
REFERENCES `test`.`region` (`id` )
ON DELETE SET NULL
ON UPDATE RESTRICT;
This should execute without errors or warnings - you can run a SHOW WARNINGS to confirm that no warnings were issued.
Now run a SHOW CREATE TABLE person and inspect the resulting change - on my system, the index person_region_fk index has been created, but no foreign key gets created. No error message, no warning, no explanation - the FOREIGN KEY part of the ALTER TABLE statement is simply ignored.
By removing/changing parts of the SQL statement, I narrowed it down to a very small difference - here's a working version of the above statement:
ALTER TABLE `test`.`person`
CHANGE COLUMN `password_hash` `password_hash` CHAR(40) NULL DEFAULT NULL COMMENT '40-character SHA1 of the user''s password and password_salt.' ,
ADD CONSTRAINT `person_region_fk`
FOREIGN KEY (`region_id` )
REFERENCES `test`.`region` (`id` )
ON DELETE SET NULL
ON UPDATE RESTRICT;
Run that, and then run SHOW CREATE TABLE person again - this time, you should see the foreign key was created.
Can you spot the difference?
Let's put the COMMENT portion of those two versions of that statement side by side:
... COMMENT '40-character SHA1 of the user\'s password and password_salt.'
... COMMENT '40-character SHA1 of the user''s password and password_salt.'
According to the MySQL reference, the single quote can be escaped either as two quotes '' or with a backslash \'
http://dev.mysql.com/doc/refman/5.5/en/string-literals.html
This is down right spooky - and running the first statement does correctly apply the comment, but somehow this trips up MySQL's interpretation of the rest of that statement, or somehow causes it to not fully execute...
Repeated on Linux with MySQL 5.1.67