django.db.utils.ProgrammingError: there is no unique constraint matching given keys for referenced table "accounts_user"

[naresh-kapsus]

Facing issue while migrating

`psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "accounts_user"

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/var/www/html/Kapsus-eCom-SAAS/manage.py", line 22, in main() File "/var/www/html/Kapsus-eCom-SAAS/manage.py", line 18, in main execute_from_command_line(sys.argv) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/core/management/init.py", line 425, in execute_from_command_line utility.execute() File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/core/management/init.py", line 419, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/core/management/base.py", line 373, in run_from_argv self.execute(*args, cmd_options) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/core/management/base.py", line 417, in execute output = self.handle(*args, *options) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/core/management/base.py", line 90, in wrapped res = handle_func(args, kwargs) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 253, in handle post_migrate_state = executor.migrate( File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/migrations/executor.py", line 126, in migrate state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/migrations/executor.py", line 156, in _migrate_all_forwards state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/migrations/executor.py", line 235, in apply_migration with self.connection.schema_editor(atomic=migration.atomic) as schema_editor: File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 126, in exit self.execute(sql) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django_multitenant/backends/postgresql/base.py", line 118, in execute super(DatabaseSchemaEditor, self).execute(statement) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 153, in execute cursor.execute(sql, params) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/backends/utils.py", line 99, in execute return super().execute(sql, params) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers return executor(sql, params, many, context) File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute with self.db.wrap_database_errors: File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/utils.py", line 90, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/var/www/html/Kapsus-eCom-SAAS/kes_env/lib/python3.10/site-packages/django/db/backends/utils.py", line 85, in _execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: there is no unique constraint matching given keys for referenced table "accounts_user" `

[gurkanindibay]

Hi @naresh-kapsus Thanks for your interest. Could you clarify more? If it is possible, could you share the project that you are getting this error? It seems a django problem not a library problem

[abhibhaw]

I'm getting the same error with postgres db, with sqllite it worked fine

[weslly99]

I'm getting the same error when use TenantForeignKey, using models.ForeignKey works fine :

models.py

from django.db import models

from django_multitenant.models import TenantModel
from django_multitenant.fields import TenantForeignKey

class Store(TenantModel):
  name =  models.CharField(max_length=50)
  address = models.CharField(max_length=255)
  email = models.CharField(max_length=50)

  class TenantMeta:
    tenant_field_name = "id"

class Historic(TenantModel):
  store = models.ForeignKey("store.Store",  on_delete=models.CASCADE)

  class TenantMeta:
    tenant_field_name = "store_id"

class Order(TenantModel):
    store = models.ForeignKey("store.Store", on_delete=models.CASCADE)
    historic = TenantForeignKey("order.Historic", on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now=False, auto_now_add=True)

    class Meta:
      unique_together = ["id", "store"]
      ordering = ("created_at",)

    class TenantMeta:
        tenant_field_name = "store_id"

class ProductItem(TenantModel):
  name = models.CharField(max_length=255)
  value = models.IntegerField()
  description = models.TextField()
  order = TenantForeignKey("order.Order", on_delete=models.CASCADE)
  store = models.ForeignKey("store.Store", on_delete=models.CASCADE)

  class TenantMeta:
    tenant_field_name = "store_id"

SQL generated by migration with TenantForeignKey

