sqlalchemy constraint on a boolean column -

i have table 3 columns a(, b, c) , have 2 constraints

1. a , b should jointly unique have done defining unique constraint.
2. for given value in a, there can 1 true in c. (a, c=true) has unique. need in defining second constraint.

e.g data set

a : b c

a1 : b1 : false

a1 : b2 : true

a1 : b3 : false

a2 : b1 : true

a2 : b2 : false

a2 : b3 : false

i don't think can expressed in database-portable way without changing schema. partial index can this:

create unique index one_c_per_a_idx on table(a) c; 

but available on postgresql , sql server (and simulated functional index on oracle). postgresql back-end can use postgresql_where keyword argument index (see docs) define partial index in sqlalchemy.

a portable way split @ least 2 tables, converting explicit boolean column implicit "present in referencing table" expression. sql:

create table my_table (     integer not null,     b integer not null,     primary key (a, b) );  create table flagged (     integer not null primary key,     b integer not null,     foreign key (a, b) references my_table(a, b) ); 

query equivalent of original table:

select t.a, t.b, f.a not null c my_table t left outer join flagged f on t.a = f.a , t.b = f.b; 

i'll omit sqlalchemy model definitions now, because not sure solution suits needs best.