ManyToManyField generates inconsistent table/column names

[Nick Retallack <nickretallack@…>]

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

[Nick Retallack <nickretallack@…>]

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.

[Ramiro Morales]

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.

[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.

[Russell Keith-Magee]

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.