Skip to main content

Rails: Handling Duplicate Entries with Unique Indexes

If you add a unique index to a column in a database table, the database will prevent you from having two records with the same value for the given column. Rails has similar functionality at the application level. However, just like with foreign keys, the application isn't in a good position to enforce such a constraint. As soon as you have multiple application servers hitting the same database, it becomes obvious that only the database is in a position to enforce the constraint without suffering from ugly race conditions.

Ok, so how do you let the database enforce the constraint, but still handle duplicate records gracefully? Start with adding the unique index to the database in a migration:
add_index :wishlist_books, [:user_id, :book_id], :unique => true
Now, instead of creating the record directly in the controller, call a method in the model:
current_user.add_book_to_wishlist(book)
In the user model, you might have something like this:
# Add a book to the wish list.
#
# This is idempotent. It will just do the right thing if the user tries to
# insert a duplicate record.
#
# Important: if you call this method, then the next time you load the wish
# list, you must reload to clear the cache. For instance:
#
# current_user.wishlist_books(true)
def add_book_to_wishlist(book)
wishlist_books.create! do |list_item|
list_item.book = book
end
rescue ActiveRecord::StatementInvalid => e
raise e unless /Mysql::Error: Duplicate entry/.match(e)
end
Don't worry too much about the associations. I'm just trying to show the code somewhat in context. The important part is the exception that is caught.

Naturally, this is MySQL specific. I don't know the exact thing to look for with PostgreSQL. However, that's okay. I don't mind using MySQL specific features in my models where it makes sense.

Comments

jjinux said…
By the way, here's the full exception:

ActiveRecord::StatementInvalid in ... Mysql::Error: Duplicate entry '1409-6622' for key 2: INSERT INTO `wishlist_books` (...) VALUES(...)
Anonymous said…
I realize this is very old, but I stumbled upon it via google and noticed a potential snag with the code, so I will point it out: if the attributes are not the same as the existing record, they do not get updated in the database, but the method in the model swallows the exception...
jjinux said…
Thanks for your comment, but are you sure? I think the exception will get re-raised if it doesn't match "Duplicate entry".
mwittig said…
A superior approach is to use MySQL's on-duplicate-key-update functionality. This gem provides an interface to it:
https://github.com/zdennis/activerecord-import

That way you don't get an error if there is a duplicate key, and you can overwrite values of other columns. If the record you're trying to save is at all different from the version in the database, the version in the database will not be modified since the save fails.
jjinux said…
mwittig, nice!

Popular posts from this blog

Ubuntu 20.04 on a 2015 15" MacBook Pro

I decided to give Ubuntu 20.04 a try on my 2015 15" MacBook Pro. I didn't actually install it; I just live booted from a USB thumb drive which was enough to try out everything I wanted. In summary, it's not perfect, and issues with my camera would prevent me from switching, but given the right hardware, I think it's a really viable option. The first thing I wanted to try was what would happen if I plugged in a non-HiDPI screen given that my laptop has a HiDPI screen. Without sub-pixel scaling, whatever scale rate I picked for one screen would apply to the other. However, once I turned on sub-pixel scaling, I was able to pick different scale rates for the internal and external displays. That looked ok. I tried plugging in and unplugging multiple times, and it didn't crash. I doubt it'd work with my Thunderbolt display at work, but it worked fine for my HDMI displays at home. I even plugged it into my TV, and it stuck to the 100% scaling I picked for the othe

Drawing Sierpinski's Triangle in Minecraft Using Python

In his keynote at PyCon, Eben Upton, the Executive Director of the Rasberry Pi Foundation, mentioned that not only has Minecraft been ported to the Rasberry Pi, but you can even control it with Python . Since four of my kids are avid Minecraft fans, I figured this might be a good time to teach them to program using Python. So I started yesterday with the goal of programming something cool for Minecraft and then showing it off at the San Francisco Python Meetup in the evening. The first problem that I faced was that I didn't have a Rasberry Pi. You can't hack Minecraft by just installing the Minecraft client. Speaking of which, I didn't have the Minecraft client installed either ;) My kids always play it on their Nexus 7s. I found an open source Minecraft server called Bukkit that "provides the means to extend the popular Minecraft multiplayer server." Then I found a plugin called RaspberryJuice that implements a subset of the Minecraft Pi modding API for B

ERNOS: Erlang Networked Operating System

I've been reading Dreaming in Code lately, and I really like it. If you're not a dreamer, you may safely skip the rest of this post ;) In Chapter 10, "Engineers and Artists", Alan Kay, John Backus, and Jaron Lanier really got me thinking. I've also been thinking a lot about Minix 3 , Erlang , and the original Lisp machine . The ideas are beginning to synthesize into something cohesive--more than just the sum of their parts. Now, I'm sure that many of these ideas have already been envisioned within Tunes.org , LLVM , Microsoft's Singularity project, or in some other place that I haven't managed to discover or fully read, but I'm going to blog them anyway. Rather than wax philosophical, let me just dump out some ideas: Start with Minix 3. It's a new microkernel, and it's meant for real use, unlike the original Minix. "This new OS is extremely small, with the part that runs in kernel mode under 4000 lines of executable code.&quo