Ecto unique_constraint not working as expected

Question

I want to create a unique_constraint with 3 fields and call it "unique_user_product_shop". When I do this my database throws an error saying the attempted insert was not unique:

error:

18:04:18.830 [error] #PID<0.380.0> running Api.Router terminated
Server: 192.168.1.12:4000 (http)
Request: PUT /product/isinshop/?p_id=12&s_id=12&u_id=792200324272726
** (exit) an exception was raised:
    ** (FunctionClauseError) no function clause matching in Ecto.Changeset.add_constraint/6
        (ecto) lib/ecto/changeset.ex:2272: Ecto.Changeset.add_constraint(#Ecto.Changeset<action: nil, changes: %{p_id: 12, s_id: 12, u_id: "792200324272726", voted_in_shop: true, voted_not_in_shop: false}, errors: [], data: #Api.UserProductShop<>, valid?: true>, :unique, "unique_user_product_shop", :exact, [:p_id, :u_id, :s_id], {"has already been taken", []})
        (api) lib/api/models/user_product_shop.ex:37: Api.UserProductShop.insert_user_product_shop/2
        (api) lib/api/controllers/product/put_product_is_in_shop.ex:48: Api.Controllers.PutProductIsInShop.put_product_is_in_shop/1
        (api) lib/api/router.ex:1: Api.Router.plug_builder_call/2
        (api) lib/plug/debugger.ex:123: Api.Router.call/2
        (plug) lib/plug/adapters/cowboy/handler.ex:15: Plug.Adapters.Cowboy.Handler.upgrade/4
        (cowboy) /Users/Ben/Development/Projects/vepo/api/deps/cowboy/src/cowboy_protocol.erl:442: :cowboy_protocol.execute/4

my code:

  def changeset(user_product_shop, params \\ %{}) do
    user_product_shop
    |> cast(params, [
        :u_id, 
        :p_id,
        :s_id,
        :voted_not_in_shop,
        :voted_in_shop])
    |> validate_required([:u_id, :p_id, :s_id])
    |> unique_constraint([:p_id, :u_id, :s_id], name: :unique_user_product_shop)
  end

So why is it not picking up my unique_constraint which causes my db to throw a red error in the console?

Answer 1

Ecto.Changeset.unique_constraint/3 must be backed up by the real unique constraint in the database. Here is the excerpt from the documentation:

In order to use the uniqueness constraint, the first step is to define the unique index in a migration:

create unique_index(:users, [:email])

Ecto.Changeset.unique_constraint/3 is built to pattern match the existing unique constraints only, that’s why if there is no such constraint in the database, FunctionClauseError is raised.