Hello, I need to index 3 field in my database and those should be uniq, then I created my migration like this:
def change do
create table(:user, primary_key: false) do
add :id, :uuid, primary_key: true
...
add :username, :string, size: 20, null: false, unique: true
add :email, :string, null: false, unique: true
...
add :verified_email, :string, size: 120, null: true, unique: true
timestamps()
end
create(
index(:user, [:email, :verified_email, :username],
concurrently: true,
name: :index_of_users,
unique: true
)
)
end
and my changeset:
def changeset(struct, params \\ %{}) do
struct
....
|> unique_constraint(:username, name: :index_of_users, message: "userNAME")
|> unique_constraint(:email, name: :index_of_users, message: "Email")
end
but my changeset
can’t identify uniq fields and lets user to add whatever they want, for example, I can add 3 records with Identical emails.
You made an index which says the combination of those 3 columns must be unique, not each individual column must be unique.
6 Likes
then I should create 3 index? like this?
create(
index(:user, [:email],
concurrently: true,
name: :index_of_users_email,
unique: true
)
)
create(
index(:user, [:verified_email],
concurrently: true,
name: :index_of_users_verified_email,
unique: true
)
)
create(
index(:user, [:username],
concurrently: true,
name: :index_of_users_username,
unique: true
)
)
1 Like
hauleth
February 15, 2019, 1:00pm
6
You do not need to create index concurrently there, it is different from what you probably think it does.
2 Likes
hello, I need to index my fields because I search these field many times and need to load fast, { id, email, username}, is my way false? would you mind explaining me? and is there a way to identify uniq field without creating index?
1 Like
PragTob
February 15, 2019, 2:16pm
8
I’m not sure what @hauleth thinks you’re thinkingbut adding indexes concurrently is usually an ok option, albeit not needed when your tables have few rows (as it’s so fast then that locking doesn’t really hurt).
If you’re using Postgres the docs are here - there’s a whole section with caveats though .
3 Likes
hauleth
February 15, 2019, 2:46pm
9
I meant that you do not need concurrently: true
, not that you do not need indices at all.
2 Likes