MySQL errno: 150 Foreign Key Constraint

解决MySQL外键约束错误

最新推荐文章于 2021-07-13 19:14:20 发布

原创最新推荐文章于 2021-07-13 19:14:20 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #reference #table #delete #null

本文探讨了MySQL中出现的外键约束错误1005及其解决方案。文章详细解释了InnoDB存储引擎对外键的支持，并提供了创建外键的正确语法。此外还列出了确保外键正常工作的检查步骤。

From MySQL Manual we can see it happens a foreign key constraint was not correctly formed.
(http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html)
It reports something like ' ERROR 1005: Can't create table './.../#.....frm' (errno: 150)'

Now let's see the right syntax:
(http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html)

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

To fix this, please do such checks:

1. InnoDB supports foreign key constraints. 
   This means you should created table as InnoDB if you are planning to add foreign key constraint on it.
2. Both source column and destination column should be indexed.
   InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.