Using PostgreSQL database constraints
Effective use of database constraints can ensure data integrity.
In an earlier blog post we used generated columns in PostgreSQL. In this one we will explore another cool database feature - constraints
.
Many times applications add some business constraints on our data.
eg.
price
must be positivediscount
must be between 0 and 100product_sku
must be 8 character long, etc.
We can encode these in Rails model validations:
validates :price, numericality: { greater_than: 0 }
validates :discount, numericality: { in: 0..100 }
validates :product_sku, length: { 8 }
Above single-column cases should generally work fine in most cases. But let us consider a multi-column case where we want to ensure either the user_id
or channel_id
must be present:
validates :user_id, presence: true, if: -> { channel_id.blank? }
validates :channel_id, presence: true, if: -> { user_id.blank? }
In this case, multiple threads could try to remove one of those ids at the same time, each perfectly passing the Rails validation - and ending up with incorrect data.
This is where we need database constraints to ensure atomic consistency at the database level. We can use a CHECK
constraint with any expression that evaluates to a boolean. For above example, we need the num_nonnulls
operator:
class AddConstraintOnOrders < ActiveRecord::Migration[7.0]
def change
add_check_constraint :orders, "num_nonnulls(user_id, channel_id) > 0",
name: "orders_user_or_channel_present"
end
end
add_check_constraint
was added in Rails 6.1, but you can use plain SQL migrations with earlier Rails versions. Make sure to name the constraint, otherwise database will generate a generic name like chk_rails_abcdef
which is difficult to debug.
We can also compare two columns - and you can also add the constraint at the time of table creation:
class CreateEvents < ActiveRecord::Migration[7.0]
def change
create_table :events, force: true do |t|
t.string :name
t.timestamp :starts_at
t.timestamp :ends_at
t.check_constraint "ends_at > starts_at", name: :events_ends_greater_than_starts
end
end
end
Failure of constraint raises ActiveRecord::StatementInvalid
error, so you might need to handle that.
All code can be found in an executable format in this gist. Read more about PostgreSQL constraints in the docs.