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:
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.
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 => trueNow, 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.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.
#
# 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
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
ActiveRecord::StatementInvalid in ... Mysql::Error: Duplicate entry '1409-6622' for key 2: INSERT INTO `wishlist_books` (...) VALUES(...)
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.