Welcome back! In previous lessons, we learned how to make our forms dynamic and user-friendly using @api.onchange to warn users about mistakes. But here is a harsh truth about software development: Never trust the user, and never rely solely on the user interface for data security.

If an onchange method warns a user that a price shouldn't be negative, a stubborn user might just ignore the warning and click "Save" anyway. Or, what if another application creates a record via an API call, bypassing your form entirely?

To truly protect your database from bad data, you must enforce strict rules at the model level. In Odoo 19, we have two powerful ways to do this: SQL Constraints and Python Constraints. Let's break them down.

1. SQL Constraints (_sql_constraints)

Think of an SQL constraint as a bouncer standing directly at the door of your PostgreSQL database. Before any data is physically written to the hard drive, PostgreSQL checks the rule. If the rule is broken, the database violently rejects the data.

Because these rules are handled directly by PostgreSQL (and not Python), they are incredibly fast and highly efficient.

We define them using a special list of tuples called _sql_constraints inside our model. Each tuple requires three things: ('Constraint Name', 'SQL Rule', 'Error Message').

The Two Most Common SQL Constraints

A. The UNIQUE Constraint Used to ensure no two records have the exact same value. Perfect for email addresses, SKU codes, or national ID numbers.

B. The CHECK Constraint Used for simple mathematical comparisons (e.g., "Price must be greater than zero").

from odoo import models, fields

class ProductCatalog(models.Model):
    _name = 'product.catalog'
    _description = 'Product Catalog'

    name = fields.Char(string='Product Name', required=True)
    sku = fields.Char(string='SKU Code', required=True)
    price = fields.Float(string='Price')

    # Defining our database-level constraints
    _sql_constraints = [
        # 1. Name, 2. SQL logic, 3. User-friendly error message
        ('sku_unique', 'unique(sku)', 'This SKU code already exists! SKUs must be unique.'),
        ('price_positive', 'check(price >= 0)', 'The product price cannot be negative!')
    ]

2. Python Constraints (@api.constrains)

SQL constraints are amazing for simple checks, but they are limited. They cannot easily check data in other models, and they can't run complex business logic.

What if you want to ensure that a "Manager" assigned to an employee actually works in the same "Department" as that employee? You can't write that in simple SQL easily. You need Python!

Python constraints use the @api.constrains decorator. They run in the Odoo ORM (Object-Relational Mapping) layer before the data is sent to the database. If the logic fails, you raise a ValidationError.

from odoo import models, fields, api
from odoo.exceptions import ValidationError # You MUST import this!

class ProjectTask(models.Model):
    _name = 'project.task'
    _description = 'Project Task'

    name = fields.Char(string='Task Name')
    start_date = fields.Date(string='Start Date')
    end_date = fields.Date(string='End Date')
    assigned_user_id = fields.Many2one('res.users', string='Assigned To')

    # We tell Odoo which fields should trigger this check
    @api.constrains('start_date', 'end_date')
    def _check_dates(self):
        # Always loop through self in constrains!
        for record in self:
            if record.start_date and record.end_date:
                if record.start_date > record.end_date:
                    # If the rule is broken, crash the process and show this message
                    raise ValidationError("The End Date cannot be earlier than the Start Date!")
                        @api.constrains('assigned_user_id')
    def _check_user_active(self):
        for record in self:
            # We can use Python to check related models!
            if record.assigned_user_id and not record.assigned_user_id.active:
                raise ValidationError("You cannot assign a task to an archived user.")

3. The Golden Rule: Which one should I use?

It is very common for students to get confused about when to use which tool. Here is your definitive developer cheat sheet:

💡 Developer Pro-Tips for Odoo 19

1. Changing SQL Constraints: If you update an _sql_constraint in your Python code, Odoo sometimes struggles to update it in the PostgreSQL database automatically during a module upgrade. If your new SQL constraint isn't working, you might need to drop the old constraint directly in the database or uninstall/reinstall the module (in a dev environment).

2. Never Catch Validation Errors: In your custom code, never write a try/except block that catches a ValidationError just to ignore it. These errors are meant to stop bad data dead in its tracks.

3. The _check_ Naming Convention: Always name your @api.constrains functions starting with _check_ (e.g., _check_age, _check_dates). This makes your code clean and instantly recognizable to other Odoo developers.

Homework for this lesson: Create an employee.profile model with a phone_number (Char) field and an age (Integer) field.

1. Write an _sql_constraint to ensure the phone_number is unique.

2. Write an @api.constrains method to ensure the age is exactly 18 or older. Test what happens when you try to save a 17-year-old!