what does this mean?
create unique_index(:users, [:email, :username])
and compare to this:
create unique_index(:users, [:username, :username]
and just this:
create unique_index(:users, [:username]
thanks in advance.
This creates a unique index for email–username pairs. This means that you can have the same email many times as long as the username is different, and vice versa. So:
| username | |
|---|---|
| a@example.com | A |
| a@example.com | A2 |
| b@example.com | B |
| b2@example.com | B |
This would be fine because each combination of email and username here is unique, even if separately they are not unique.
This makes no sense. It would create a unique index for username–username pairs, but it doesn’t make sense to do that.
This creates a unique index for the username column only. So you can have usernames A, A2, B… but you cannot have two or more rows with username A. This is usually the one you want.
2 Likes
One important thing to know about multicolumn indexes (we just encountered this at work) - the order is important, at least on Postgres. The engine can only use the index if the columns starting from the left are fixed, so an index on [:email, :username] will speed up queries searching by email and also email + username but not a query on just :username. Further discussion in the documentation
3 Likes
