Migration ERROR when adding unique constraint to model! "standard error"?

Question

In my rails app, I want to add a unique constraint to my favourites model. (I'm remaking a basic twitter app, with users and tweets, and generated a third model for favourite). However when I try and add a unique constraint to my favourites model so that one user can favourite a tweet only once, then run the command

rake db:migrate, I get the following error:

rake aborted!

StandardError: An error has occurred, this and all later migrations canceled:

SQLite3::ConstraintException: UNIQUE constraint failed: favourites.user_id, favourites.tweet_id: CREATE UNIQUE INDEX "index_favourites_on_user_id_and_tweet_id" ON "favourites" ("user_id", "tweet_id")/Users/Tabish/.rvm/gems/ruby-2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/statement.rb:108:in `step'

Here is how my migration file that I have created looks:

class AddUniqueConstraintToTweets < ActiveRecord::Migration
  def change
    add_index :favourites, [:user_id, :tweet_id], :unique => true
  end
end

Also here is my favourites table migration file:

class CreateFavourites < ActiveRecord::Migration
  def change
    create_table :favourites do |t|
      t.references :user, index: true
      t.references :tweet, index: true

      t.timestamps null: false
    end
    add_foreign_key :favourites, :users
    add_foreign_key :favourites, :tweets
  end
end

I am using Rails 4.2.0 and SQLite3

thanks!! i just dropped by db, and re-created by db, then ran migrate and everything worked!:) — TABISH KHAN, Feb 03 '15 at 01:19

score 1 · Answer 1 · answered Feb 01 '15 at 05:54

As @mu mentioned, this means that you can't apply this index, because in your current database state you have duplicated user_id, tweet_id pairs. So you should remove them before running a migration.

To find them, open console and fire this command, which will show you those duplicates:

Favourite.select('user_id, tweet_id').group(:user_id, :tweet_id).having('count(*) > 1')

Migration ERROR when adding unique constraint to model! "standard error"?

1 Answers1