Creating a unique constraint on two columns together in Ecto

Phoenix Forum Questions / Help
, ,
1

Hello Fellas,

I am trying to enforce uniquness in a table using to combined fields I originally followed this instructions Stackoverflow thread which seems to work for everybody apart from my self and I do not seem to get why.

So my migration code looks like this :

def change do
    create table(:inventories) do
      add :operation_account_id, references(:operation_accounts, on_delete: :nothing)
      add :currency_id, references(:currencies, on_delete: :nothing)

      timestamps()
    end

    create index(:inventories, [:operation_account_id])
    create index(:inventories, [:currency_id])
    create unique_index(:inventories, [:currency_id, :operation_account_id], name: :operation_currency_index)
  end

and this is my changeset


  @doc false
  def changeset(inventory, attrs) do
    inventory
    |> cast(attrs, [:balance, :currency_id, :operation_account_id])
    |> validate_required([:balance, :currency_id, :operation_account_id])
    |> unique_constraint(:currency_per_account, name: :operation_currency_index)
  end

The problem is that during testing when I try to insert two Inventory entries with same currency_id and operation_account_id it inserts them properly withour and exception or an error and I do not understand what I am missing.

Thanks a lot

2

You should use a composite table in these cases, primary keys are indexed.

    create table(:inventories, primary_key: false) do
      add :operation_account_id, references(:operation_accounts, on_delete: :nothing, primary_key: true)
      add :currency_id, references(:currencies, on_delete: :nothing, primary_key: true)

      timestamps()
    end


  @doc false
  def changeset(inventory, attrs) do
    inventory
    |> cast(attrs, [:balance, :currency_id, :operation_account_id])
    |> validate_required([:balance, :currency_id, :operation_account_id])
    |> assoc_constraint(:currency)
    |> assoc_constraint(:operation_account)
  end
3

Did you do this just to make sure?

MIX_ENV=test mix do ecto.drop, ecto.create, ecto.migrate
1 Like
4

Thank you somehow I completly missed to reset the database in the testing env.

5

Thank you for your response, but somehow I fail to understand how what you propose is any different with the code that I posted to describe my issue. Are you proposing that the assoc_constraint might be neccessery for what I am trying to accomplish ?