Modify foreign key in Ecto

Question

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.

You have working answer in you question, it'be useful to post it as a separate answer so anyone can vote. — denis.peplin, Dec 15 '16 at 14:04

score 43 · Answer 1 · edited Feb 03 '19 at 13:45

43

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

edited Feb 03 '19 at 13:45

answered Aug 19 '17 at 10:36

Max Clarke

1,148
2
11
19

2

Doesn't support it with mysql as of now - `(ArgumentError) MySQL adapter does not support constraints` – Jeremy Belolo Feb 12 '19 at 16:22

score 23 · Accepted Answer · answered Feb 02 '16 at 08:43

23

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

answered Feb 02 '16 at 08:43

Gazler

83,029
18
279
245

I ended up doing a variation of this. – Thomas Dippel Feb 03 '16 at 19:39
1

The `drop_if_exists index(:videos, [:user_id])` line doesn't make sense since that removes a normal index, not a foreign key constraint. The answer by Jessie is better IMO: https://stackoverflow.com/a/43572806/175830 – Jason Axelson Aug 02 '17 at 08:40
This doesn't seem to work anymore. The solution below by Max Clarke works. – xji Jun 12 '18 at 14:27

score 8 · Answer 3 · answered Apr 23 '17 at 15:08

8

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.

answered Apr 23 '17 at 15:08

Jesse Shieh

4,660
5
34
49

1

This should be higher – Peter Brown Aug 10 '17 at 16:49
2

Shouldn't `flush()` be used ? `drop constraint(: videos, "videos_user_id_fkey")` should work. – David Magalhães Jan 05 '18 at 23:42

score 7 · Answer 4 · answered May 04 '20 at 14:18

In Ecto SQL 3.4.3:

"If the :from value is a %Reference{}, the adapter will try to drop the corresponding foreign key constraints before modifying the type."

modify :user_id, references(:users, on_delete: :delete_all), from: references(:users)

Should work. In working on a rollback, I worked out that this worked to clear the FK and remove the column:

remove :user_id, references(:users)

score 3 · Answer 5 · edited May 23 '17 at 12:25

3

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

edited May 23 '17 at 12:25

Community

1
1

answered Feb 02 '16 at 00:30

tkowal

9,129
1
27
51

1

I was hoping that there was a better way of doing it. In this particular example i could just drop the column and re-add it, but if this was a real-world situation that column would probably already contain valuable information that I would be very sorry to loose :) – Thomas Dippel Feb 02 '16 at 07:56

score 2 · Answer 6 · answered Apr 03 '21 at 19:59

from option was introduced in Ecto 3.0, so you can setup your migration in more elegant way:

defmodule App.Migrations.ChangeVideosUserConstraint do
  use Ecto.Migration
  def change do
    alter table(:videos) do
      modify(:user_id, references(:users, on_delete: :delete_all), from: references(:users))
    end
  end
end

Modify foreign key in Ecto

6 Answers6

Linked