1

Using rails, I set up a HATBM model with a users table, a groups table and a groups_users for the joins (all with scaffold command). Now I want to add a migration to add a unique index on both columns group_id and user_id for the table groups_users, in order to constraint the group_id/user_id couples to be unique. The individual columns group_id and user_id are not null and indexes already.

Here is the line I want executed in the migration:

add_index :groups_users, [:user_id, :group_id], :unique => true

I get the following error:

-- add_index(:groups_users, [:user_id, :group_id], {:unique=>true})
rake aborted!
An error has occurred, this and all later migrations canceled:

SQLite3::ConstraintException: constraint failed: CREATE UNIQUE INDEX "index_groups_users_on_user_id_and_group_id" ON "groups_users" ("user_id", "group_id")

I use rails 3.2.11 on mac os with sqlite3 as the db.

I am sure there must be some obvious explanation, since I did not see any report of that error on the net, but I'm terribly stuck... What constraint is not satisfied here? I tried to remove the indexes on group_id and user_id, but it doesn't change anything. If I create the bi-column index without uniqueness, then it works (but doen't help...)

Someone's help greatly appreciated...

Axel Von Arnim
  • 13
  • 3

2 Answers2

4

Seems as if the UNIQUE constraint could not be fullfilled with the current data in the table. Check with GROUP BY and COUNT.

ckruse
  • 9,642
  • 1
  • 25
  • 25
  • Great! Indeed, I had duplicate couples of group_id,user_id in the table and that prevented the creation of the unique index. I had absolutely not loooked in this direction... Thank you so much! – Axel Von Arnim Feb 07 '13 at 13:48
  • @ckruse I'm having the same issue, but I already manually change the repeated value. As I'm still getting the error, I'm thinking the index on that column has not being updated. How could I fix that? – Sebastialonso May 16 '14 at 19:54
  • 1
    @Sebastialonso Although it is possible that the index doesn't get updated I don't think that is very likely. But you can ensure that very easily by dropping and recreating the index. – ckruse May 19 '14 at 06:54
1

Your "groups_users" table has duplicate data in it so you'd have to either manually clear out the dupes or just reset your database with rake db:reset.

Once you've cleared the duplicates, either by removing all the data from the database or finding the dupes, you should be able to run rake db:migrate without error.

PS, make sure you have a backup of your db before you run the command above because it is destructive.

danielricecodes
  • 3,446
  • 21
  • 23