Code

Opened 4 years ago

Closed 4 years ago

#12614 closed (invalid)

ManyToManyField generates inconsistent table/column names

Reported by: Nick Retallack <nickretallack@…> Owned by: nobody
Component: Database layer (models, ORM) Version: 1.1
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

If you override the table name in your models and then generate a table with a ManyToManyField, the results look horribly inconsistent. Take a look:

### module_name/models.py
from django.db import models

# Create your models here.
class ModelName(models.Model):
    class Meta:
        db_table = "table_name"
    
class OtherModelName(models.Model):
    class Meta:
        db_table = "other_table_name"
    property_name = models.ManyToManyField(ModelName)

### ./manage.py sql column # as sqlite3
BEGIN;
CREATE TABLE "table_name" (
    "id" integer NOT NULL PRIMARY KEY
)
;
CREATE TABLE "other_table_name" (
    "id" integer NOT NULL PRIMARY KEY
)
;
CREATE TABLE "other_table_name_property_name" (
    "id" integer NOT NULL PRIMARY KEY,
    "othermodelname_id" integer NOT NULL REFERENCES "other_table_name" ("id"),
    "modelname_id" integer NOT NULL REFERENCES "table_name" ("id"),
    UNIQUE ("othermodelname_id", "modelname_id")
)
;
COMMIT;

This is especially annoying if you're working with a legacy database that already has a consistent naming scheme, or if you care at all what your tables and columns are named. You would have to add a "through" option to every ManyToManyField and specify that table manually, since Django insists on using class names and property names in the table definition when it generates it itself.

In my opinion, class names and property names should never be used in database table/column definitions. Only the real table names should be used. In specific, the many to many table should have looked like this:

CREATE TABLE "other_table_name_table_name" (
    "id" integer NOT NULL PRIMARY KEY,
    "other_table_name_id" integer NOT NULL REFERENCES "other_table_name" ("id"),
    "table_name_id" integer NOT NULL REFERENCES "table_name" ("id"),
    UNIQUE ("other_table_name_id", "table_name_id")
);

Attachments (0)

Change History (4)

comment:1 Changed 4 years ago by Nick Retallack <nickretallack@…>

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Oops, typo here:

### ./manage.py sql column # as sqlite3

Should have been

### ./manage.py sql module_name # as sqlite3

to be consistent with the annotation for the previous code snippet. Shouldn't affect the issue.

comment:2 follow-up: Changed 4 years ago by ramiro

Replying to Nick Retallack <nickretallack@gmail.com>:

If you override the table name in your models and then generate a table with a ManyToManyField, the results look horribly inconsistent.

It is generating those names as it is described in the documentation: automatic intermediate m2m table name and FK fields column names http://docs.djangoproject.com/en/1.1/ref/models/fields/#database-representation.

If you want to control the name of the table you can use the db_table field option as documented.

For controlling the FK column names you will need to use a through as you have discovered or crate the table by hand then you will have total control over its naming.

comment:3 in reply to: ↑ 2 Changed 4 years ago by Nick Retallack <nickretallack@…>

Replying to ramiro:

It is generating those names as it is described in the documentation: automatic intermediate m2m table name and FK fields column names http://docs.djangoproject.com/en/1.1/ref/models/fields/#database-representation.

Actually it's not consistent with the documentation you linked to. The "DATABASE REPRESENTATION" paragraph says "By default, this table name is generated using the names of the two tables being joined. " However, the table is actually being named after a table and a property, not two tables.

Also, regardless of what the documentation says, do you think the current default behavior is sane? Think about this from the perspective of someone who has to use this database later on, without the django ORM. It seems silly that "property_name", "modelname", and "othermodelname" all appear in the generated schema, despite having nothing to do with the database structure.

comment:4 Changed 4 years ago by russellm

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

Using "other_table_name_table_name" doesn't work because you can have two m2m fields onto the related table. If you used "other_table_name_table_name" as the m2m table name, the intermediate table name would be ambiguous. You need to use the property name to compose the m2m table name to avoid this ambiguity.

As Ramiro said - if you need control over the m2m table name, use the db_table option on the m2m field.

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.