Bartis, Robert M (Bob) wrote:
I recently added a column and Index to an existing table. I wanted to also add a Foreign Key. I have done this before defining the commands outside MySQL and souring the file in for new tables, but would prefer to not have to dump the current table just for the modification. I tried to add one based on the users guide, see command/response below, without success. Obviously I am missing something. Any suggestions?
mysql> alter table runload_list add foreign key (PlanName) references testplans (PlanName) on update cascade on delete restrict;
ERROR 1005: Can't create table './mydb/#sql-3ebd_430.frm' (errno: 150)
From the manual at
http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
If MySQL reports an error number 1005 from a CREATE TABLE statement,
and the error message string refers to errno 150, this means that the
table creation failed because a foreign key constraint was not correctly
formed. Similarly, if an ALTER TABLE fails and it refers to errno 150,
that means a foreign key definition would be incorrectly formed for
the altered table. Starting from MySQL 4.0.13, you can use SHOW INNODB STATUS
to display a detailed explanation of the latest InnoDB foreign key error in the
server.
So, you should check "show innodb status" for some more about this error it seems.
Also, one of the more common errors is a lack of an index on testplans.PlanName,
so make sure you have one of those too.
Regards,
Josh
________________________________________________________________________
Josh Chamas, Founder | NodeWorks - http://www.nodeworks.com
Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com
http://www.chamas.com | Apache::ASP - http://www.apache-asp.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
