end0tknr's kipple - 新web写経開発

http://d.hatena.ne.jp/end0tknr/ から移転しました

mysql の外部キー制約設定(FOREIGN KEY)でエラーなら、「SHOW ENGINE INNODB STATUS」でログを見ましょう

mysql> ALTER TABLE software ADD CONSTRAINT software_hw_name
    -> FOREIGN KEY (fk_hw_name) REFERENCES hardware (hw_name)
    -> ON DELETE SET NULL ON UPDATE CASCADE;
ERROR 1215 (HY000): Cannot add foreign key constraint

上記のようなエラーに出会ったら、ググるより、 「SHOW ENGINE INNODB STATUS」でmysqlのログを見る方が解決への近道です。

mysql> SHOW ENGINE INNODB STATUS\G
<略>
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-03-08 10:01:35 7fe1f6294700 Error in foreign key constraint of table it_assets/#sql-cfa_4e4:

FOREIGN KEY (fk_hw_name) REFERENCES hardware (hw_name)
ON DELETE SET NULL ON UPDATE CASCADE:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

今回の場合、create indexを追加することで解消しています