[Django] #35956: Add composite foreign keys

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Type: New
| feature
Status: new | Component: Database
| layer (models, ORM)
Version: dev | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
This is a follow up to #373 (CompositePrimaryKey).

Now that composite primary keys are merged, it would be great to be able
to create foreign keys referencing them through the Django ORM.

My proposal is to add 2 parameters to `ForeignKey`: `from_fields` and
`to_fields`.
They would map to the underlying `ForeignObject`'s `from_fields`,
`to_fields` parameters.

If a `ForeignKey` has multiple fields, it acts as a ''virtual field'',
meaning it doesn't create a database column automatically.
--
Ticket URL: <https://code.djangoproject.com/ticket/35956>
Django <https://code.djangoproject.com/>
The web framework for perfectionists with deadlines.

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------

Reporter: Csirmaz Bendegúz | Owner: (none)

Type: New feature | Status: new

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

Replying to [ticket:35956 Csirmaz Bendegúz]:

> My proposal is to add 2 parameters to `ForeignKey`: `from_fields` and
`to_fields`.

Do we need these parameters? `CompositePrimaryKey` is always a primary key
so its fields should be detected automatically, IMO, the following example
should work:
{{{#!python
class Release(models.Model):
pk = models.CompositePrimaryKey("version", "name")
version = models.IntegerField()
name = models.CharField(max_length=20)


class RefRelease(models.Model):
release = models.ForeignKey("Release", models.CASCADE)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:2>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* cc: Mariusz Felisiak (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:3>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Csirmaz Bendegúz):

Replying to [comment:2 Mariusz Felisiak]:

> Replying to [ticket:35956 Csirmaz Bendegúz]:
> > My proposal is to add 2 parameters to `ForeignKey`: `from_fields` and
`to_fields`.
>
> Do we need these parameters? `CompositePrimaryKey` is always a primary
key so its fields should be detected automatically, IMO, the following
example should work:
> {{{#!python
> class Release(models.Model):
> pk = models.CompositePrimaryKey("version", "name")
> version = models.IntegerField()
> name = models.CharField(max_length=20)
>
>
> class RefRelease(models.Model):
> release = models.ForeignKey("Release", models.CASCADE)
> }}}

The issue with this example is `RefRelease` must have 2 fields matching
the `CompositePrimaryKey` (an `IntegerField()` and a
`CharField(max_length=20)`).
So `release` would need to create 2 fields implicitly and I'm not sure if
this would be a good direction?
We should allow sharing fields between composite foreign keys, for example
in a multitenant app, the `tenant_id` should be shared between multiple
composite foreign keys.
Let me know what you think, I'm happy to consider any alternatives.
--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:4>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* cc: Clifford Gama (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:5>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Csirmaz Bendegúz):

I don't have the time to work on this right now
--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:6>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Clifford Gama):

Replying to [comment:4 Csirmaz Bendegúz]:

> Replying to [comment:2 Mariusz Felisiak]:

> > Do we need these parameters? `CompositePrimaryKey` is always a primary
key so its fields should be detected automatically, IMO, the following
example should work:
> > {{{#!python
> > class Release(models.Model):
> > pk = models.CompositePrimaryKey("version", "name")
> > version = models.IntegerField()
> > name = models.CharField(max_length=20)
> >
> >
> > class RefRelease(models.Model):
> > release = models.ForeignKey("Release", models.CASCADE)
> > }}}
>
> The issue with this example is `RefRelease` must have 2 fields matching
the `CompositePrimaryKey` (an `IntegerField()` and a
`CharField(max_length=20)`).
>

> So `release` would need to create 2 fields ''implicitly'' and I think
that's too restrictive.
>
> We should allow **sharing fields between composite foreign keys**.

I'm +1 on sharing fields, but I think that Felix's suggestion should work
as the default.

In simple cases, having the foreign key automatically infer and create the
needed fields is great developer experience — it keeps things intuitive
and close to how `ForeignKey` works today.

That said, we should definitely allow shared fields. For example, if a
model already has a `tenant_id` FK to `Tenant`, and also needs to
reference a model with a composite PK like `(tenant_id, id)`, it should be
able to reuse the same `tenant_id` field in both relationships.

So if possible, maybe we should have implicit field creation as default,
and `to|from_fields` as an opt-in for more advanced use cases
--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:7>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Clifford Gama):

Thinking about it further... sharing fields only work when the tenant in
both relationships is always the same — which might not always be true and
may possibly lead to data integrity issues. For example, a model might
have a direct FK to `Tenant`, and also reference another model with a
composite PK like (tenant_id, id). Even though both use tenant_id, they
could point to different tenants unless explicitly constrained. Pointing
to the same field here would actually introduce a bug

I suppose this weakens the case for shared fields. It can be useful, but
in very specific cases, like, say modeling tight tenant-scoped
relationships — i.e., when you know the tenant in both FKs is always the
same and want to enforce that via shared fields.
--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:8>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Csirmaz Bendegúz):

