Support customize ForeignKey DDL when db_constraint=False

[elonzh]

Sometimes we want to join tables without foreignkeys, so we define a ForeignKey field with db_constraint=False to achieve this.

For example:

class Journal(Model):
    issn = models.CharField(max_length=9, primary_key=True)
    name = models.CharField(max_length=255)

class Biblio(Model):
    # There already exists a field called issn in the Biblio model
    # issn = models.CharField(max_length=50, default="", blank=True)
    journal = models.ForeignKey(
        Journal,
        related_name="+",
        db_column="issn",
        db_constraint=False,
        db_index=False,
        on_delete=models.DO_NOTHING,
        default="",
        blank=True,
        max_length=50,
    )

In this example, we define a journal field from issn field and we want to do a fake migration.

But the migratation will generate DDL like this:

--
-- Remove field issn from biblio
--
ALTER TABLE `userlibrary_biblio` DROP COLUMN `issn`;
--
-- Add field issn_record to biblio
--
ALTER TABLE `userlibrary_biblio` ADD COLUMN `issn` varchar(9) DEFAULT '' NOT NULL;
ALTER TABLE `userlibrary_biblio` ALTER COLUMN `issn` DROP DEFAULT;

which is kind of awkward because that means Django will force max_length=9 and this is not we want.

[Mariusz Felisiak]

db_constraint is only to disable creation of foreign key constraints in the tables and to allow for handling legacy database structures (if you don't other choice). It has nothing to do with a column data type that should always match a data type of the referenced column. Moreover, max_length is not a proper keyword argument for ForeignKey and as such is ignored.

[elonzh]

Replying to Mariusz Felisiak:

db_constraint is only to disable creation of foreign key constraints in the tables and to allow for handling legacy database structures (if you don't other choice). It has nothing to do with a column data type that should always match a data type of the referenced column. Moreover, max_length is not a proper keyword argument for ForeignKey and as such is ignored.

I know those parameters are intentionally ignored, what I want is joining tables without db_constraint. Django doesn't provide a method to achieve this, so I define a fake ForeignKey as a workround.

I am curious that since it is allowed to set db_constraint=True for handling legacy database structures, why max_length should be always match a data type of the referenced column.

This behavior will make unnessary migrations for legacy database structures, right?

[Natalia Bidart]

Replying to elonzh:

[...] I want is joining tables without db_constraint. Django doesn't provide a method to achieve this, so I define a fake ForeignKey as a workround.

Another way of achieving this is by using a slightly more manual approach involving subqueries. You could also consider seeking more help or ideas in how to resolve this without using a foreign key by posting your question n the ​Django Forum, or using any of the user support channels from ​this link.

[Simon Charette]

I think this will eventually come to core in a form or the other through a Relation object ​that can be defined at the model level but in the mean time your best bet is to use (at your own risks) the undocumented django.db.models.ForeignObject object to define such relationships.

class Journal(Model):
    issn = models.CharField(max_length=9, primary_key=True)
    name = models.CharField(max_length=255)

class Biblio(Model):
    issn = models.CharField(max_length=50, default="", blank=True)
    journal = models.ForeignObject(
        Journal,
        models.DO_NOTHING,
        from_fields=["issn"],
        to_fields=["issn"],
    )

Under the hood ForeignKey is Field + ForeignObject + the equivalent of a ForeignKeyConstraint(ForeignObject) entry in Model._meta.constraints so if you care about the ORM + JOIN generation part and don't want a database constraint it's likely what you're looking for.

In other words, doing

class Journal(Model):
    issn = models.CharField(max_length=9, primary_key=True)
    name = models.CharField(max_length=255)

class Biblio(Model):
    journal = models.ForeignKey(Journal, models.ON_DELETE, null=True)

Is roughly the sugared equivalent of doing

class Journal(Model):
    issn = models.CharField(max_length=9, primary_key=True)
    name = models.CharField(max_length=255)

class Biblio(Model):
    issn = models.CharField(max_length=50, null=True, db_index=True)
    journal = models.ForeignObject(
        Journal,
        models.ON_DELETE,
        from_fields=["issn"],
        to_fields=["issn"],
    )
    class Meta:
        constraints = [
            ForeignKeyConstraint(Journal, from_fields=["issn"], to_fields=["issn"])  # XXX: This currently doesn't exist but it could
        ]

[elonzh]

Replying to Simon Charette:

Thanks for your suggestion, it's very helpful.