rebnoob rebnoob - 14 days ago 5
Ruby Question

How to check constraints in Sequel

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

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

Comments