Ecto.Migration error on create constraints

Phoenix Forum Questions / Help
, ,
1

I’m trying to modify a table and add some constraints, as:

defmodule Bank.Repo.Migrations.AlterCpfCnpjConstraint do
  use Ecto.Migration

  def change do
    alter table(:users) do
      drop constraint(:users, :cpf_or_cnpj)
    end

    create constraint(:users, :cpf_or_cnpj_exists,
             check: ~s|(cpf is not null) or (cnpj is not null)|
           )

    create(
      constraint(:users, :cpf_or_cnpj,
        check: ~s|(cpf is null) and (cnpj is not null) or (cpf is not null) and (cnpj is null)|
      )
    )
  end
end

However I receive this error:

** (Ecto.MigrationError) cannot execute nested commands
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:140: Ecto.Migration.Runner.execute/1
    (ecto_sql 3.5.3) lib/ecto/migration.ex:584: Ecto.Migration.drop/1
    priv/repo/migrations/20210106210201_alter_cpf_cnpj_constraint.exs:6: Bank.Repo.Migrations.AlterCpfCnpjConstraint.change/0
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:279: Ecto.Migration.Runner.perform_operation/3
    (stdlib 3.13.2) timer.erl:166: :timer.tc/1
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:349: Ecto.Migrator.attempt/8
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:250: anonymous fn/5 in Ecto.Migrator.do_up/5
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:331: anonymous fn/3 in Ecto.Migrator.run_maybe_in_transaction/6
    (ecto_sql 3.5.3) lib/ecto/adapters/sql.ex:1027: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.3.1) lib/db_connection.ex:1444: DBConnection.run_transaction/4
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:330: Ecto.Migrator.run_maybe_in_transaction/6
    (elixir 1.11.0) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir 1.11.0) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib 3.13.2) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

What I’m doing wrong?

2

So, to drop a constraint you don’t to use the alter, you only need to specify the table on the constraint function as:

defmodule Bank.Repo.Migrations.AlterCpfCnpjConstraint do
  use Ecto.Migration

  def change do
    drop constraint(:users, :cpf_or_cnpj)

    create constraint(:users, :cpf_or_cnpj_exists,
             check: ~s|(cpf is not null) or (cnpj is not null)|
           )

    create(
      constraint(:users, :cpf_or_cnpj,
        check: ~s|(cpf is null) and (cnpj is not null) or (cpf is not null) and (cnpj is null)|
      )
    )
  end
end

Also, I was trying to check if at least of one of cpf and cnpj was filled, but also don’t allow to fill both! The best I could think was using a XOR logic:

defmodule Bank.Repo.Migrations.AlterCpfCnpjConstraint do
  use Ecto.Migration

  def change do
    create(
      constraint(:users, :cpf_or_cnpj,
        check: ~s|(cpf is null) and (cpf is not null) or (cpf is not null) and (cnpj is null)|
      )
    )
  end
end