Is it possible to make Ecto Values unique?

is it possible to make Ecto Values unique? like when ID
1234 is already in the database, that it doesn’t insert a new data for that (so 1234 is being added twice)

this is how I insert new data

person = %Spizzy.People{id: 1234}
    Spizzy.Repo.insert(person)

when someone new registers, I assign an ID to his acc, however, can I somehow check if the data (specially the same ID) is already in the db and then return nothing? - like dont save a clone of it

1 Like

Hey @spizzy, what database are you using? When you create an ecto schema with one of the phoenix generators the migration to create the actual table should set up an “auto incrementing” value for the id column. This means you should never set it yourself in the application code. Instead, insert a record with no id set, and the one that is returned will have an id set for you by the database.

2 Likes

hey there, im using postgres.
The problem is not the id itself, I have a schema with the table users and now changed it the name to userID.

When I send multiple HTTP request with the same ID, it keeps cloning the same ID over and over - I want to make it unique so if userID 1234 already exists, I dont want to save a “second version”

This is usually done at the database level, not at the schema level. When you have a migration you can do something like create unique_index(:table_name, [:column_name]). You can read more about the unique_index/3 function. Note how the second argument is a list, meaning you can make the index across multiple columns.

1 Like

image
here is an example. I dont want two IDs with 1234

In that case it looks like you want to create a migration that calls create unique_index(:table_name, [:ip]). This will prevent the same value from being in multiple rows.

1 Like

Can you show us the code you’re using?

I already did in my starting post

As @Ankhers pointed out, you can make a migration that will create an unique constraint on the desired column. Let’s say your table is named entities and the column you want to be unique ip.

def change do
    create unique_index(:entities, [:ip])
end

Then in the Ecto changeset you use for insertion, you will have to add that unique constraint so on duplicated insertion attempt an error will be returned.

def changeset(entity, attrs) do
    entities
    |> cast(attrs, [...])
    |> validate_required([...])
    |> unique_constraint(:ip, message: "This ip is already recorded!")
end

Hope this helps.

Edit:

Of course in some controller you can check if an insertion failed due to that unique constraint and then do specific action or just nothing. ^^

Edit 2:
This thread may be interresting to you if from the place you’re trying to insert your record, you need to pattern match the unique constraint error.

I tried this but get

** (Ecto.ConstraintError) constraint error when attempting to insert struct:
     
         * users_ip_index (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.
def changeset(user, params \\ %{}) do
    user
    |> Ecto.Changeset.cast(params, [:ip])
    |> Ecto.Changeset.validate_required([:ip])
    |> Ecto.Changeset.unique_constraint(:ip, message: "Alr. saved this")
  end

Then try to use the name of the constrainst given in the console.

So try to rewrite your changeset like this ?

def changeset(user, params \\ %{}) do
    user
    |> Ecto.Changeset.cast(params, [:ip])
    |> Ecto.Changeset.validate_required([:ip])
    |> Ecto.Changeset.unique_constraint(:ip, name: users_ip_index, message: "Alr. saved this")
end
1 Like

then getting this

undefined function users_ip_index/0 (expected DbTests.Users to define such a function or for it to be imported, but none are available)

My bad. It should be an atom instead (:users_ip_index).

def changeset(user, params \\ %{}) do
    user
    |> Ecto.Changeset.cast(params, [:ip])
    |> Ecto.Changeset.validate_required([:ip])
    |> Ecto.Changeset.unique_constraint(:ip, name: :users_ip_index, message: "Alr. saved this")
end

yea I tried that too before replying but then the old error came back

Ok can you show your migrations for that table ?

Before using Ecto.Changeset unique_constraint/3, you need to ensure a unique index is created in your migration as well. You could even customize the name of the constraint. By default it will be tablename_columnname_index though.

Anyway can you show your migrations for that table?

defmodule Users.Repo.Migrations.Users do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :ip, :string
    end

    create unique_index(:users, [:ip])
  end
end

Hum that’s weird… I mean the migration is correct. I t should work.

At this point what I would do is to check if that migration is actually ran.

Maybe you should drop the databse and run your migration again ?

If that is not possible maybe add a new migration file like this:

mix ecto.gen.migration add_users_unique_ip_index

Then in the new created migration file, you can add :

defmodule Users.Repo.Migrations.AddUsersUniqueIpIndex do
  use Ecto.Migration

  def change do
    create unique_index(:users, [:ip])
  end
end

Then you can run:

mix ecto.migrate

If the unique index is not actually yet created it will be, or you will be set.

Good luck.

There’s some basic stuff here that seems confusing. Your posts say ID but your picture has both id and ip. Your initial code has id: 1234 not ip: 1234. Which value are you actually referring to?

When I asked for code, I meant your whole schema file, your whole migration file, and any broader context around how you were using them.

1 Like