I have Users table with id, user_name, email, password columns, by default, id is primary key, then I set user_name and email are uniques. I don't know if multiple unique columns will guarantee the value in each column not duplicate or the value of unique columns together not duplicate? I mean, which case of followings is right?
asked Oct 26, 2014 at 16:59
1 Answer
There are two options here:
Set uniqueness of each column individually
validates :email, uniqueness: true
validates :name, uniqueness: true
The behaviour is best explained with examples
| id | name | email |
|–––––––––––––––––––––––|
| 1 | Dave | [email protected] |
| 2 | Dave | [email protected] | # invalid, as Dave is a duplicate
| 3 | Mary | [email protected] | # valid, both values are unique
Set uniqueness as a scope
validates :email, uniqueness: { scope: :name }
Examples
| id | name | email |
|–––––––––––––––––––––––|
| 1 | Dave | [email protected] |
| 2 | Dave | [email protected] | # valid, as the combination of Dave & [email protected] is unique
| 3 | Mary | [email protected] | # valid, both values are unique
| 4 | Mary | [email protected] | # invalid, as the combination already exists
1 Comment
taz mazia
The answer is very solid with minor detail I wish you could just to give that 100% guide on how to use the
scope option. You could set the scope option with a single column (as your 2nd example suggest). The other way is to set an array of additional columns if the unique constrain should be more than 2 columns. Example that you could show validates :email, uniqueness: { scope: [:name, :other_col] }. Besides that, the answer is solid
UNIQUE INDEXguarantees that combination of data in all columns included is unique. If you have an index with single column it means that this column will have unique values. If you have an index with multiple columns it means that combination of values in those columns will be unique.