CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordruby-on-rails-4model
user3649729
user3649729

Reputation: 5

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

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 | 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.

Upvotes: 0

Views: 1629

Answers (3)

Mohamad
Mohamad

Reputation: 35359

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] }

Upvotes: 2

Nimir
Nimir

Reputation: 5839

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

Upvotes: 2

Patrick
Patrick

Reputation: 32364

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.

Upvotes: 0

Related Questions