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 positive
• discount must be between 0 and 100
• product_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.