Opened 18 years ago

Closed 18 years ago

#2230 closed defect (duplicate)

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

Reported by: filipwasilewski@… Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version: dev
Severity: normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (2)

comment:1 by bangcok_dangerus (at) hotmail.com, 18 years ago

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.

comment:2 by Malcolm Tredinnick, 18 years ago

Resolution: duplicate
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top