Community mailing list archives

Re: Conditional Unique Constraint

OpusVL, Nick Booker
- 08/07/2015 05:28:59
vak0160 <> writes:

> Hello Community,
> How can I create a unique constraint, but only when active field is
> True?

What you need to do is create an ordinary unique constraint in your
_sql_constraints list, and then in the 'init' method (not __init__)
you drop that constraint and create UNIQUE INDEX ... WHERE constraint.

This example makes the 'ref' field unique for all records where supplier
and is_company are both true.

Note it's on the old API at the moment, but the interfaces in question
haven't changed for the new one as it happens (though you might need an
@api.model on top of init - you'll need to test that).

class your_res_partner(osv.osv):
    _inherit    = "res.partner"

    def init(self, cr):
            SELECT COUNT(1)
            FROM information_schema.constraint_column_usage
            WHERE table_name = 'res_partner'
                  AND constraint_name = 'res_partner_supplier_ref_uniq_idx'
        if cr.fetchone()[0] > 0:
                ALTER TABLE res_partner DROP CONSTRAINT res_partner_supplier_ref_uniq_idx
                CREATE UNIQUE INDEX res_partner_supplier_ref_uniq_idx
                ON res_partner (ref)
                WHERE is_company = true AND supplier = true

    _sql_constraints = [
        ('supplier_ref_uniq_idx', 'unique(ref)', 'The supplier reference must be unique!'),

Your WHERE clause on the last cr.execute() would be:
  WHERE active = true

Thanks, by the way, you've prompted me to consider whether I need to add
AND active = true to that too...

Nick Booker