When I tried to assign foreign key to a table named mydb.table1 it was deleted from my database, I could not understand the reason and tried to move on. For this tried to create same table with create SQL but it says
#1005 - Can't create table 'mydb.table1' (errno: 150)
Now I created same schema table with table name table2, this was successfully created but as my application code is written for table1, I need to rename this table to table1 again on which gives me following error.
#1025 - Error on rename of table errno: 150
Also tried to run alter table query with set foreign_key_checks = 0. But same error.
While searching I came accross below SQL,
SHOW INNODB ENGINE STATUS;
Which displays following message
141223 11:30:10 Error in foreign key constraint of table myahd/table1: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:, CONSTRAINT "fk_key9" FOREIGN KEY ("updated_by") REFERENCES "users" ("userid") ON DELETE NO ACTION ON UPDATE NO ACTION
While there is no such table listed in my database with name table1. Nor any such key is found with fk_key9.
May be I can delete this table1's idb or frm file, but unable to locate it. Or may be there is some other solution.
Thanks in advance.