rails unique index on multiple columns fails (sqlite3)

1
votes

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...

2
ruby-on-rails

2 Answers

4
votes

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

1
votes

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.