Last active
February 18, 2016 05:17
-
-
Save vlado/81de6a20c50ccc7323b2 to your computer and use it in GitHub Desktop.
Postgres constraints and triggers migration examples
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment