cross schema relation in migration. is it possible to foreign key of a table from public schema to tenant schema in postgresql?

[naveedscript]

user table in public schema

  use Ecto.Migration

  def change do
    execute("CREATE EXTENSION IF NOT EXISTS pgcrypto")
    execute("CREATE TYPE gender_t AS ENUM ('male', 'female', 'other')")
    execute("CREATE TYPE user_t AS ENUM ('teacher', 'student', 'admin', 'other')")

    create table(:users, primary_key: false) do
      add(:id, :uuid, primary_key: true)
      add(:email, :string, null: false)
      add(:phone, :string, null: false)
      add(:user_type, :user_t, null: false)
      add(:gender, :gender_t, null: false)
      add(:hash_password, :string)
      add(:first_name, :string)
      add(:last_name, :string)
      add(:username, :string, null: false)
      add(:deleted_at, :utc_datetime)
      add(:is_active, :boolean, default: true)
      timestamps()
    end

    create unique_index(:users, [:email])
    create unique_index(:users, [:username])
  end
end```

Institute Table in tenant schema 

```defmodule Data.Repo.Migrations.CreateInstitutes do
  use Ecto.Migration

  def change do
    create table(:institutes, primary_key: false) do
      add(:id, :uuid, primary_key: true)
      add(:title, :string, null: false)
      add(:established_at, :utc_datetime)
      add(:contact_no, :string, null: false)
      add(:image, :string)
      add(:email, :string)
      add(:location, :string)
      add(:deleted_at, :utc_datetime)
      # add(:inserted_by_id, references(:users, column: :id, type: :uuid))
      # add(:updated_by_id, references(:users, column: :id, type: :uuid))
      # add(:deleted_by_id, references(:users, column: :id, type: :uuid))

      timestamps()
    end

  end
end```


For adding user_id to institute table 

```defmodule Data.Repo.Migrations.AddUserIdToInstituteTable do
  use Ecto.Migration
  @fk_name "institutes_users_fkey"
  def up do
    prefix = Ecto.Migration.prefix
    query = "alter table #{prefix}.institutes add constraint #{@fk_name} foreign key (user_id) references public.users(id)"
             IO.inspect(query)
    Ecto.Adapters.SQL.query!(Data.Repo, query, [])
  end
end```




I'm using Triplex lib for multitenancy

[kelvinst]

Sorry I took so long to answer @naveedscript. But yeah, that should be possible, are you getting any error from it?

[kelvinst]

Ping @naveedscript

[aseigo]

FWIW, I have done exactly this and it works just fine. I did determine while testing that that one needs to be aware that it will create an absurd number of constraints on the public table (one for every tenant, as each will have their own unique FK reference). But it does work.

[kelvinst]

Thanks for the review @aseigo. Will close the issue, let me know if you keep having problems @naveedscript.

[kelvinst]

Oops, actually, I have no permission to triplex Repo anymore, @petersonfs can you close this issue?