MySQL Bugs: #27974: Imprecise mysql error message when pk index is missing

Bug #27974	Imprecise mysql error message when pk index is missing
Submitted:	20 Apr 2007 10:39	Modified:	20 Apr 2007 20:50
Reporter:	Oli Sennhauser	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	any	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When creating a table with a foreign to a table where the PK index is missing a very imprecise error message is displayed.

How to repeat:
CREATE TABLE parent (
    id   INT NOT NULL
  , data VARCHAR(32)
) ENGINE=InnoDB
;

CREATE TABLE child (
    id   INT NOT NULL
  , data VARCHAR(32)
  , fk INT
  , CONSTRAINT fk_c FOREIGN KEY (fk) REFERENCES parent (id)
) ENGINE=InnoDB
;
ERROR 1005 (HY000): Can't create table 'test.child' (errno: 150)

ALTER TABLE parent
  ADD PRIMARY KEY (id)
;

CREATE TABLE child (
    id   INT NOT NULL
  , data VARCHAR(32)
  , fk INT
  , CONSTRAINT fk_c FOREIGN KEY (fk) REFERENCES parent (id)
) ENGINE=InnoDB
;
Query OK, 0 rows affected (0.08 sec)

Suggested fix:
The error message could be more precise by saying that primary key is missing on id in table parent...

This has costed me at least 15 minutes to find out. And I guess many others too!

Oli,

you should use SHOW INNODB STATUS to print a detailed error message.

This feature request is already at bugs.mysql.com, as so many people have bumped into these bad error messages.

Regards,

Heikki