MySQL Bugs: #18207: can't add foreign key to existing table using ALTER TABLE

Bug #18207	can't add foreign key to existing table using ALTER TABLE
Submitted:	14 Mar 2006 3:34	Modified:	23 Mar 2006 16:03
Reporter:	Jim Michaels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
Can't create a foreign key with the query browser using the mouse.
Error 1005. Can't create table. errno: 150

since I get the same problem executing this as a query, maybe this is a problem with the engine.  I can't add a forgeign key to a table when the table already exists.  I must create the table with the foerign keys all at once.  ALTER TABLE is not working correctly.

How to repeat:
using the query browser 1.1.20, Edit an existing table. add a foreign key with the default name.  make sure the column names are the same between tables and are of type INT, the referring table's being PRIMARY KEY auto_increment. pick the reference table from the list. column is automatically picked. Click apply.  InnoDB Engine (default).
you get 
ALTER TABLE `dbo`.`quiz_reports_quiz_questions` ADD CONSTRAINT `FK_quiz_reports_quiz_questions_1` FOREIGN KEY `FK_quiz_reports_quiz_questions_1` (`quiz_id`) REFERENCES `quiz_quizzes` (`quiz_id`);

MySQL Error Number 1005
Can't create table '.`dbo\#sql-358_1.frm' (errno: 150)

use these tables for example:
CREATE TABLE `quiz_questions` (
    `question_id` INT AUTO_INCREMENT NOT NULL,
    `question_num` INT,
    `question` LONGTEXT,
    `image` BLOB,
    `is_img` TINYINT(1),
    `mime_type` VARCHAR(25),
    PRIMARY KEY(question_id),
    INDEX(question_num)
);
CREATE TABLE `quiz_reports_quiz_questions` (
    `rqq_id` INT AUTO_INCREMENT NOT NULL,
    `quiz_id` INT,
    `rq_id` INT,
    PRIMARY KEY(rqq_id),
    INDEX(quiz_id),
    INDEX(rq_id)
);

Thanks for your bug report:

C:\my>perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Can you please provide us with the CREATE TABLE statement for table: quez_quezzes 

Thanks

CREATE TABLE `quiz_quizzes` (
    `quiz_id` INT AUTO_INCREMENT NOT NULL,
    `title` VARCHAR(100),
    `released` TINYINT(1) NOT NULL DEFAULT '0',
    `retired` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY(quiz_id)
);

ALTER TABLE works.  this was a missing or renamed table.  Sorry for the false alarm.  
Odd error message to display though.  Doesn't help me debug.  Could we get more appropriate error messages?  If not, OK.  I'll muddle through.

Closed as it was not a bug, really.