Have Geek Brain

Error 1005 while adding foreign key constraint in mysql table

I have a problem updating my django and mysql app from the south.

I tried to do a sql based update with the code generated by 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`);

When adding an FK constraint, an error occurs:

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

Does anyone have any ideas?

Update: DEFAULT values ​​that are missing, but even if I add the default value '' in django model, foreign key generation fails.

thanks for the help

+2


a source to share


3 answers


I finally fixed the issue thanks to a workaround. The change works fine on my dev machine as long as it doesn't work on the host. I didn't find the cause, but I managed to get it to work by exporting, transferring to my dev machine and moving around.



+1


a source


It looks like you are trying to add a constraint with a pre-existing symbol / name.

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`);
+3


a source


There was the same problem. Finally, I found that the referenced field of the table was "unsigned", but the reference table was Not unsigned.

+2


a source






More articles:


All Articles