How to check constraints in Sequel

Question

How do I create a CHECK constraint to check for a range of possible values in Sequel. a Ruby ORM.

All attempts seem to generate CHECK (1 = 0) as seen in the output logs.

Here's the table I'm trying to model using Sequel's DSL:

create table memberships(  
      id integer primary key autoincrement
    , group_id integer references groups(id) on delete cascade
    , user_id integer references users(id) on delete cascade
    , role char check (role in ('u','a','o')) default 'u'
    , unique(group_id, user_id, role)   
);

and here's the Sequel schema generation code:

db.create_table(:memberships){
    primary_key :id
    foreign_key :user_id, :users
    foreign_key :group_id, :groups  
    char :role, default: 'u'
    check{role=='u' or role=='a'} #<-----this line generates CHECK (1 = 0)
    unique [:user_id, :group_id, :role]
}

Answer 1

In Sequel, check constraints are handled just like a filter expression. The recommended way to handle your case would be:

check(:role=>%w[a o u])

I agree that more documentation would probably be better, though there are examples in http://sequel.jeremyevans.net/rdoc/files/doc/schema_modification_rdoc.html

Answer 2

The documentation on how check and add_constraint are supposed to work is rather sparse but you could try bypassing all the magic entirely, write the constraint as you would in SQL, and return that from the block; something like this:

db.create_table(:memberships) {
    #...
    check { "role in ('a', 'o', 'u')" }
    #...
}

The language that is allowed in a CHECK constraint is quite rich and varied so I'd expect a simple string to be an option.

Answer 3

This would be handled elegantly with an enum:

up do
  extension :pg_enum
  create_enum(:role_types, %w[a b c])
  create_table # ...
    role_types :role, null: false
#...

down do
  drop_table # :...
  drop_enum :role_types
end