Rails - DB Level Validation for `:start_date` and `:end_date`

Question

I have a question related to the following code in Rails:

class CreateEmployments < ActiveRecord::Migration[6.0]
  def change
    create_table :employments do |t|
      t.string :title, null: false
      t.string :company_name
      t.datetime :start_date
      t.datetime :end_date
      t.integer :user_id

      t.timestamps
    end
  end
end

I'm trying to disallow the db to accept any :start_date value greater than :end_date . I want :end_date to be always greater than :start_date and want to do it at the db level. Is there a way to do it? I know I can use model field validations, but I want to implement it at db level too. Any tips? Thanks in advance!

Answer 1

Rules on DB level are called constraints.

class AddEmploymentsDateConstraint < ActiveRecord::Migration
  def self.up
    execute "ALTER TABLE employments ADD CONSTRAINT employments_date_check CHECK (end_date > start_date)"
  end

  def self.down
    execute "ALTER TABLE employments DROP CONSTRAINT employments_date_check"
  end
end

It is important to know what DB is used. For instance, if it is used SQLite, can not use ALTER TABLE syntax to add constraint - only on CREATE TABLE can add constraint. See answer.

Additionaly, rails 6.1+ supports constraints, see another answer.