OD227 sql-definition-unknown-column¶

An SQL constraint or index names a column its model's table does not have.

What it reports¶

A column a literal constraint or index definition names, when no stored field of the model gives it, at the column. The definitions are the second item of each _sql_constraints entry before 19.0, and the first argument of an attribute that calls Constraint, Index or UniqueIndex, as models.Constraint(...) does, from 19.0; a module whose series is unknown has both checked. A definition that is no literal, such as "CHECK(x > %s)" % limit or a lambda, is skipped, and so is one with a placeholder or an unclosed quote or bracket.

The columns are the bare names of the definition, CHECK expressions and partial index WHERE clauses included. Keywords, calls such as lower(name) (whose arguments are read), casts, typed literals such as date '2024-01-01', qualified names, the method after USING, a collation, an operator class such as gin_trgm_ops, the storage parameters after WITH, a tablespace, and the table and columns after REFERENCES are not.

A name is reported when the model has no field of that name, the field is not stored, is a One2many or a Many2many, is a field of a model it reaches through _inherits, or is a log column that _log_access = False leaves out, by the rules OD224 gives for each series; x_id is the fix for a missing x when the model stores it. As for OD224, id, the log columns of a model that logs access, parent_path under _parent_store, the field an _inherits entry names when no class declares it, Binary, Image and company-dependent fields, columns a module adds by hand, and fields only a module outside the dependencies defines are never reported, nor is a field an ir.model.fields record adds, as OD304 describes: Odoo applies the definition again once the record creates the column. A declared delegating field is judged as any other field, so one that is related and not stored is reported. The entries of the models in _inherit count, a later entry for a parent replacing an earlier one, and of the models one class lists in _inherit, the entry of the first listed winning. The undeclared field is not reported in any series, though Odoo creates it only before saas~16.4, then only for an x_ name until saas~18.2. Nor is any name no field has reported when an _inherits, or an _inherit the model reaches, is not a literal, since an entry the scan cannot see may give it, or a name an entry gives when the order the entries merge in leaves unknown whether that entry is the last for its parent, as it is between two listed models in the cases OD224 names. A module that Odoo always installs with base, such as web, counts as in depends.

Definitions of an abstract model or of a model with _auto = False, _table_query or _table_sql are skipped, as Odoo applies none to them. So are those of a model with a plain Python base class, with an _inherit element that is not a literal, or introduced outside the dependencies, and a module when it or one of its dependencies depends on a module that is not indexed.

Why it matters¶

Odoo adds each constraint with ALTER TABLE ... ADD CONSTRAINT and each index with CREATE INDEX when it installs or updates the module. A missing column makes PostgreSQL refuse the statement, and Odoo logs the error and goes on without the constraint: the module installs, but the uniqueness or the check it was meant to enforce never applies, and the data it should have refused gets in.

Example¶

class ResPartner(models.Model):
    _inherit = "res.partner"

    x_customs_code = fields.Char()

    _sql_constraints = [
        ("x_customs_code_unique", "UNIQUE(x_customs_code, company)", "A customs code is unique per company."),
    ]

Before 19.0 this is reported: the column is company_id, so Odoo adds no constraint and two partners of a company may share a code.

class ResPartner(models.Model):
    _inherit = "res.partner"

    x_customs_code = fields.Char()

    _sql_constraints = [
        ("x_customs_code_unique", "UNIQUE(x_customs_code, company_id)", "A customs code is unique per company."),
    ]

From 19.0 the same mistake in a table object is reported:

class ResPartner(models.Model):
    _inherit = "res.partner"

    x_customs_code = fields.Char()

    _x_customs_code_unique = models.UniqueIndex(
        "(x_customs_code, company) WHERE x_customs_code IS NOT NULL",
        "A customs code is unique per company.",
    )

class ResPartner(models.Model):
    _inherit = "res.partner"

    x_customs_code = fields.Char()

    _x_customs_code_unique = models.UniqueIndex(
        "(x_customs_code, company_id) WHERE x_customs_code IS NOT NULL",
        "A customs code is unique per company.",
    )

Suppressing¶

A finding sits on the column when the definition is a single literal whose source spells its text as is, without an escape such as \n; an SQL comment on that line of a multi-line literal then covers it, since PostgreSQL reads # as an operator:

    _x_code_unique = models.UniqueIndex("""
        (x_code, company)  -- odooscan: ignore[OD227]
    """)

On a definition that fits on one line, a # odooscan: ignore[OD227] after the code covers it:

    _sql_constraints = [("x_code_unique", "UNIQUE(x_code, company)", "")]  # odooscan: ignore[OD227]

Otherwise the finding sits where the literal starts, and that comment alone on the line above covers it.

See suppressing a finding.