citusdata/django-multitenant

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

naresh-kapsus opened this issue · 8 comments

naresh-kapsus commented

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 commented

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 commented

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

weslly99 commented

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 commented

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

daleanplg commented

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

daleanplg commented

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 commented

@daleanplg Try to add Meta in ProductItem Model:

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

I think it should work.

itsjef commented

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.