Beyond Unique Constraints in Odoo
Tags: odoo, postgres, gist, constraints
Published: 2024-05-08 19:15:07

Inspired by <blockquote>PostgreSQL exclusion constraints: Beyond UNIQUE ~ by Hans-Jürgen Schönig </blockquote> 


In Odoo platform you could apply model constraints in two methods. the first one is using **SQL** constraints and the second is using **Python**. Odoo provide Python constraints to give you the capability to apply checks on sophisticated logic.

An example of applying `unique` using **SQL** constraints in Odoo framework by adding `_sql_constraints` class attribute.

```python
_sql_constraints = [
    (
        'code_company_uniq',
        'unique (code,company_id)',
        'The code of the account must be unique per company !'
    )
]
```
[code snippet example](https://github.com/odoo/odoo/blob/5112935fa216758e0d522f1ee8b69d34f446487c/addons/account/models/account_account.py#L111)

such simple constrains could be applied using python logic as following:

```python
@api.constrains("code", "company_id")
def _uniqe_code_per_company(self):
    records = self._read_group(
        domain=[("code", "=", self.code), ("company_id", "=", self.company_id.id)],
        fields=["company_id", "ids:array_agg(id)"],
        groupby=["company_id"],
    )
    for rec in records:
        if rec["company_id_count"] >= 2:
            raise ValidationError(_("The code of the account must be unique per company !"))
```
[code snippet example](https://github.com/odoo/odoo/blob/5112935fa216758e0d522f1ee8b69d34f446487c/addons/account/models/account_account.py#L26)

as it appears the Python code is more than the SQL code. in a different scenario you may need to check uniqueness between ranges data-structures. like dates cannot be overlapped, applying such scenario using Python would require a lot of logic. luckily, we could use Postgresql exclusion constraints in such case. imagine you have the following model.

|KPI NAME|Bottom Percentage|Top Percentage|
|--------|--------|--------|
|Sales Target Achieved|85%|95%|
|Sales Target Achieved|95%|105%|
|Sales Target Achieved|105%|125%|
|Sales Target Achieved|125%|150%|

and the model definition in Odoo is as following:

```python
class SalesKPIPercentage(models.Model):
    _name = "sales.kpi.percentage"

    type_kpi_id = fields.Many2one("sales.kpi.type", string="KPI Name", required=True)
    percent_bottom = fields.Float(
        string="Bottom Percentage",
        required=True,
    )
    percent_top = fields.Float(
        string="Top Percentage",
        required=True,
    )
```
to apply a constraints so no records to have overlapped percentage with the same type. you could use SQL contraints usign `GIST` as following:

```python
_sql_constraints = [
    (
        "no_overlapped_kpis",
        """
            EXCLUDE USING GIST (
                numrange(percent_bottom::numeric, percent_top::numeric) WITH &&,
                int4range(type_kpi_id, type_kpi_id, '[]') WITH =
            )
        """,
        "KPI category Overlapped",
    ),
]
```
please note that you could also compare against `fields.Char` definition. forexample if the field definition of `type_kpi_id` is of type `fields.Selection` as:
```python
type_kpi_id = fields.Many2one([
    ('sale_target_amount', 'Sales Target Amount'),
    ('sale_target_qty', 'Sales Target Quantity'),
], string="KPI Name", required=True)
```
the SQL constraints would be as:
```python
_sql_constraints = [
    (
        "no_overlapped_kpis",
        """
            EXCLUDE USING GIST (
                numrange(percent_bottom::numeric, percent_top::numeric) WITH &&,
                type_kpi_id WITH =
            )
        """,
        "KPI category Overlapped",
    ),
]
```
and in such case you will need to enable a postgresql `btree_gist` extension using a `pre_init_hook` as:

```python
def add_pg_extensions(cr, registry):
    cr.execute("CREATE EXTENSION IF NOT EXISTS btree_gist")
```