Cannot resolve table name close to

Question

I want to create a references to foreign table. but i'm getting the following error:

query:

CREATE TABLE category_ids (id INT, post_id INT,
                    INDEX par_ind (post_id),
                    FOREIGN KEY (post_id) REFERENCES post(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

SHOW ENGINE INNODB STATUS\G:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2013-08-23 00:11:06 7f6f49e7b700 Error in foreign key constraint of table fun/category_ids:
FOREIGN KEY (post_id) REFERENCES post(id)
                      ON DELETE CASCADE
) ENGINE=INNODB:
Cannot resolve table name close to:
(id)
                      ON DELETE CASCADE
) ENGINE=INNODB

posts table structure

    mysql> describe post;
    +-------------+-----------------------+------+-----+---------------------+----------------+
    | Field       | Type                  | Null | Key | Default             | Extra          |
    +-------------+-----------------------+------+-----+---------------------+----------------+
    | id          | int(11)               | NO   | PRI | NULL                | auto_increment |
...
    +-------------+-----------------------+------+-----+---------------------+----------------+
    22 rows in set (0.00 sec)

Answer 1

Only InnoDB supports Foreign keys, MyISAM doesn't. Even if it would, you cannot create relations between tables of different type.

Therefore you need to convert the table post into InnoDB. ALTER TABLE post ENGINE = InnoDB;

Answer 2

This error can also come when parent table is partitioned. Removing partitioning from the parent table allows foreign constraint to be created without any problem.