Odoo 19 Constraint Syntax Guide: SQL and Python Constraints

Constraints in Odoo 19

Constraints protect data integrity at the model level. Odoo supports two types: SQL constraints enforced by the database engine and Python constraints enforced by application logic. Understanding when to use each type and how to implement them correctly prevents data corruption and provides clear user feedback.

SQL Constraints

Basic Syntax

SQL constraints are defined as a class attribute using _sql_constraints:

_sql_constraints = [
    ('name_unique', 'UNIQUE(name)', 'The name must be unique.'),
    ('amount_positive', 'CHECK(amount > 0)', 'The amount must be positive.'),
]

Each tuple has three elements: constraint name, SQL expression, and error message shown to users.

Common SQL Constraint Patterns

SQL Constraint Caveats in Odoo 19

A critical issue: _sql_constraints may not create constraints on existing tables during module upgrade (-u). If you add a constraint to a model that already has data, the constraint may silently fail to be created — especially if existing data violates it.

The robust approach is using init() with raw SQL:

def init(self):
    self.env.cr.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS
            my_model_name_unique
        ON my_model (name)
        WHERE active = True
    """)

This pattern also supports partial unique indexes (with WHERE clauses), which _sql_constraints cannot express.

Python Constraints

Basic Syntax

Python constraints use the @api.constrains decorator:

from odoo.exceptions import ValidationError

@api.constrains('email')
def _check_email(self):
    for record in self:
        if record.email and '@' not in record.email:
            raise ValidationError(
                "Invalid email address: %s" % record.email
            )

When to Use Python vs SQL

• SQL — Simple value checks (positive, unique, range). Enforced at the database level, cannot be bypassed. Fastest execution.
• Python — Complex business logic, cross-field validation, relational checks (checking related records). More flexible but only enforced at the ORM level.

Multi-Field Python Constraints

Validate relationships between fields:

@api.constrains('date_start', 'date_end')
def _check_dates(self):
    for record in self:
        if record.date_end and record.date_start:
            if record.date_end < record.date_start:
                raise ValidationError(
                    "End date must be after start date."
                )

Relational Constraints

Validate against related records:

@api.constrains('parent_id')
def _check_recursion_constraint(self):
    if not self._check_recursion():
        raise ValidationError(
            "Error: You cannot create recursive categories."
        )

Important Rules

Constraint Naming

• SQL constraint names must be unique within the module
• Odoo prefixes them with the table name automatically
• Use descriptive names: positive_amount, unique_email_per_company

Error Messages

• Use translatable strings with _() for Python constraints
• SQL constraint messages are also translatable via Odoo's translation system
• Include the problematic value in Python error messages for debugging

Self Iteration

Always iterate over self in Python constraints — the decorator may pass a recordset with multiple records during batch operations.

Constraint Timing

• SQL constraints are checked at commit time (or flush)
• Python constraints are checked immediately on create/write
• @api.constrains only triggers when the listed fields are in the write values

Testing Constraints

def test_positive_amount(self):
    with self.assertRaises(ValidationError):
        self.env['my.model'].create({
            'name': 'Test',
            'amount': -5,
        })

Test both valid and invalid data to ensure constraints fire correctly and allow legitimate data through.