rails unique index on multiple columns fails (sqlite3)

Question

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

ckruse · Accepted Answer · 2013-02-07 13:35:07Z

4

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

answered Feb 7, 2013 at 13:35

ckruse

9,7701 gold badge27 silver badges25 bronze badges

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

3 Comments

Axel Von Arnim Over a year ago

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!

Sebastialonso Over a year ago

@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?

ckruse Over a year ago

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

danielricecodes · Accepted Answer · 2013-02-07 14:50:07Z

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.

answered Feb 7, 2013 at 14:50

danielricecodes

3,58624 silver badges25 bronze badges

Collectives™ on Stack Overflow

rails unique index on multiple columns fails (sqlite3)

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related