0

So I am trying to add an index on my votes table to prevent duplicate rows from being added.

In my previous stackoverflow question. I asked "How do I ensure that duplicate rows are not added to my database table via activerecords?" How do I ensure that duplicate rows are not added to my database table via activerecords?

The answer was adding an index through a database migration and this solved it.

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

However, now I 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, 519, 2) is duplicated.

How do I automatically delete the duplicate rows/row when the migration is run.

thank you!

Community
  • 1
  • 1
user3649729
  • 5
  • 4

2 Answers2

1

You can delete duplicate records first with the following sql statement, assuming you have a primary key consisting of one column. Also, be aware that it deletes all duplicate records that do not have the minimum value for their id column.

    DELETE FROM votes v WHERE v.id NOT IN 
    (SELECT MIN(id) FROM votes GROUP BY voter_id, votefor_id, vote)
dominik
  • 127
  • 2
  • 7
0

run another migration before which will destroy all "duplicates" rows?

you also want to add validates_uniqueness_of to the model

http://apidock.com/rails/ActiveRecord/Validations/ClassMethods/validates_uniqueness_of

Jakub Kuchar
  • 1,665
  • 2
  • 23
  • 39