Not all business rules are absolute—sometimes data validity depends on context. A field might be optional in one scenario but required in another. A value might be acceptable in draft mode but invalid when confirmed. This is where conditional constraints in Odoo 19 shine, allowing you to enforce rules that adapt based on the state or values of other fields.
Understanding Conditional Constraints
Conditional constraints are SQL CHECK constraints that validate data based on specific conditions. Unlike simple constraints that apply universally, conditional constraints evaluate multiple fields together, enforcing rules only when certain criteria are met. They operate at the database level, providing the same performance and reliability as standard SQL constraints while offering contextual intelligence.
These constraints use SQL's CHECK clause with logical operators (AND, OR, NOT) to create sophisticated validation rules that reflect real-world business scenarios.
Syntax and Structure
Conditional constraints in Odoo 19 follow the same models.Constraint pattern but with more complex SQL expressions:
# -*- coding: utf-8 -*-
_constraint_name = models.Constraint(
'CHECK(condition)',
'Error message explaining the violation'
)
The key difference lies in the CHECK expression, which can include:
- Multiple field references
- Logical operators (AND, OR, NOT)
- Comparison operators (=, !=, >, <, >=, <=)
- NULL checks (IS NULL, IS NOT NULL)
- State-based conditions (IN, NOT IN)
Use Cases
Range Validation with Context
Sometimes a value's acceptable range depends on another field. For example, a discount percentage might have different limits based on customer type:
class SaleOrderLine(models.Model):
_name = 'sale.order.line'
discount = fields.Float(string='Discount (%)')
customer_type = fields.Selection([
('regular', 'Regular'),
('vip', 'VIP'),
('wholesale', 'Wholesale')
])
_discount_limit_by_type = models.Constraint(
"CHECK((customer_type = 'regular' AND discount <= 10) OR "
"(customer_type = 'vip' AND discount <= 25) OR "
"(customer_type = 'wholesale' AND discount <= 50))",
'Discount exceeds the maximum allowed for this customer type.'
)
Positive Value Constraints
A classic example is ensuring numerical fields contain only positive values when they're expected to represent quantities, ages, or amounts:
class Student(models.Model):
_name = 'school.student'
age = fields.Integer(string='Age')
_positive_age = models.Constraint(
'CHECK(age > 0)',
'Student age must be greater than zero.'
)
This simple yet effective constraint prevents illogical data like negative ages or zero values from entering your system.
Date Range Validation
Conditional constraints excel at ensuring date relationships remain logical. For a project management system, you might need to verify that start dates precede end dates, but only when both are provided:
class Project(models.Model):
_name = 'project.project'
date_start = fields.Date(string='Start Date')
date_end = fields.Date(string='End Date')
_valid_date_range = models.Constraint(
'CHECK(date_start IS NULL OR date_end IS NULL OR date_start <= date_end)',
'Project end date must be after the start date.'
)
This constraint allows either date to be empty but ensures that when both are present, they form a valid range.
Advantages of Conditional Constraints
Context-Aware Validation: Rules adapt based on record state, field values, or combinations of conditions, reflecting real business processes.
Database-Level Enforcement: Like all SQL constraints, these operate at the database layer, ensuring data integrity regardless of how data enters the system.
Performance: Database engines are optimized for evaluating CHECK constraints, making them faster than equivalent Python validations.
Immediate Feedback: Users receive instant error messages when conditions are violated, preventing workflow interruptions.
Data Protection: These constraints work even during bulk imports, API calls, or direct database modifications.
When to Use Conditional Constraints
Conditional constraints are most effective for:
- State-dependent required fields (fields mandatory only in certain states)
- Value ranges that vary based on categories or types
- Date relationships with optional fields
- Business rules expressible in SQL logic
- Validations that must survive direct database access
For rules requiring complex calculations, access to related records beyond simple foreign keys, or Python-specific logic, use the @api.constrains decorator instead.
Best Practices
Write Clear Conditions: Use parentheses generously to make logical precedence explicit and avoid ambiguity.
Provide Context in Messages: Error messages should explain not just what's wrong, but under what conditions the rule applies.
Test Edge Cases: Verify your constraints handle NULL values, empty states, and boundary conditions correctly.
Keep Logic at the Database: Don't try to implement complex business logic that requires Python's flexibility in SQL constraints.
Document Complex Rules: Add comments explaining the business rationale behind sophisticated conditional logic.
Common Patterns
The "Required When" Pattern
CHECK((state = 'target_state' AND field IS NOT NULL) OR state != 'target_state')
The "Valid Range When" Pattern
CHECK((condition AND value BETWEEN min AND max) OR NOT condition)
The "Either Or" Pattern
CHECK(field1 IS NOT NULL OR field2 IS NOT NULL)
The "Mutually Exclusive" Pattern
CHECK((field1 IS NOT NULL AND field2 IS NULL) OR (field1 IS NULL AND field2 IS NOT NULL))
Conclusion
Conditional constraints bring intelligence to database-level validation, allowing you to enforce business rules that depend on context rather than applying blindly to all scenarios. By combining the speed and reliability of SQL constraints with conditional logic, you create a robust data validation layer that accurately reflects your business requirements. When used appropriately alongside Python constraints, they form a comprehensive validation strategy that keeps your Odoo data accurate, consistent, and meaningful across all business states and workflows.
To read more about Overview of SQL constraints in Odoo 19, refer to our blog Overview of SQL constraints in Odoo 19.