BEGIN;
--
-- Create model Historic
--
CREATE TABLE "order_historic" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "store_id" bigint NOT NULL);
--
-- Create model Order
--
CREATE TABLE "order_order" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "created_at" timestamp with time zone NOT NULL, "historic_id" bigint NOT NULL, "store_id" bigint NOT NULL);
--
-- Create model Store
--
CREATE TABLE "order_store" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "name" varchar(50) NOT NULL, "address" varchar(255) NOT NULL, "email" varchar(50) NOT NULL);
--
-- Create model ProductItem
--
CREATE TABLE "order_productitem" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "name" varchar(255) NOT NULL, "value" integer NOT NULL, "description" text NOT NULL, "order_id" bigint NOT NULL, "store_id" bigint NOT NULL);
ALTER TABLE "order_historic" ADD CONSTRAINT "order_historic_store_id_12492c4f_fk_store_store_id" FOREIGN KEY ("store_id") REFERENCES "store_store" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "order_historic_store_id_12492c4f" ON "order_historic" ("store_id");
ALTER TABLE "order_order" ADD CONSTRAINT "order_order_id_store_id_db25fe97_uniq" UNIQUE ("id", "store_id");
ALTER TABLE "order_order" ADD CONSTRAINT "order_order_historic_id_store_id_5065e251_fk_order_his" FOREIGN KEY ("historic_id", "store_id") REFERENCES "order_historic" ("id", "store_id") DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "order_order" ADD CONSTRAINT "order_order_store_id_337cf84c_fk_store_store_id" FOREIGN KEY ("store_id") REFERENCES "store_store" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "order_order_historic_id_ed1f1124" ON "order_order" ("historic_id");
CREATE INDEX "order_order_store_id_337cf84c" ON "order_order" ("store_id");
ALTER TABLE "order_productitem" ADD CONSTRAINT "order_productitem_order_id_store_id_2917b765_fk_order_ord" FOREIGN KEY ("order_id", "store_id") REFERENCES "order_order" ("id", "store_id") DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "order_productitem" ADD CONSTRAINT "order_productitem_store_id_7542325e_fk_store_store_id" FOREIGN KEY ("store_id") REFERENCES "store_store" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "order_productitem_order_id_c123ed0f" ON "order_productitem" ("order_id");
CREATE INDEX "order_productitem_store_id_7542325e" ON "order_productitem" ("store_id");
COMMIT;

Resulting error:

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, order, sessions, store
Running migrations:
  Applying order.0001_initial...Traceback (most recent call last):
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "order_historic"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/weslly-pc/Workspace/citus_test/manage.py", line 22, in <module>
    main()
  File "/home/weslly-pc/Workspace/citus_test/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/core/management/base.py", line 402, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/core/management/base.py", line 448, in execute
    output = self.handle(*args, **options)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/core/management/base.py", line 96, in wrapped
    res = handle_func(*args, **kwargs)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 349, in handle
    post_migrate_state = executor.migrate(
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 249, in apply_migration
    with self.connection.schema_editor(
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 164, in __exit__
    self.execute(sql)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django_multitenant/backends/postgresql/base.py", line 119, in execute
    super().execute(statement)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 199, in execute
    cursor.execute(sql, params)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/weslly-pc/Workspace/citus_test/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: there is no unique constraint matching given keys for referenced table "order_historic"

[cristiangrojas]

I'm facing the same issue, could you please provide some help?

[daleanplg]

Did you manage to resolve this? I am experiencing the same issue.

[daleanplg]

Any update on this issue? Common Citus team. What is the solution to this?

Using models.ForeignKey works but that wouldn't give us the additional join clauses automatically and includes the tenant_id. Using TenantForeignKey would, but whenever I use the TenantForeignKey I get the psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "account_branches.

[ayarshabeer]

@daleanplg Try to add Meta in ProductItem Model:

 class Meta:
      unique_together = ["id", "store"]

I think it should work.

[itsjef]

Not sure why this is not mentioned in the docs. In order to refer to a tenant-based model using TenantForeignKey, the referenced model must have a composite unique constraint on the PK and FK to the central tenant model.

For example, in @weslly99 's comment, Historic model must be defined as:

class Historic(TenantModel):
  store = models.ForeignKey("store.Store",  on_delete=models.CASCADE)

  class Meta:
    unique_together = ["id", "store"]  # or using UniqueConstraint

  class TenantMeta:
    tenant_field_name = "store_id"

---

No need to define it in ProductItem since it isn't referred anywhere. However I'd recommend you update it just in case.