Sam Sargeant

I’ve been using MySQL for over a decade now. When I have the choice, I’ll build projects with PostgreSQL. It happens that my current contract means I have a legacy MySQL database to work with. That’s fine, I’m a professional. Lets do this thing.

I wanted to add a new foreign key constraint to an existing table.

ALTER TABLE person
ADD CONSTRAINT person_type_id
FOREIGN KEY (person_type_id)
REFERENCES person_type (person_type_id);

Upon trying this SQL, I get the following error:

ERROR 1005 (HY000): Can't create table './customers/#sql-910_26.frm' (errno: 150)

Hrm, okay. That seems rather strange. I wonder that error number 150 is.

$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Oh, I must have done something wrong with my new constraint. Weird that it doesn’t just come out and tell me that. Okay, lets check the manual and my SQL. Nope, I think my SQL is right. Now what?

Some searching suggested that more detail on the error could be found with another command:

msyql> show engine innodb status;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

So it seems I need to be a superuser to get the error message for the command I just ran. That’s bizarre. Fortunately this isn’t a production box and I have superuser access. I run the command and search through pages and pages of text to find my error:

Cannot resolve table name close to: (person_type_id)

I’m not sure what that means, so I search some more. A few bug reports and forum posts finally leads me close to a solution. If your tables don’t use the same backend engine type, then foreign keys don’t work.

The solution, in my case, was to recreate the person_type table with the same engine as my person table. (In this case, InnoDB)

In summary: Initial error is unhelpful, secret error message is available if you have admin access, but it’s buried in a lot of text, and doesn’t really describe the problem. I had a very frustrating 30 mins chasing errors down a hole. I have now vented here, and will never mention it again. Maybe.