Odoo Unique Constraint Violation — 'duplicate key value violates unique constraint' Fix

Unique Constraint Violations in Odoo

When creating or updating records, you encounter:

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint
"ir_model_data_module_name_uniq"
DETAIL: Key (module, name)=(my_module, view_form) already exists.

IntegrityError: duplicate key value violates unique constraint
"res_users_login_key"
DETAIL: Key (login)=(admin) already exists.

ValidationError: The value for field 'Reference' already exists
(existing record with same value: SO042)

Common Unique Constraints in Odoo

Fix 1: External ID Conflicts (ir_model_data)

The most common unique constraint violation in Odoo development:

# Error: duplicate key (module, name)=('my_module', 'view_partner_form')

# Cause: Two XML records in your module use the same id
# File 1: 
# File 2: 

# Fix: Use unique IDs across ALL XML files in your module
# File 1: 
# File 2: 

# If the duplicate already exists in the database:
sudo -u postgres psql -d mydb -c "
  SELECT * FROM ir_model_data
  WHERE module='my_module' AND name='view_partner_form';
  -- Delete the stale entry if needed
  DELETE FROM ir_model_data
  WHERE module='my_module' AND name='view_partner_form' AND model='ir.actions.act_window';
"

Fix 2: Sequence Conflicts

When the PostgreSQL sequence gets out of sync with existing data:

# Error: duplicate key value violates unique constraint "sale_order_pkey"
# Cause: sequence value is behind the max ID in the table

# Find the max ID and current sequence value
sudo -u postgres psql -d mydb -c "
  SELECT MAX(id) FROM sale_order;
  SELECT last_value FROM sale_order_id_seq;
"

# Fix: reset the sequence to max + 1
sudo -u postgres psql -d mydb -c "
  SELECT setval('sale_order_id_seq', (SELECT COALESCE(MAX(id), 0) + 1 FROM sale_order));
"

Fix 3: User Login Duplicates

# Error: duplicate key (login)=([email protected])
# Cause: trying to create a user with an email that already exists

# Find the existing user
sudo -u postgres psql -d mydb -c "
  SELECT id, login, active FROM res_users WHERE login='[email protected]';
"

# If the user is archived (active=false), either:
# Option 1: Reactivate the existing user
sudo -u postgres psql -d mydb -c "
  UPDATE res_users SET active=true WHERE login='[email protected]';
"
# Option 2: Delete the old user record (careful!)
# Option 3: Use a different login email

Fix 4: Barcode Duplicates

# Error: duplicate barcode value
# Find duplicate barcodes
sudo -u postgres psql -d mydb -c "
  SELECT barcode, COUNT(*), array_agg(id)
  FROM product_product
  WHERE barcode IS NOT NULL
  GROUP BY barcode HAVING COUNT(*) > 1;
"

# Fix: clear duplicates
# Option 1: Make barcodes unique manually
# Option 2: Clear duplicate barcodes
sudo -u postgres psql -d mydb -c "
  UPDATE product_product SET barcode = NULL
  WHERE id IN (
    SELECT id FROM (
      SELECT id, ROW_NUMBER() OVER(PARTITION BY barcode ORDER BY id) as rn
      FROM product_product WHERE barcode IS NOT NULL
    ) t WHERE t.rn > 1
  );
"

Fix 5: Custom _sql_constraints

When adding unique constraints to custom models:

class MyModel(models.Model):
    _name = 'my.model'

    reference = fields.Char(string='Reference')
    company_id = fields.Many2one('res.company')

    _sql_constraints = [
        ('reference_company_uniq',
         'UNIQUE(reference, company_id)',
         'Reference must be unique per company!'),
    ]

# If existing data violates the new constraint,
# the module update will fail.
# Fix: clean duplicates BEFORE adding the constraint
def init(self):
    # Remove duplicates first
    self.env.cr.execute("""
        DELETE FROM my_model WHERE id IN (
            SELECT id FROM (
                SELECT id, ROW_NUMBER() OVER(
                    PARTITION BY reference, company_id ORDER BY id
                ) as rn FROM my_model
            ) t WHERE t.rn > 1
        )
    """)
    super().init()

Fix 6: Import Duplicates

# During CSV import, use 'id' column with external IDs to update existing records
# Instead of creating duplicates:
id,name,reference
my_module.record_1,Name 1,REF001
my_module.record_2,Name 2,REF002

# This updates record_1 if it exists, creates if it doesn't

Prevention

DeployMonkey's AI agent validates data integrity before imports and module updates. Duplicate detection runs proactively, and sequence synchronization is part of regular maintenance.