Odoo IntegrityError: Unique Constraint Violated — Fix Guide

The Error

psycopg2.errors.UniqueViolation: duplicate key value violates
unique constraint "my_model_name_unique"
DETAIL: Key (name)=(Widget A) already exists.

What It Means

You're trying to create or update a record with a value that already exists in a column (or combination of columns) that has a UNIQUE constraint. Odoo prevents this to maintain data integrity.

Cause 1: Duplicate Record Creation

# Trying to create a record with the same unique value:
env['product.product'].create({'default_code': 'SKU-001'})  # Already exists!

# Fix: search first, then create or update
existing = env['product.product'].search([('default_code', '=', 'SKU-001')], limit=1)
if existing:
    existing.write({'name': 'Updated Widget'})
else:
    env['product.product'].create({'default_code': 'SKU-001', 'name': 'New Widget'})

Cause 2: CSV/Excel Import

# Importing a CSV with duplicate values in a unique column:
# Row 1: SKU-001, Widget A
# Row 5: SKU-001, Widget B  ← DUPLICATE!

# Fix options:
# 1. Remove duplicates from CSV before import
# 2. Use the "id" column (External ID) for upsert behavior
# 3. Clean data: sort by the unique column and remove dupes

# In Excel: Data → Remove Duplicates → select the unique column

Cause 3: Sequence/Code Conflict

# Invoice or reference numbers colliding:
# This happens when:
# - Sequence was reset manually
# - Database was partially restored
# - Multiple workers generated the same sequence simultaneously

# Check the sequence:
SELECT * FROM ir_sequence WHERE code = 'sale.order';

# Fix: advance the sequence past existing records:
SELECT setval(
    (SELECT pg_get_serial_sequence('sale_order', 'id')),
    (SELECT MAX(id) FROM sale_order) + 1
);

# Or update the ir_sequence number_next:
UPDATE ir_sequence SET number_next = 1500
WHERE code = 'sale.order' AND company_id = 1;

Cause 4: Concurrent Write Race Condition

# Two requests try to create the same record simultaneously:
# Worker 1: create({'email': '[email protected]'})  →  INSERT
# Worker 2: create({'email': '[email protected]'})  →  INSERT  ← CONFLICT!

# Fix: use a try/except pattern:
try:
    record = env['my.model'].create(vals)
except IntegrityError:
    env.cr.rollback()
    record = env['my.model'].search([('email', '=', vals['email'])], limit=1)
    if record:
        record.write(vals)

Cause 5: Module Upgrade Constraint Issue

# Adding a new UNIQUE constraint to an existing table with duplicates:
# Odoo 19 _sql_constraints may fail silently on -u

# Check for existing duplicates BEFORE adding constraint:
SELECT name, COUNT(*)
FROM my_model
GROUP BY name
HAVING COUNT(*) > 1;

# Fix duplicates, then add constraint:
# Use init() with CREATE UNIQUE INDEX IF NOT EXISTS
def init(self):
    self.env.cr.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS my_model_name_unique
        ON my_model (name)
        WHERE active = True
    """)

Cause 6: Soft-Delete / Active Field

# Record was soft-deleted (active=False) but unique constraint
# still includes it:

# Problem: Can't create "Widget A" because a deleted "Widget A" exists

# Fix: Use partial unique index that only covers active records:
CREATE UNIQUE INDEX my_model_name_active_unique
ON my_model (name)
WHERE active = True;

# Or in Odoo 19:
def init(self):
    self.env.cr.execute("""
        CREATE UNIQUE INDEX IF NOT EXISTS my_model_name_active_unique
        ON my_model (name)
        WHERE active = True
    """)

Finding Duplicates

# Find all duplicates for any column:
SELECT column_name, COUNT(*) as count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1
ORDER BY count DESC;

# Find specific duplicates with IDs:
SELECT id, name, create_date
FROM my_model
WHERE name IN (
    SELECT name FROM my_model
    GROUP BY name HAVING COUNT(*) > 1
)
ORDER BY name, id;

# Delete duplicates (keep oldest):
DELETE FROM my_model
WHERE id NOT IN (
    SELECT MIN(id) FROM my_model GROUP BY name
);

Prevention

• Always search before create in custom code
• Use upsert patterns (search + create/update)
• Validate CSV data before import (remove duplicates)
• Use partial unique indexes for soft-delete models
• Handle IntegrityError in concurrent operations

DeployMonkey

DeployMonkey's AI agent identifies duplicate records and constraint violations. It suggests the exact fix — whether it is a data cleanup, sequence reset, or constraint modification.