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
– TABISH KHAN, Commented Feb 3, 2015 at 1:19

Tom Gasaner · Accepted Answer · 2015-02-01 05:54:37Z

1

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')

answered Feb 1, 2015 at 5:54

Tom Gasaner

15.8k10 gold badges64 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related