Data Inconsistency Due to Missing Constraints (Odoo)

#python #database #backend #sql

Why This Problem Happens (Root Cause)
In Odoo, data inconsistency occurs when:

❌ No SQL constraints exist at database level
❌ No Python constraints exist at ORM level
❌ Imports, RPC calls, or automated scripts bypass UI validations
❌ Multiple users create records concurrently

Result:

Duplicate records
Invalid field values
Broken business logic

Step 1: Identify What Must Be Unique or Valid

Before coding, clearly define:
Rule of thumb

SQL constraint → absolute rule (never allowed)
Python constraint → business logic rule

Step 2: Add SQL Constraints (Database-Level Protection)
Use Case: Prevent Duplicate Employee Codes
❌ Problem (No constraint)
employee_code = fields.Char()

Multiple records can have same code.
Solution:_sql_constraints

class HrEmployee(models.Model):
    _inherit = 'hr.employee'

    employee_code = fields.Char(required=True)

    _sql_constraints = [
        (
            'employee_code_unique',
            'unique(employee_code)',
            'Employee Code must be unique.'
        )
    ]

Why SQL Constraints Matter

Enforced at database level
Works for:
Imports
RPC calls
Background jobs
Fast & reliable

Step 3: Add Python Constraints (Business Logic Validation)
Use Case: Start Date must be before End Date
Problem
Users can save invalid date ranges.

Solution: @api.constrains

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

class ProjectContract(models.Model):
    _name = 'project.contract'

    start_date = fields.Date(required=True)
    end_date = fields.Date(required=True)

    @api.constrains('start_date', 'end_date')
    def _check_date_range(self):
        for record in self:
            if record.start_date and record.end_date:
                if record.start_date > record.end_date:
                    raise ValidationError(
                        "Start Date must be before End Date."
                    )

Why Python Constraints Matter

Allows complex logic
Clear error messages
Business-rule focused

Step 4: Prevent Duplicate Records Based on Multiple Fields
Use Case: Same Customer + Product not allowed
SQL Constraint (Multi-column)

_sql_constraints = [
    (
        'customer_product_unique',
        'unique(customer_id, product_id)',
        'This customer already has this product.'
    )
]

Step 5: Validate Data During Create & Write (Extra Safety)
Sometimes constraints alone aren’t enough.
Example: Prevent Negative Amounts

@api.model
def create(self, vals):
    if vals.get('amount', 0) < 0:
        raise ValidationError("Amount cannot be negative.")
    return super().create(vals)

def write(self, vals):
    if 'amount' in vals and vals['amount'] < 0:
        raise ValidationError("Amount cannot be negative.")
    return super().write(vals)

Step 6: Handle Existing Duplicate Data (Very Important)

Adding constraints will FAIL if bad data already exists.

Step-by-step cleanup approach:
1 Detect duplicates

SELECT employee_code, COUNT(*)
FROM hr_employee
GROUP BY employee_code
HAVING COUNT(*) > 1;

2 Fix manually or via script
3 Then add constraints

Step 7: Combine SQL + Python Constraints (Best Practice)
Scenario Solution
Absolute uniqueness SQL constraint
Business rules Python constraint
User-friendly errors Python constraint
Performance SQL constraint

Use BOTH whenever possible
Final Best-Practice Checklist