Can you modify a column's references/3 options in Ecto?

Questions / Help
1

I created a table with a FK that has on_delete: :delete_all. I now want to modify this cascading logic to do nothing if the FK record is deleted. However, I get a duplicate_object error when trying to modify the column: ** (Postgrex.Error) ERROR 42710 (duplicate_object): constraint "address_person_id_fkey" for relation "address" already exists.

Is this possible without dropping and re-creating the column?

My original migration:

def change do
  create table(:address) do
    add :person_id, references(:person, on_delete: :delete_all), null: false
  end
end

My new migration:

def change do
  alter table(:address) do
    modify :person_id, references(:person, on_delete: :nothing), null: false
  end
end
6 Likes
2

bump :slight_smile:

3

Your code really looks like it ‘should’ work, if it does not then sounds like an ecto bug to me. Your alter should be running something kind of like:

ALTER TABLE address
DROP CONSTRAINT address_person_id_fkey
ADD CONSTRAINT address_person_id_fkey
    FOREIGN KEY (person_id)
    REFERENCES person(id)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION

If ecto is not, it sounds like a bug…

4

How do you check the SQL commands run by ecto migration?

5

It prints it out to the console when you run it. :slight_smile:

6

you can use the command mix ecto.migrate --log-sql this is in the master branch, you can edit your mix file to point to it by doing something like this:

{:ecto,
       github: "elixir-ecto/ecto",
       sha: "9962c146c0569a91eda189e328a7d02382d9bcc7",
       override: true},

I’m actually getting the same error.
I looked in the ecto tests, and there is no test for the SQL that OvermindDL1 described in his comment.
There is no issue in github for this bug

I am actually attempting to write a test for this bug right now, but i may not be able to solve the issue.

7 
def up do
  drop constraint "address_person_id_fkey"
  alter table(:address) do
    modify :person_id, references(:person, on_delete: :nothing), null: false
  end
end

you will need a down migration too, and it will need a drop constraint as well

15 Likes
8

I just ran into this issue and found this thread. To build on boxxxie’s reply, here’s the full migration that worked for me (constraint needed a table specified)

def up do
  drop(constraint(:address, "address_person_id_fkey"))

  alter table(:address) do
    modify(:person_id, references(:person, on_delete: :nothing), null: false
  end
end

def down do
  drop(constraint(:address, "address_person_id_fkey"))

  alter table(:address) do
    modify(:person_id, references(:person, on_delete: :delete_all), null: false
  end
end
14 Likes
9

Thank you for expanding on boxxxie’s reply!. :slight_smile: Just to mention that you’re missing a closing bracket for the modify call.

2 Likes
10

if you use the :from option, you can use the change without explicit declaring up and down:

def change do
  alter table(:address) do
    modify :person_id, references(:person, on_delete: :nothing),
      from: references(:person, on_delete: :delete_all)
  end
end

docs: Ecto.Migration — Ecto SQL v3.11.1

This command is not reversible unless the :from option is provided. If the :from value is a %Reference{} , the adapter will try to drop the corresponding foreign key constraints before modifying the type.

27 Likes
11

What if former column was nullable?
Can we just do something like this?

def change do
  alter table(:posts) do
    modify :category_id, references(:categories, on_delete: :restrict),
      null: false,
      from: references(:categories, on_delete: :nilify_all)
  end
end

or

def change do
  alter table(:posts) do
    modify :category_id, references(:categories, on_delete: :restrict),
      null: false,
      from: [references(:categories, on_delete: :nilify_all), null: true]
  end
end
1 Like