修改Ecto中的外键 [英] Modify foreign key in Ecto

问题描述

我有这个原始迁移已经在上游运行并发送了:

I have this original migration that have already been run and sent upstream:

create table(:videos) do
  add :url, :string
  add :title, :string
  add :description, :text
  add :user_id, references(:users, on_delete: :nothing)

  timestamps
end
create index(:videos, [:user_id])

现在，我希望将user_id上的外键更改为级联删除，以便在删除用户时，所有与其关联的视频也将被删除.

Now i wish to change the foreign key on user_id to cascade deletions, so that when a user is deleted all of his associated videos will also be deleted.

我尝试了以下迁移:

alter table(:videos) do
  modify :user_id, references(:users, on_delete: :delete_all)
end

但这会引发错误:

(Postgrex.Error) ERROR (duplicate_object): constraint "videos_user_id_fkey" for relation "videos" already exists

如何制定一个迁移脚本，该脚本将根据我的要求更改此外键?

How can I formulate a migration script that will change this foreign key according to my requirement?

更新

我最终得到了以下解决方案:

I ended up with the following solution:

def up do
  execute "ALTER TABLE videos DROP CONSTRAINT videos_user_id_fkey"
  alter table(:videos) do
    modify :user_id, references(:users, on_delete: :delete_all)
  end
end

def down do
  execute "ALTER TABLE videos DROP CONSTRAINT videos_user_id_fkey"
  alter table(:videos) do
    modify :user_id, references(:users, on_delete: :nothing)
  end
end

这会在ecto尝试重新创建约束之前就删除约束.

this drops the constraint before ecto tries to recreate it.

推荐答案

您可以在调用alter之前删除索引:

You can drop the index before calling alter:

drop_if_exists index(:videos, [:user_id])
alter table(:videos) do
  modify :user_id, references(:users, on_delete: :delete_all)
end

反之则有些棘手:

execute "ALTER TABLE videos DROP CONSTRAINT videos_user_id_fkey"
create_if_not_exists index(:videos, [:user_id])

这篇关于修改Ecto中的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！