Code

Opened 8 years ago

Closed 8 years ago

#2230 closed defect (duplicate)

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

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

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

Attachments (0)

Change History (2)

comment:1 Changed 8 years ago by 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.

comment:2 Changed 8 years ago by mtredinnick

  • Resolution set to duplicate
  • Status changed from new to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.