`unique_constraint/3` for composite primary keys?

list table has a composite primary key of listing_profile and listed_profile.

I added unique_constraint to changeset. But it doesn’t check uniqueness of the primary key.

# Should error
iex(8)> List.Entity.changeset(%List.Entity{listing_profile: 1, listed_profile: 2}, %{})
#Ecto.Changeset<action: nil, changes: %{}, errors: [],
 data: #Feder.Social.List.Entity<>, valid?: true>

On Postgres side, list_pkey unique constraint works as expected.

# Existing key
iex> Repo.insert(%List.Entity{listing_profile: 1, listed_profile: 2})

** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * list_pkey (unique_constraint)

...

What am I missing here?

# schema
@primary_key false

schema "list" do
  field :listing_profile, :id, primary_key: true
  field :listed_profile, :id, primary_key: true
end

def changeset(entity, attrs) do
  entity
  |> cast(attrs, [:listing_profile, :listed_profile])
  |> validate_required([:listing_profile, :listed_profile])
  |> unique_constraint([:listing_profile, :listed_profile], name: :list_pkey)
end
# migration
create table(:list, primary_key: false) do
  add :listing_profile,
      references(:profile, on_delete: :delete_all),
      primary_key: true

  add :listed_profile,
      references(:profile, on_delete: :delete_all),
      primary_key: true
end
                    Table "public.list"
     Column      |  Type  | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
 listing_profile | bigint |           | not null |
 listed_profile  | bigint |           | not null |
Indexes:
    "list_pkey" PRIMARY KEY, btree (listing_profile, listed_profile)
Foreign-key constraints:
    "list_listed_profile_fkey" FOREIGN KEY (listed_profile) REFERENCES profile(id) ON DELETE CASCADE
    "list_listing_profile_fkey" FOREIGN KEY (listing_profile) REFERENCES profile(id) ON DELETE CASCADE

Separating each field doesn’t help.

|> unique_constraint([:listing_profile, :listed_profile], name: :list_pkey)
# or
|> unique_constraint(:listing_profile, name: :list_pkey)
|> unique_constraint(:listed_profile, name: :list_pkey)

@taro, the *_constraint functions of Ecto.Changeset turn those violations into errors on the changeset, but requires you to attempt to write to the database in order to do so.

Separately, there’s Ecto.Changeset — Ecto v3.9.4 if you want Ecto to check ahead of time, but as the name implies it alone is not safe of race conditions

So, if I try to insert, it doesn’t use changeset.

# Existing key
Feder.Repo.insert(%List.Entity{listing_profile: 1, listed_profile: 2})

** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * list_pkey (unique_constraint)

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `unique_constraint/3` on your changeset with the constraint
`:name` as an option.

The changeset has not defined any constraint.

    (ecto 3.9.2) lib/ecto/repo/schema.ex:795: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir 1.14.3) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
    (ecto 3.9.2) lib/ecto/repo/schema.ex:780: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto 3.9.2) lib/ecto/repo/schema.ex:761: Ecto.Repo.Schema.apply/4
    (ecto 3.9.2) lib/ecto/repo/schema.ex:369: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    iex:3: (file)

Try this:

%List.Entity{}
|> List.Entity.changeset(%{listing_profile: 1, listed_profile: 2})
|> Feder.Repo.insert()
1 Like

I see, I need to include changeset.

I thought Ecto would magically finds changeset for me :joy:

Thanks!

Ecto is light on that kind of magic – cast_assoc/3 & cast_embed/3 are the exceptions that come to mind.

1 Like