cross schema relation in migration. is it possible to foreign key of a table from public schema to tenant schema in postgresql? #79
Description
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