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])

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.

I have tried the following migration:

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

But this raises an error:

(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?

---

UPDATE

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

this drops the constraint before ecto tries to recreate it.

回答1:

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

Doing the opposite is a little trickier:

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

回答2:

I'm not sure when this was added to Ecto, but at least in 2.1.6 there's no need for raw SQL anymore. drop/1 now supports constraints (drop_if_exists/1 doesn't though):

def up do
  drop constraint(:videos, "videos_user_id_fkey")
  alter table(:videos) do
    modify :user_id, references(:users, on_delete: :delete_all)
  end
end

def down do
  drop constraint(:videos, "videos_user_id_fkey")
  alter table(:videos) do
    modify :user_id, references(:users, on_delete: :nothing)
  end
end

回答3:

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

this drops the constraint before ecto tries to recreate it

Copied from the question.

回答4:

I don't think that it can achieved with alter table. For example according to this answer Postgres doesn't allow modifying constraints in ALTER TABLE statement. MySQL also doesn't allow modifying constraints.

The easiest thing to do would be removing the field and adding it back if you don't have any data. Otherwise, you need use raw SQL with execute