How do I ensure that duplicate rows are not added to my database table via activerecords?

Question

So I have a table called votes.

There are three columns. VoterID, VoteforID and Vote.

The uniqueness of each individual column is not important however, the same row cant repeat twice

For instance. This is correct

Voter_id | Votefor_id | Vote

1 | 2 | 1

1 | 3 | 1

1 | 4 | 1

2 | 1 | 2

This is wrong.

Voter_id | Votefor_id | Vote

1 | 2 | 1

1 | 4 | 1

2 | 1 | 2

Currently, I am doing a select statement before adding records just to make sure that an identical row does not exist. Surely, there is a better solution.

Thank you.

score 2 · Answer 1 · edited May 23 '17 at 10:29

Use the scope parameter on uniqueness_validation, something like:

validates :voter_id, uniqueness: { scope: :votefor_id }

scope parameters could be more than one:

validates :voter_id, uniqueness: { scope: [:votefor_id, :vote] }

Update: (App validation Vs Db constraint)

It really depends on the details but for the case in hand my personal opinion that db constraint is a bit overkill here because chances for a race condition (documented in rails uniqueness validation) to happen are low, since we need

two records with the exact same voter_id, votefor_id and vote to be submitted at the exact same time (we are talking about milliseconds), if i may assume something about the OP business logic that voter would normally be a logged in user meaning duplicate entries should only come from him !!

conclusion:

I had quite a number of rails apps running in production using uniqueness_validation just fine and only one time it failed me in a situation of a uniqueness check on a single column with many simultaneous entries (obviously i needed db constraint there).

If data integrity is critical you should for sure go with db constraint.

Or if you don't mind the overhead & specially if you could benefit from the new index on the (voter_id, votefor_id and vote) to increase search speed i would say go with db constraint.

otherwise i would say: keep it simple and stay with uniquess_valdation

Thanks Nimir, so what is the difference between: validates :voter_id, uniqueness: { scope: [:votefor_id, :vote] } and validates :votefor_id, uniqueness: { scope: [:voter_id, :vote] }.thanks again! — user3649729, May 18 '14 at 13:28
This answer is not correct. It does not guarantee uniqueness. You need a database constraint. — Mohamad, May 18 '14 at 13:38
regarding your inquiry @user3649729 , the params in the `scope` defines what columns should be compared along with `voter_id` for uniqueness — Nimir, May 18 '14 at 14:46

Mohamad · Accepted Answer · 2014-05-18T17:35:19.887

2

If data integrity is critical, you should not use a validation to guarantee uniqueness. It can fail. The only way to guarantee uniqueness is to use a database constraint. This is because the Rails validates_uniqueness can have race conditions.

Create a migration to add the index, or change your existing one to reflect this change:

For a new table:

class CreateVotes < ActiveRecord::Migration
  def change
    create_table :votes do |t|
      t.belongs_to :voter
      t.belongs_to :votefor
      t.string :vote # Choose the correct column type
      t.timestamps
    end
    add_index :votes, [:voter_id, :votefor_id, :vote], unique: true
  end
end

For an existing table:

class AddUniqueIndexToVotes < ActiveRecord::Migration
  def change
    add_index :votes,  [voter_id, votefor_id, vote], unique: true
  end
end

Now you can go ahead and add a validation, as suggested by others, if you want to give your user feedback that they've already voted:

validates :voter_id, uniqueness: { scope: [:votefor_id, :vote] }

edited May 18 '14 at 17:35

answered May 18 '14 at 13:42

Mohamad

34,731
32
140
219

btw the validation here won't work since DB constraint will raise `ActiveRecord::RecordNotUnique` when uniqueness fails on db side which need to be handled (rescued) – Nimir May 18 '14 at 14:51
@Nimir Yes it will. Rails will run the query to verify the uniqueness. It will raise a validation error if it finds matches. The database constraint is just to guarantee that no duplicates will be saved in the event of a race condition that can happen with Rails and it's `uniqueness` validation. – Mohamad May 18 '14 at 14:55
@Mohamad (https://github.com/rails/rails/blob/master/activerecord/lib/active_record/validations/uniqueness.rb#L165) – Nimir May 18 '14 at 15:07
@Mohamed, you missed out the ":" before [voter_id, votefor_id, vote]. It should be [:voter_id, :votefor_id, :vote]. Thanks a bunch! – user3649729 May 18 '14 at 15:38
@Mohamad I now get the error "PG::Error: ERROR: could not create unique index '"index_votes_on_voter_id_and_votefor_id_and_vote" DETAIL: Key (voter_id, votefor_id, vote)=(581, 51, 2) is duplicated.".' Is there any way I could delete the records automatically which are duplicate. Thank you! – user3649729 May 18 '14 at 16:10
@Nimir I don't understand what you mean by the link. It says exactly what I said: **IF** there is a race condition, the database will error out. But most of the time, Rails will pick the the validation as normal. That's precisely what I said: The validation will work nearly all of the time. – Mohamad May 18 '14 at 17:37
@user3649729 can't you just delete them? If this is not production, do `rake db:reset` – Mohamad May 18 '14 at 17:39

Patrick · Answer 3 · 2014-05-18T13:37:30.177

0

In your table definition, add a UNIQUE table constraint:

CREATE TABLE votes (
  voter_id   int NOT NULL,
  votefor_id int NOT NULL,
  vote       int NOT NULL,
  UNIQUE (voter_id, votefor_id, vote)
);

This way PostgreSQL checks for duplicates. If you try to INSERT a record whose values are the same for all three columns as in an existing record, then an error will be thrown.

edited May 18 '14 at 13:37

answered May 18 '14 at 13:31

Patrick

29,357
6
62
90

make that `UNIQUE (voter_id, votefor_id)` Otherwise a person can vote twice (with a different vote value) Also: {voter_id,votefor_id} should be NOT NULLable. – wildplasser May 18 '14 at 13:33
@wildplasser: Maybe it is valid to cast multiple votes? The OP implies that. Added the `NOT NULL` qualifiers. – Patrick May 18 '14 at 13:37
Indeed the OP said that. But to me thi makes no sense: multiple votes are allowed *only* if the have a different value? – wildplasser May 18 '14 at 13:45

How do I ensure that duplicate rows are not added to my database table via activerecords?

3 Answers3

Update: (App validation Vs Db constraint)

conclusion:

Linked