Defer database constraints with Rails + PostgreSQL
January 01, 2020
Database constraints are great. They define the rules the data in our DB must abide by, upholding the integrity of the data it contains.
You’re probably familiar with constraints like
PRIMARY/FOREIGN KEY etc. but are there cases where we would rather ignore a particular constraint?
I recently ran into this issue when updating the item positions in a list.
Say you have a
list_items. You would likely have a multicolumn index on the
list_items table on
position to ensure that not list has more than one list item in the same position.
This makes sense and is a good constraint to apply but what happens when we try to update the position of one or more of the existing list items?
Take the following list for example:
1. albatross 2. eagle 3. birdy
birdy record from position
2 will violate our contraint raising an exception when we try to make the update as that position is already held by
One workaround is to keep track of what all the new positions will be in our application logic, clear all of the list item positions, and subsequently update with the newly calculated positions.
While this approach works, it complicates our application code and involves unnecessary writes to the DB.
Alternatively we can make the uniquness constraint on
position deferrable while updating the list item positions:
... def up remove_index :list_items, [:list_id, :position] execute <<~SQL ALTER TABLE list_items ADD CONSTRAINT list_items_position UNIQUE (list_id, position) DEFERRABLE INITIALLY IMMEDIATE; SQL end def down execute <<~SQL ALTER TABLE list_items DROP CONSTRAINT list_items_position; SQL add_index :list_items, [:list_id, :position], unique: true end ...
... ActiveRecord::Base.transaction do ActiveRecord::Base.connection.execute('SET CONSTRAINTS list_items_position DEFERRED') list_item_ids.each_with_index do |id, index| list_item = ListItem.find(id) # We use update_attribute to avoid triggering application level constraints. list_item.update_attribute(:position, index) end end ...
We can now reorder our list items, deferring the constraint until the end of the transaction.