Replying to [comment:7 Clifford Gama]:

> In simple cases, having the foreign key automatically infer and create
the needed fields is great developer experience — it keeps things
intuitive and close to how ForeignKey works today.

I think I agree, but it's going to be a lot more complex to implement

> It can be useful, but in very specific cases, like, say modeling tight
tenant-scoped relationships — i.e., when you know the tenant in both FKs
is always the same and want to enforce that via shared fields.

Yeah that's what I meant, in a multi-tenant database you never want one
tenant's records to point to another tenant's records
--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:9>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by David Sanders):

Just FYI to folks subscribed to this ticket:

In addition to having composite FKs for multitenancy, there's also the new
temporal PK/FK feature in Postgres 18 (currently beta) that will be useful
for declaring temporal tables: https://www.depesz.com/2024/10/03/waiting-
for-postgresql-18-add-temporal-foreign-key-contraints/

tl;dr it adds the new `PERIOD` qualifier to FKs for declaring timestamp
range constraints, which will likely be used in a composite key alongside
the record identifier.

Multitenancy I already use frequently and I anticipate I'll make quite
good use of temporal keys :)
--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:10>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by David Sanders):

* cc: David Sanders (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:11>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------

Reporter: Csirmaz Bendegúz | Owner: David
| Sanders
Type: New feature | Status: assigned

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by David Sanders):

* owner: (none) => David Sanders
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:12>

[Django]

#35956: Add composite foreign keys
-------------------------------------+-------------------------------------
Reporter: Csirmaz Bendegúz | Owner: David
| Sanders
Type: New feature | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* cc: Simon Charette (added)

Comment:

For anyone interested in enforcing composite foreign key constraints until
this ticket gets fixed you might be interested in
[https://github.com/charettes/django-fk-constraint this package] that adds
the missing part of top of `ForeignObject` to enforce validation and
constraint creation and enforcement.

It can be used like the following

{{{#!python
class Tenant(models.Model):
name = models.CharField()


class TenantModel(models.Model):
tenant = models.ForeignKey(
Tenant,
models.CASCADE,
)
uuid = models.UUIDField(default=uuid.uuid4)

pk = models.CompositePrimaryKey("tenant", "uuid")

class Meta:
abstract = True


class Product(TenantModel):
name = models.CharField()


class ProductPrice(TenantModel):
product_uuid = models.UUIDField()
product = models.ForeignObject(
Product,
models.CASCADE,
from_fields=["tenant", "product_uuid"],
to_fields=["tenant", "uuid"],
)
price = models.DecimalField(max_digits=10, decimal_places=2)

class Meta:
constraints = [
ForeignKeyConstraint(
Product,
from_fields=["tenant", "product_uuid"],
to_fields=["tenant", "uuid"],
name="product_price_product_fk",
)
]
}}}

TL;DR use `ForeignObject` and define a `ForeignKeyConstraint` with a
similar signature.

This works as `ForeignKey` in its current form is basically ''sugar'' to

1. Define a concrete field
2. Define a `ForeignObject`
3. Define the equivalent of `ForeignKeyConstraint`

In other words, these two definitions are equivalent

{{{#!python
class Book(models.Model):
author_id = models.IntegerField()
author = models.ForeignObject(
Author, models.CASCADE, from_fields=["author_id"],
to_fields=["id"]
)

class Meta:
constraints = [
ForeignKeyConstraint(
Author,
from_fields=["author_id"],
to_fields=["id"],
name="book_author_fk",
)
]
}}}

and

{{{#!python
class Book(models.Model):
author = models.ForeignKey(
Author, models.CASCADE
)
}}}

Now, whether something similar to `ForeignKeyConstraint` should exist as
standalone documented form is debatable but getting `ForeignKey` ''sugar''
to support multiple fields without it is going to be challenging as it
will require figuring out what should be done with `attname` (AKA the
implicit `_id` field).

Until we have non-pk composite fields support I suspect we'll have to make
the field fully ''virtual'' and require that concrete local `from_fields`
are specified. That is making `.attname` be `None` and limit the ''sugar''
to injecting a `constraints` entry as when a field is virtual (it's
`db_column is None`) the schema editor completely ignores it.
--
Ticket URL: <https://code.djangoproject.com/ticket/35956#comment:13>