ERROR 1005 (HY000)
Met a MySQL error 1005 when trying to create a foreign key constraint as following:
mysql> ALTER TABLE sales ADD CONSTRAINT parent_id_fk FOREIGN KEY (parent_id) REFERENCES orders (id);
ERROR 1005 (HY000): Can't create table 'database_name.#sql-xxxxx' (errno: 150)
I checked the error details by the errno in the stack.
mysql> \! perror 150;
MySQL error code 150: Foreign key constraint is incorrectly formed
My first guess is the current data violate the constraint. But no, the table was empty, which means no data.
My next guess is the name collision. Since the constraint name "parent_id_fk" is too common, so it might be duplicate, but there is no duplicate in the database.
Compare Two Columns
Next, I compared the referencing and the referenced columns by "show create table", they should be the same in data type, width, etc.
The referencing table:
mysql> show create table sales;
...
`parent_id` int(11) NOT NULL,
...
The referenced table:
mysql> show create table orders;
...
`id` int(11) unsigned NOT NULL,
...
They are the same in data type and width, BUT not in the value range: unsigned. The one is unsigned and the other is NOT unsigned.
Solution
We should modifying the column parent_id into unsigned. After modifying it, we can try to add the constraint again:
mysql> ALTER TABLE sales ADD CONSTRAINT sales_parent_id_fk FOREIGN KEY (parent_id) REFERENCES orders (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
It succeeded. The key point in the whole problem is that the two columns must match with each others in data type, width, range, anything.