CODEWITHSUNDEEP
mysqlruby-on-railsrubydatabase-migration
Jeff
Jeff

Reputation: 16031

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?

Upvotes: 44

Views: 30070

Answers (6)

Isaac Betesh
Isaac Betesh

Reputation: 3000

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
end

add_check_constraint TABLE_NAME, conditions
remove_check_constraint TABLE_NAME, CONSTRAINT_NAME

Note that at this time, only postgres is supported.

Upvotes: 5

Marian13
Marian13

Reputation: 9238

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'
  end

  def down
    remove_check_constraint :table_name, name: 'check_constraint_name'
  end
end

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

Upvotes: 46

John Doe
John Doe

Reputation: 401

You can use Sequel gem https://github.com/jeremyevans/sequel

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

Upvotes: 2

Valera Prokopchuk
Valera Prokopchuk

Reputation: 126

You can do it with Migration Validators gem. See details here: https://github.com/vprokopchuk256/mv-core

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]
  end
end

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" }
  end
end

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

class Post < ActiveRecord::Base 
  enforce_migration_validations
end

and then validation defines in migration will be also defined as ActiveModel validation in your model: 

Post.new(priority: 3).valid? 
=> true

Post.new(priority: 4).valid?
=> false

Post.new(priority: 4).errors.full_messages
=> ["Priority can't be anything else than 1, 2, or 3"]

Upvotes: 5

ronen
ronen

Reputation: 2598

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 http://guides.rubyonrails.org/migrations.html#types-of-schema-dumps

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 https://github.com/vprokopchuk256/mv-core 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 ... )")
        end
    end

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.)

Upvotes: 4

Nilesh
Nilesh

Reputation: 1159

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) )"
  end

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

Upvotes: 56

Related Questions