rails rspec duplicate key value violates unique constraint after adding unique index

Question

I am working on a rails app, and I added a table. On that table, I wanted an index so to add one, I made a migration for adding a unique index. My migration looks like

class AddIndexToTable < ActiveRecord::Migration
  def change
    add_index :table, :user_id, unique: true
  end
end

everything seemed fine, but now when I run my specs against my migration changes I now get the error

ActiveRecord::RecordNotUnique:
        PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_table_name_on_user_id"
        DETAIL:  Key (user_id)=(15) already exists.
        : INSERT INTO "table_name" ("user_id", "day", "last_visited_on", "daily_rewards", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

I have tried dropping the DB's, re-migrating, re-setting the pk_sequence with ActiveRecord::Base.connection.reset_pk_sequence!(table_name) and other fixes I could find, but I am still stuck on the same error.

Could anyone help me understand why I keep getting this error, and how it can be fixed? I can post more code if needed

This needs more background info. How are you seeding the database? Seeds file or Factorybot or what? Is this happening on a specific test? What is the setup? I would look for where you are creating the duplicate data. — Joel Cahalan
@JoelCahalan I am using factory bot for the data. in terms of the database, I have dropped and re-migrated, so it should be a fresh db with close to nothing in it. the seeds file has not been ran. — James N

smathy smathy · Accepted Answer · 2019-09-13T01:06:37

You've got either a factory or a test that's trying to create multiple table records for the same user. Either that's going to be obvious looking in the test that's causing this, or perhaps it's an integration test and you don't have your test environment set up properly to clean out the DB between test cases.

rails rspec duplicate key value violates unique constraint after adding unique index

2 Answers