Django GeneratedField as ForeignKey with referential integrity

I'm trying to create a generated field that is also a foreign key to another table, while maintaining referential integrity at the database level.

Basically, I'm trying to have the same effect as the following SQL, but in Django

CREATE TABLE parent(
  id TEXT PRIMARY KEY
);

CREATE TABLE child(
  id TEXT PRIMARY KEY,
  data JSONB,
  parent_id TEXT GENERATED ALWAYS AS (data->>'parent') STORED REFERENCES parent(id)
);

I have successfully managed to create the generated field using Django 5.0 GeneratedField

class Parent(models.Model):
    id = models.TextField(primary_key=True)

class Child(models.Model):
    id = models.TextField(primary_key=True)
    data = models.JSONField()
    parnet_id = models.GeneratedField(expression=models.fields.json.KT('data__parent'), output_field=models.TextField(), db_persist=True)

Now the problem is: how can I make this field also a foreign key? Because using ForeignKey would create a new column in the database that is not generated.

I tried using ForeignObject, since it supports using an existing field as a foreign key, but the foreign key constraint was not created at the database level.

class Parent(models.Model):
    id = models.TextField(primary_key=True)

class Child(models.Model):
    id = models.TextField(primary_key=True)
    data = models.JSONField()
    parnet_id = models.GeneratedField(expression=models.fields.json.KT('data__parent'), output_field=models.TextField(), db_persist=True)
    parent = models.ForeignObject(Parent, from_fields=['parnet_id'], to_fields=['id'], on_delete=models.CASCADE)

This generates the following SQL, which does not have a foreign key constraint

CREATE TABLE "myapp_parent" ("id" text NOT NULL PRIMARY KEY);
CREATE TABLE "myapp_child" ("id" text PRIMARY KEY, "data" jsonb NOT NULL, "parent_id" text GENERATED ALWAYS AS (("data" ->> 'parent_id')) STORED);

Notes:

  • I use Postgres.
  • I don't want to depend on pre_save and post_save signals, because I'll be inserting it using bulk_create, which doesn't support them.
  • I want to keep the column generated, to maintain integrity. Please don't suggest to not use generated columns.
  • I prefer to not write custom SQL in migrations by hand, unless absolutely necessary.

Thank you in advance <3

Вернуться на верх