Odoo Model Constraints and Validation: Python and SQL Guide

Two Types of Constraints in Odoo

Odoo provides two constraint mechanisms: Python constraints (flexible, logic-based) and SQL constraints (fast, database-enforced). Each has its strengths, and most real-world modules use both together.

Python Constraints with @api.constrains

Python constraints are decorated methods that raise ValidationError when data is invalid. They run after the ORM writes data but before the transaction commits:

from odoo import models, fields, api
from odoo.exceptions import ValidationError

class SaleOrder(models.Model):
    _inherit = 'sale.order'

    @api.constrains('date_order', 'validity_date')
    def _check_validity_date(self):
        for order in self:
            if order.validity_date and order.date_order:
                if order.validity_date < order.date_order.date():
                    raise ValidationError(
                        'Expiration date cannot be before the order date.'
                    )

Key rules for @api.constrains:

• The decorator arguments list the field names that trigger the check
• The method runs when any listed field changes (create or write)
• Always iterate over self — the recordset may contain multiple records
• Only raise ValidationError, never UserError or generic exceptions
• The constraint runs AFTER the write, so field values are already updated

Common Python Constraint Patterns

Numeric Range Validation

@api.constrains('discount')
def _check_discount(self):
    for record in self:
        if record.discount < 0 or record.discount > 100:
            raise ValidationError('Discount must be between 0 and 100.')

Cross-Field Validation

@api.constrains('minimum_qty', 'maximum_qty')
def _check_qty_range(self):
    for record in self:
        if record.minimum_qty and record.maximum_qty:
            if record.minimum_qty > record.maximum_qty:
                raise ValidationError('Minimum quantity cannot exceed maximum.')

Relational Consistency

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

Conditional Required Fields

@api.constrains('state', 'partner_id')
def _check_partner_required(self):
    for record in self:
        if record.state == 'confirmed' and not record.partner_id:
            raise ValidationError(
                'A partner is required before confirming the order.'
            )

SQL Constraints

SQL constraints are enforced at the database level. They are faster and impossible to bypass — even raw SQL queries must respect them:

class ProductTemplate(models.Model):
    _inherit = 'product.template'

    _sql_constraints = [
        ('unique_default_code',
         'UNIQUE(default_code)',
         'Internal reference must be unique.'),
        ('positive_list_price',
         'CHECK(list_price >= 0)',
         'Sale price must be positive.'),
    ]

The tuple format is: (constraint_name, sql_definition, error_message).

Common SQL Constraint Types

Partial Unique Indexes

Standard _sql_constraints cannot handle conditional uniqueness. For example, you want uniqueness only for active records. Use init():

class DmServer(models.Model):
    _name = 'dm.server'

    def init(self):
        self.env.cr.execute("""
            CREATE UNIQUE INDEX IF NOT EXISTS
                dm_server_unique_hostname_active
            ON dm_server (hostname)
            WHERE (active = true AND
                   (cleanup_state IS NULL OR
                    cleanup_state IN ('pending','cleaning')))
        """)

This is the correct pattern in Odoo 19 because _sql_constraints may not create constraints on existing tables during -u upgrades.

Constraint Execution Order

Understanding when constraints run is critical for debugging:

1. ORM create() or write() is called
2. Field values are written to the database (INSERT/UPDATE)
3. Python @api.constrains methods run
4. SQL constraints are checked when the transaction commits (or on each statement, depending on constraint timing)

This means Python constraints see the new values, but if they pass and the SQL constraint fails, you get a database-level error instead of a clean ValidationError.

Handling Constraint Errors Gracefully

SQL constraint violations produce IntegrityError exceptions that Odoo converts to ValidationError with the message from _sql_constraints. But the error message can be cryptic if not properly defined. Always provide clear messages:

# Bad
_sql_constraints = [
    ('check_amount', 'CHECK(amount > 0)', 'Error!')
]

# Good
_sql_constraints = [
    ('positive_amount',
     'CHECK(amount > 0)',
     'The amount must be strictly positive. Please enter a value greater than zero.')
]

@api.constrains Gotchas

• Does not trigger on related field changes: If order_line.price changes, a constraint on sale.order watching order_line_ids does NOT fire
• Computed fields: Constraints on stored computed fields work, but constraints on non-stored computed fields are ignored
• Batch operations: self can be a multi-record set — always loop
• Import behavior: Constraints run during CSV/Excel import, which can cause batch failures. Consider using _check_* naming for clear error tracing
• sudo() bypass: Python constraints run even under sudo(), but SQL constraints always apply regardless

Testing Constraints

from odoo.tests.common import TransactionCase
from odoo.exceptions import ValidationError

class TestConstraints(TransactionCase):

    def test_negative_discount_rejected(self):
        with self.assertRaises(ValidationError):
            self.env['sale.order.line'].create({
                'order_id': self.order.id,
                'product_id': self.product.id,
                'discount': -10,
            })

Test both valid and invalid cases. For SQL constraints, wrap in self.assertRaises(IntegrityError) or check the ValidationError wrapper.

Best Practices

• Use SQL constraints for simple uniqueness and range checks — they are faster and cannot be bypassed
• Use Python constraints for complex cross-field logic and relational checks
• Always iterate over self in Python constraints
• Provide user-friendly error messages in both constraint types
• Use init() for partial unique indexes in Odoo 19
• Test constraints explicitly in your test suite