Syncdb generates non-unique foreign key constraints

[DavidA]

I mistakenly posted this in django-users so reposting here...

--
I've been having a problem rebuilding my database from scratch via
syncdb. I've tracked it down to duplicate constraint name. Here is the
output from manage.py sql for my app:

ALTER TABLE `data_rawinst` ADD CONSTRAINT
`inst_id_referencing_data_inst_id` FOREIGN KEY (`inst_id`) REFERENCES
`data_inst` (`id`);
ALTER TABLE `data_instmap` ADD CONSTRAINT
`inst_id_referencing_data_inst_id` FOREIGN KEY (`inst_id`) REFERENCES
`data_inst` (`id`);

Note that I have two tables, both with FK's to data_inst and its
generating the same constraint name
(`inst_id_referencing_data_inst_id`). It seems the source table should
be part of that name, such as
`data_rawinst_inst_id_referencing_data_inst_id`.

I'm on the trunk at Rev 3350.

This used to work, I'm pretty sure, but I haven't rebuilt the whole DB
from scratch for a long time so I don't know if its been lingering for
a while.

Thanks,
-Dave

[Michael Radziej]

DavidA wrote:
> I've been having a problem rebuilding my database from scratch via
> syncdb. I've tracked it down to duplicate constraint name. Here is the
> output from manage.py sql for my app:

> ...

> This used to work, I'm pretty sure, but I haven't rebuilt the whole DB
> from scratch for a long time so I don't know if its been lingering for
> a while.

The algorithm for naming the constraints has changed since the
original way created names that were too long for mysql.

See ticket #2257. There's not a patch, but the idea of the solution.

Michael

[DavidA]

Thanks. I'll try the suggestion in the ticket.

I also did a little more research on this and found this MySQL bug
report:
http://bugs.mysql.com/bug.php?id=13942

One thing to note is that in MySQL if you don't name the constraint, it
automatically creates one of the form: <table_name>_ibfk_# where # is a
number, ensuring its unique, similar to the suggestion in the ticket.
(But as the MySQL bug report points out, very long table names can
generate constraint names that exceed the 64 character limit).

Since the SQL to generate FK constraints is somewhat non-standard,
wouldn't it make more sense if the generation of the constraint
statement was handled in the backend where you can employ more
DB-specific logic to it? Right now the statements is mostly built up in
django.core.management.syncdb

-Dave