How do I add a check constraint in a Rails migration?

I need to add a new integer column to an existing table in my Rails app. The column can only have values 1, 2, 3, so I’d like to add a check constraint to the table/column. How do I specify this constraint within a Rails migration?


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Rails migration does not provide any way to add Constraints, but you can still do it via migration but by passing actual SQL to execute()

Create Migration file:

ruby script/generate Migration AddConstraint

Now, in the migration file:

class AddConstraint < ActiveRecord::Migration
  def self.up
    execute "ALTER TABLE table_name ADD CONSTRAINT check_constraint_name CHECK (check_column_name IN (1, 2, 3) )"

  def self.down
    execute "ALTER TABLE table_name DROP CONSTRAINT check_constraint_name"

Method 2

Rails 6.1+ Check Constraints

Rails 6.1 added basic support for check constraints to database migrations.

So now, a migration for adding a check constraint which restricts integer column values only to 1, 2, and 3 can be written as follows:

class AddConstraint < ActiveRecord::Migration
  def up
    add_check_constraint :table_name, 'check_column_name IN (1, 2, 3)', name: 'check_constraint_name'

  def down
    remove_check_constraint :table_name, name: 'check_constraint_name'

Here is a link to the relative PR where you can find more details about add_check_constraint and remove_check_constraint.

Method 3

You can do it with Migration Validators gem. See details here:

With that gem you’ll be able to define inclusion validation on db level:

def change
  change_table :table_name do |t|
    t.integer :column_name, inclusion: [1, 2, 3]

moreover you is able to define how that validation should be defined and even error message that should be shown:

def change
  change_table :posts do |t|
    t.integer :priority, 
              inclusion: { in: [1, 2, 3], 
                           as: :trigger, 
                           message: "can't be anything else than 1, 2, or 3" }

you can even level up that validation from migration right to your model:

class Post < ActiveRecord::Base 

and then validation defines in migration will be also defined as ActiveModel validation in your model: 3).valid? 
=> true 4).valid?
=> false 4).errors.full_messages
=> ["Priority can't be anything else than 1, 2, or 3"]

Method 4

This answer is obsolete as of May 2021

I just published a gem for this: active_record-postgres-constraints. As the README there describes, you can use it with a db/schema.rb file, and it adds support for the following methods in migrations:

create_table TABLE_NAME do |t|
  # Add columns
  t.check_constraint conditions
  # conditions can be a String, Array or Hash

add_check_constraint TABLE_NAME, conditions
remove_check_constraint TABLE_NAME, CONSTRAINT_NAME

Note that at this time, only postgres is supported.

Method 5

I have just worked through getting a PostgreSQL CHECK constraint to work.

Nilesh’s solution is not quite complete; the db/schema.rb file won’t include the constraint, so tests and any deployments that use db:setup won’t get the constraint. As per

While in a migration you can execute custom SQL statements, the
schema dumper cannot reconstitute those statements from the database.
If you are using features like this, then you should set the schema
format to :sql.

I.e., in config/application.rb set

config.active_record.schema_format = :sql

Unfortunately, if you’re using PostgreSQL you may get an error when loading the resultant dump, see discussion at ERROR: must be owner of language plpgsql. I didn’t want to go down the PostgreSQL configuration path in that discussion; plus in any case i’m fond of having a readable db/schema.rb file. So that ruled out custom SQL in the migration file for me.

The gem suggested by Valera seems promising, but it only supports a limited set of constraints (and I got an error when I tried to use it, though that may be due to incompatibilities with other gems I’m including).

The solution (hack) I went with is to have the model code insert the constraint. Since it’s kindof like a validation, that’s where I put it:

class MyModel < ActiveRecord::Base

    validates :my_constraint

    def my_constraint
        unless MyModel.connection.execute("SELECT * FROM information_schema.check_constraints WHERE constraint_name = 'my_constraint'").any?
            MyModel.connection.execute("ALTER TABLE my_models ADD CONSTRAINT my_constraint CHECK ( ...the SQL expression goes here ... )")

Of course this does an extra select before each validation; if that’s a problem a solution would be to put it in an “after connect” monkey patch such as discussed in How to run specific script after connected to oracle using rails? (You can’t simply cache the result of the select, because the validation/constraint addition happens within a transaction that might get rolled back, so you need to check each time.)

Method 6

You can use Sequel gem

Sequel.migration do
  change do
    create_table(:artists) do
      primary_key :id
      String :name
      constraint(:name_min_length){char_length(name) > 2}

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x