Creating tables under MySQL4.1 fails if constraint names are not unique

[filipwasilewski@…]

This happens when using MySQL 4.1 on Windows. In the example below two tables (SomethingB1 and SomethingB2) refers to third one (Something) via foreign key. In the generated sql the constraint names are identical for these two tables (something_id_referencing_test_something_id).

Possible solution could be including name of table in the name of it's constraint.

This may be MySQL version dependent, but I can't verify that. Works fine with sqlite.

See the code below:

from django.db import models

class Something(models.Model):
    name = models.CharField(maxlength=50)

class SomethingB1(models.Model):
    something = models.ForeignKey(Something)
    name = models.CharField(maxlength=50)

class SomethingB2(models.Model):
    something = models.ForeignKey(Something)
    name = models.CharField(maxlength=50)

CREATE TABLE `test_somethingb1` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `something_id` integer NOT NULL,
    `name` varchar(50) NOT NULL
);
CREATE TABLE `test_somethingb2` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `something_id` integer NOT NULL,
    `name` varchar(50) NOT NULL
);
CREATE TABLE `test_something` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(50) NOT NULL
);
ALTER TABLE `test_somethingb1` ADD CONSTRAINT `something_id_referencing_test_something_id` FOREIGN KEY (`something_id`) REFERENCES `test_something` (`id`);
ALTER TABLE `test_somethingb2` ADD CONSTRAINT `something_id_referencing_test_something_id` FOREIGN KEY (`something_id`) REFERENCES `test_something` (`id`);
CREATE INDEX test_somethingb1_something_id ON `test_somethingb1` (`something_id`);
CREATE INDEX test_somethingb2_something_id ON `test_somethingb2` (`something_id`);

[bangcok_dangerus (at) hotmail.com]

I can confirm that I've experienced the same problem using MySQL 4.1.14 on Linux (Slackware 10.2).
The following code in in models.py adds two constraints with the same name, creating an error during "manage.py syncdb"

class Course(models.Model):
	#etc...

class AvailableCourse(models.Model):
	course = models.ForeignKey(Course)
	#etc...

class Report(models.Model):
	course = models.ForeignKey(Course)
	#etc...

The code created by "manage.py syncdb" that causes the error is:

ALTER TABLE `acadm_availablecourse` ADD CONSTRAINT `course_id_referencing_acadm_course_id` FOREIGN KEY (`course_id`) REFERENCES `acadm_course` (`id`);
ALTER TABLE `acadm_report` ADD CONSTRAINT `course_id_referencing_acadm_course_id` FOREIGN KEY (`course_id`) REFERENCES `acadm_course` (`id`);

This is unresolved in the most recent version ("svn update" run on 11/7/2006). A quick fix (as also mentioned in ticket #2257) would be to include the table name in the constraint name, or to include a hash.

-Basil.

[Malcolm Tredinnick]

This and #2257 are talking about the same problem. Let's merge them into one.