Postgres constraints and triggers migration examples

add_empty_check_for_name_and_ams_key_to_carriers.rb

class AddEmptyCheckForNameAndAmsKeyToCarriers < ActiveRecord::Migration
  def up
    execute %{
      ALTER TABLE carriers
        ADD CONSTRAINT check_carriers_name_is_not_empty
        CHECK (name <> '');
    }
    execute %{
      ALTER TABLE carriers
        ADD CONSTRAINT check_carriers_ams_key_is_not_empty
        CHECK (ams_key <> '');
    }
  end

  def down
    execute "ALTER TABLE carriers DROP CONSTRAINT check_carriers_ams_key_is_not_empty;"
    execute "ALTER TABLE carriers DROP CONSTRAINT check_carriers_name_is_not_empty;"
  end
end

add_triggers_to_normalize_name_and_ams_key_to_carriers.rb

class AddTriggersToNormalizeNameAndAmsKeyToCarriers < ActiveRecord::Migration
  def up
    execute %{
      CREATE FUNCTION normalize_carriers_name_and_ams_key() RETURNS trigger AS $$
      BEGIN
        NEW.name := TRIM(NEW.name);
        NEW.ams_key := UPPER(TRIM(NEW.ams_key));
        RETURN NEW;
      END;
      $$ language plpgsql;
    }
    execute %{
      CREATE TRIGGER normalize_carriers_name_and_ams_key_trigger
        BEFORE INSERT OR UPDATE
        ON carriers
        FOR EACH ROW
        EXECUTE PROCEDURE normalize_carriers_name_and_ams_key();
    }
  end

  def down
    execute "DROP TRIGGER normalize_carriers_name_and_ams_key_trigger ON carriers;"
    execute "DROP FUNCTION normalize_carriers_name_and_ams_key();"
  end
end

add_unique_case_insenstive_indexes_to_carriers.rb

class AddUniqueCaseInsenstiveIndexesToCarriers < ActiveRecord::Migration
  def up
    execute %{
      CREATE UNIQUE INDEX index_carriers_on_lowercase_name
        ON carriers
        USING btree (lower(name));
    }
    execute %{
      CREATE UNIQUE INDEX index_carriers_on_uppercase_ams_key
        ON carriers
        USING btree (upper(ams_key));
    }
  end

  def down
    execute "DROP INDEX index_carriers_on_uppercase_ams_key;"
    execute "DROP INDEX index_carriers_on_lowercase_name;"
  end
end