Unique_constraint not working as expected

Questions / Help
1

Hi everyone,

I have been banging my head with the following unique_constraint issue in the changeset:

My database table and index:

Table "public.invoices"
  Column      |          Type          | Modifiers
------------------+------------------------+-----------
 provider_id      | integer                | not null
 invoice_number   | character varying(255) | not null
 payment_order_id | integer                | not null
Indexes:
"invoices_provider_id_invoice_number_index" UNIQUE, btree (provider_id, invoice_number)

My changeset:

def changeset(%PaymentOrder{} = payment_order, attrs, document) do
    payment_order
    |> cast(attrs, [:date, :invoice_number, :exchange_rate, :amount, :check_number, :bank_account_id])
    |> validate_required([:date, :invoice_number, :exchange_rate, :amount, :check_number, :bank_account_id])
    |> validate_number(:exchange_rate, greater_than: 0)
    |> validate_number(:amount, greater_than: 0)
    |> validate_number(:check_number, greater_than: 0)
    |> validate_amount(document)
    |> today_or_future_date()
    |> validate_local_invoice_format()
    |> add_num_year_to_changeset("payment_num_sequence")
    _|> unique_constraint(:invoice_number, name: :invoices_provider_id_invoice_number_index)_
    |> unique_constraint(:num, name: :payment_num_sequence_year_index)
    |> unique_constraint(:check_number, name: :payment_orders_bank_account_id_check_number_index)
end

The problem:

invoice_number unique_constraint validations fails to add error to changeset and the database blows up with a constraint error.

It is very interesting that the other two unique validations work fine.

Database error:

Ecto.ConstraintError at POST /contracts/1/payment_orders

constraint error when attempting to insert struct:

* unique: invoices_provider_id_invoice_number_index

If you would like to convert this constraint into an error, please
call unique_constraint/3 in your changeset and define the proper
constraint name. The changeset has not defined any constraint.

Thanks for your help.

Best regards.

1 Like
2

You need a unique constraint specifically for this constraint, you don’t have one (only for num and check_number and maybe invoice_number so far). Basically exactly what the error is telling you, however the syntax is different, and I don’t recall what it is off hand… Ping me Monday if no one answers by then and I can look it up, on phone being blepped by a cat currently. :sweat_smile:

It’s the name that is different as I recall…

1 Like
3

@OvermindDL1 thanks for the help.

I will contact you tomorrow if there is no answer. Thanks.

I have a multi-column unique index in the database that includes provider_id and invoice_number. As I understand, to make a multi-column unique_constraint work you have to put one of the fields that will be evaluated as a parameter and the name of the index that will be called. This field will receive the error message if the evaluation fails.

Best regards.

1 Like
4

I’m at a computer now. ^.^

So yeah, what you had will work from some testing here. Can you show a minimal reproducible example with, say, a sqlite database or so in a test repo?

5

Sure, how do we connect? Google Hangouts?

6

Eh I’m mostly in terminal here so anything GUI would not be very easy for me. ^.^

Just make an example minimal test-case in a new project and upload to github. Make sure it is pretty trivial to run like just git clone ... && mix deps.get && mix test to show the issue.

7

I created a small repo with the boilerplate project in BitBucket.

I sent you an invitation and the repo is public.

Let me know if you where able to access.

Thanks

8

Uh, hmm, I’m not able to compile the dependencies…

== Compilation error in file lib/ecto/adapters/postgres/connection.ex ==
** (ArgumentError) argument error
    (stdlib) erl_anno.erl:322: :erl_anno.set(:generated, false, {"lib/ecto/adapters/postgres/datetime.ex", 48})
could not compile dependency :ecto, "mix compile" failed. You can recompile this dependency with "mix deps.compile ecto", update it with "mix deps.update ecto" or clean it with "mix deps.clean ecto"

Might need someone else to test, my installed elixir might have issues… ^.^;
(I’ve been experimenting a lot lately…)

9

I just udpated the repo with the needed associations but not sure about the error.

Please check that the config/dev.exs has the default postgresql username and password. That might be the issue.

Thanks

10

@OvermindDL1is this the same/related error to the one referenced by myself and @wfgilman here https://github.com/elixir-ecto/ecto/issues/2081 ? I thought the index name WAS the constraint name, and I guess for some simple cases it is, but sounds like you’re saying it’s different if multiple columns involved or something? (mine was specifically trying to allow test exceptions for a unique compound index, so adding a :test boolean to the table and WHERE test = false clause to the unique index, for reference)

11

The index name should be the constraint name in the database yeah, which is why his code initially looks like it should work to me. I don’t think it’s related to that issue, but did not look too close.

12

Ok guys,

I finally found the error. Turns out that my changeset didn’t include one of the fields that compose the multi-column index and therefor it didn’t match the index.

I added the unique_constraint in the payment_order changeset but the provider_id is not part of the payment_order’s schema, just the invoice_number.

To clarity, if someone gets into a similar issue, I will explain my scenario:

Associations:
Provider (has many) --> Contracts (many to many) <–> PaymentContracts <–> Payment Order
Provider (has many) --> Invoices

Solution

To solve this situation I added a virtual field in the payment_order schema as follows:

field :provider_id, :integer, virtual: true

When the form is submitted, I add the provider_id to the attributes map. Then created the changeset with the unique_constraint working properly.

Hope this helps

@OvermindDL1 Thanks for your help.

Best regards.

2 Likes