MySQL errno: 150 Foreign Key Constraint

chris830

于 2009-01-09 17:01:00 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

文章标签： mysql reference table delete null

本文链接：https://blog.youkuaiyun.com/chris830/article/details/3741839

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.