Wednesday, March 11, 2009

foreign key across different mysql engines

I had a pre existing table(MYISAM) and I was creating new table innodb.
Dint realize for a long time that you cant have a foreign key relationship across 2 tables of different engines.
Error that you would see are

mysql> alter table property add (FOREIGN KEY(id) REFERENCES AUTH_USER(id));
ERROR 1005 (HY000): Can't create table 'sportland.#sql-134d_a' (errno: 150)
mysql> show engine innodb status;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
090311 15:36:27 Error in foreign key constraint of table sportland/#sql-134d_a:
FOREIGN KEY(id) REFERENCES AUTH_USER(id)):
Cannot resolve table name close to:
(id))

took me sometime to figure out the issue..!!

No comments: