Foreignkey relation from model in tenant schema to model in public schema

[ragsagar]

Is it possible to have a ForeignKey relation (eg: created_by field) from a model in a tenant schema (tenant app) to the User model which resides in the public schema (shared app) ?

[rj76]

Yes, my setup currenlty is doing this. Use normal fk in django models and it'll reference the public schema in the table constraint definition.

In tenant table:

CONSTRAINT order_order_member_id_03731e9c_fk_member_member_id FOREIGN KEY (member_id)
      REFERENCES public.member_member (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED

[ragsagar]

When I am deleting some User objects which resides in the public schema, I am getting the following error: ProgrammingError: relation "tenantapp_modelname" does not exist.

Why is this happening? tenantapp_modelname has a FK relation to User model.

[rj76]

you have to switch to the public schema context for your connection

from django.db import connection

from django_tenants.utils import get_public_schema_name

connection.set_schema_to_public()
model.delete()

connection.set_tenant(some_tenant) # restore context

[ragsagar]

Thanks @rj76. That makes sense, I will try out and let you know.

[ragsagar]

i did the following. But i am still getting the same error.

from django.db import connection
from tenant_schemas.utils import get_public_schema_name
from accounts.models import User

connection.set_schema(get_public_schema_name())
User.objects.filter(pk__in=[2, 4, 5]).delete()
# same error.

[rj76]

Are you sure that model exist in the public schema? It gave me some headaches in the past where models didn't end up where I expected them to end up when switching existing models to public...

[rj76]

Ehm, sorry. I still seem to be using https://pypi.python.org/pypi/django-tenants. Not sure how much the projects differ.

[ragsagar]

i don't know how django-tenant-schemas will handle this scenario, in public schema those models in tenant apps are absent, So when i delete a model object in public schema with relation to a model in tenant schema, it raises an exception that the table doesn't exist in public schema.

[Viatrak]

While, yes, it is possible to have a model in a tenant that has a FK to a model in the public schema, you need to be careful (especially during delete) and may need to handle the cascade delete manually.

This is a cascading delete, which means that when you attempt to delete the public model it tries to delete any models that have a FK to it as well. This means it needs access to the model in the tenant, which is only possible if the tenant's schema is set. Since the public schema is appended to the end of the postgres schema search path, it will be able to access both the tenant's model AND the public model (assuming the tenant doesn't also have a model with the same name).

So the short answer is, you actually need the connection schema set to the tenant's schema that has the FK to the public model, NOT set to the public schema.

If multiple tenants have FKs to that public schema model (which is possible, btw), an automatic cascading delete is actually not possible. You would have to iterate over all tenant schemas that have a FK link to the public model, and delete any models with that FK, and then proceed to delete the model in the public schema. The reason is that a postgres search path will short-circuit and only access the first schema with a model found. So while in the single case, it works and falls through to public, in the multiple tenant FK case, all schemas won't be fixed up properly.