Hello elixir folks, I am new in elixir programming and I am trying to migrate two databases with a foreign key in both tables but it doesn’t seem to work ??
def change do
create table(:users) do
add :username, :string
add :email, :string
add :name, :string
add :password, :string
add :address, :string
timestamps()
end
alter table("users") do
add :organization, references(:organization)
end
end
def change do
create table(:organization) do
add :org_key, :string
add :name, :string
timestamps()
end
create unique_index(:organization, [:org_key])
alter table("organization") do
add :creator, references(:users)
end
end
By default Ecto is going to use the singular name of the table with _id
appended for a FK.
If you want to use different column names I would check out the documentation for references/2
Ecto.Migration — Ecto SQL v3.6.1
The migrations can’t be done, because every table depends on the another, i am trying to implement bidirectionally foreign key and idea??
Ah, you may have to disable the transactions. Try adding this @disable_ddl_transaction true
at the top of your migration module
Not working , I am getting “(Postgrex.Error) ERROR 42P01 (undefined_table) relation “users” does not exist” error
One or the other of these migrations will need to be split into two parts - the table being referenced needs to exist before the foreign key can be added.
Something like:
- create
users
, don’t add the foreign key
- create
organizations
with a foreign key to users
- add a foreign key from
users
to organizations
2 Likes
I tried it, the last step when I add a foreign key from users to organizations the migration doesn’t apply
If it’s failing with the undefined_table
error mentioned in your previous message, that means the first step (creating the users
table) did not work.
I suspect you’ve got migrations which don’t match the actual database - it’s easy to get them out-of-sync when editing and re-running - you may want to recreate your DB, or clean up schema_migrations
manually.
it’s not failing, the user table migration works, after the organization migrate too but without applying the changes on the users’ table (alter table not working )
You’ll need to do it in 3 steps.
- Create the
users
table (without organization
FK referencing organization
since that table doesn’t exist at this point in time)
- Create the
organization
table (with the creator
foreign key referencing users
)
- Add
organization
foreign key to users
referencing organization
# 1 _create_users_table.exs migration
def change do
create table(:users) do
add :username, :string
add :email, :string
add :name, :string
add :password, :string
add :address, :string
timestamps()
end
end
# 2 _create_organization_table.exs migration
def change do
create table(:organization) do
add :org_key, :string
add :name, :string
add :creator, references(:users)
timestamps()
end
create unique_index(:organization, [:org_key])
end
# 3 _add_organization_to_users.exs migration
def change do
alter table("users") do
add :organization, references(:organization)
end
end
I should remember to read all the previous replies next time
2 Likes