Error 1005 when adding a foreign key constraint on mysql table

Question

Hello,

I have a problem when upgrading a django and mysql app with south.

I've tried to make a sql-based upgrade with the code generated by the django sqlall command and I have a similar problem.

Here is the sql code:

CREATE TABLE `programmations_basissupport` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `value` numeric(6, 0) NOT NULL
)

ALTER TABLE `programmations_concert` ADD `basis_support_id` integer AFTER program_status_id;

ALTER TABLE `programmations_concert` ADD CONSTRAINT `basis_support_id_refs_id_1e4ed8d7` FOREIGN KEY (`basis_support_id`) REFERENCES `programmations_basissupport` (`id`);

An error is raised when adding the FK constraint:

ERROR 1005 (HY000): Can't create table 'apidev_mnl.#sql-106e_632b00a' (errno: 150)

Does anybody have an idea?

Update: DEFAULT values where missing but even if I add the default='' in the django model, the creation of foreign keys fails.

Thanks for your help

Answer 1

I finally fixed the problem thanks to a workaround. The alter works ok on my dev machine while it fails on the host. I didn't find the reason but I succeed to make it working by exporting, migrating on my dev machine and reimporting.

Answer 2

There was the same problem. And finally i find that in referenced table field was "unsigned" but in referencing table - Not unsigned.

Answer 3

Looks like you are trying to add a constraint with a symbol/name that already exists.

ALTER TABLE programmations_concert ADD CONSTRAINT basis_support_id_refs_id_1e4ed8d7 FOREIGN KEY (basis_support_id) REFERENCES programmations_basissupport (id);

can be changed to:

ALTER TABLE programmations_concert ADD FOREIGN KEY (basis_support_id) REFERENCES programmations_basissupport (id);