Menu
When I execute the follow two queries (I have stripped them down to absolutely necessary):
mysql> CREATE TABLE foo(id INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE bar ( id INT, ref INT, FOREIGN KEY (ref) REFERENCES foo(id)) ENGINE InnoDB;
I get the following error: ERROR 1005 (HY000): Can't create table './test/bar.frm' (errno: 150)
Where the **** is my error? I haven't found him while staring at this for half an hour.
826
The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.
MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, a “child table record” refers to a dependent record within the same table.
You can disable foreign key check in MySQL by setting the system variable foreign_key_checks to 0. However, please note, after you enable foreign key checks, MySQL will not re-validate your existing data that you added after disabling foreign key check. It will only check any new additions/updates to your database.
A table with a foreign key reference to itself is still limited to 253 foreign key references. Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables. Stretch Database is deprecated in SQL Server 2022 (16.
From FOREIGN KEY Constraints
If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.
My suspicion is that it's because you didn't create foo as InnoDB, as everything else looks OK.
Edit: from the same page -
Both tables must be InnoDB tables and they must not be TEMPORARY tables.
116
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
