Add composite foreign keys

[Csirmaz Bendegúz]

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.

[Mariusz Felisiak]

Replying to 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:

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)

[Csirmaz Bendegúz]

Replying to 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:

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.

For example, in a multi-tenant app where each model has a CompositePrimaryKey("tenant_id", "id"), the tenant_id field should be shared between composite foreign keys.

Let me know what you think about this, I'm happy to consider alternatives.

[Csirmaz Bendegúz]

I don't have the time to work on this right now, if someone wants to pick it up, feel free to do so

[Clifford Gama]

Replying to Csirmaz Bendegúz:

Replying to 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:

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

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

[Csirmaz Bendegúz]

Replying to 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

[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 (using contains), 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 :)

[Simon Charette]

For anyone interested in enforcing composite foreign key constraints until this ticket gets fixed you might be interested in ​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

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

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

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.