Opened 18 years ago

Closed 18 years ago

Last modified 17 years ago

#2257 closed defect (fixed)

[patch] syncdb should generate unique constraint names with hashes

Reported by: mir@… Owned by: Malcolm Tredinnick
Component: Core (Management commands) Version:
Severity: normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Take a look at how syncdb names constraints:

backend.quote_name('%s_referencing_%s_%s' % (r_col, table, col))

In some circumstances, syncdb generates the same constraint name (for foreign keys= for different tables. Now, using both table names in the name can lead to too long names, so this isn't a solution. I propose to use a hash of both table names + the two attribute names. A quick, easy and dirty version is:

backend.quote_name('%s_referencing_%s_%x' % (r_col, col, hash((r_table, table))))

Here's an example.

class Man(models.Model):
  father = ForeignKey(Man, null=True)

class Woman(models.Model):
  father = ForeignKey(Man, null=True)

These will (currently) both create the constraint 'father_referencing_man_id' (resulting in incomprehensible error message in case of mysql)

Attachments (1)

2257_fullname_1.patch (1.2 KB ) - added by bill@… 18 years ago.
supplies db wide unique fk names (for mysql)

Download all attachments as: .zip

Change History (7)

comment:1 by Malcolm Tredinnick, 18 years ago

Owner: changed from Adrian Holovaty to Malcolm Tredinnick

comment:2 by Malcolm Tredinnick, 18 years ago

Resolution: fixed
Status: newclosed

The auto-closer doesn't seem to want to pick this up (in [3373]):

Fixed #2257 -- MySQL wants constraint names to be unique per-database, so fixed
the SQL generation to ensure this.

comment:3 by Malcolm Tredinnick, 18 years ago

Resolution: fixed
Status: closedreopened

David Avraamides pointed out (politely) that I suck. The solution in [3373] only works when all references are generated at the same time. I'll write a better fix shortly; reopening for now, since people seem to be hitting this about once a day.

by bill@…, 18 years ago

Attachment: 2257_fullname_1.patch added

supplies db wide unique fk names (for mysql)

comment:4 by bill@…, 18 years ago

Summary: syncdb should generate unique constraint names with hashes[patch] syncdb should generate unique constraint names with hashes

patch produces unique fk names. This one uses the full table names in the fk name (I have another that uses the col names and hashes the two tablenames at the end if you want it). The memo dict reference_names has been taken out.

comment:5 by Malcolm Tredinnick, 18 years ago

Resolution: fixed
Status: reopenedclosed

(In [3512]) Fixed #2257 -- make constraint names unique across all table/column
combinations (for the benefit of MySQL).

comment:6 by Malcolm Tredinnick, 18 years ago

Thanks for the patch, Bill, but I ended up going with a hash of the table names at the end, just to save a few characters.

For people reading this in the future, the reason I am not just punting this and using no name so that MySQL can make on up is because it makes debugging a little easier to see a constraint name that makes sense when you violate it. It's worth a little pain to try and get that right.

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