Odoo Foreign Key Constraint Violation — 'IntegrityError: insert or update violates foreign key' Fix

Foreign Key Errors in Odoo

Foreign key constraint violations occur when a record references another record that does not exist, or when deleting a record that other records depend on:

psycopg2.errors.ForeignKeyViolation: insert or update on table "sale_order_line"
violates foreign key constraint "sale_order_line_order_id_fkey"
DETAIL: Key (order_id)=(999) is not present in table "sale_order".

psycopg2.errors.ForeignKeyViolation: update or delete on table "res_partner"
violates foreign key constraint "sale_order_partner_id_fkey" on table "sale_order"
DETAIL: Key (id)=(42) is still referenced from table "sale_order".

Type 1: Referenced Record Does Not Exist

Cause: You are creating a record with a Many2one field pointing to a non-existent record ID.

# Error: Key (partner_id)=(999) is not present in table "res_partner"

# Check if the referenced record exists
sudo -u postgres psql -d mydb -c "
  SELECT id, name FROM res_partner WHERE id = 999;
"

# Fix options:
# 1. Use a valid partner ID
env['sale.order'].create({'partner_id': valid_partner_id})

# 2. Create the missing record first
partner = env['res.partner'].create({'name': 'New Partner'})
order = env['sale.order'].create({'partner_id': partner.id})

Type 2: Cannot Delete — Still Referenced

Cause: You are trying to delete a record that other records reference via foreign key.

# Error: Key (id)=(42) is still referenced from table "sale_order"

# Find what references this partner
sudo -u postgres psql -d mydb -c "
  SELECT table_name, column_name
  FROM information_schema.key_column_usage
  WHERE constraint_name IN (
    SELECT constraint_name FROM information_schema.referential_constraints
    WHERE unique_constraint_name IN (
      SELECT constraint_name FROM information_schema.table_constraints
      WHERE table_name = 'res_partner' AND constraint_type = 'PRIMARY KEY'
    )
  );
"

# Fix options:
# 1. Archive instead of delete
partner.active = False

# 2. Delete referencing records first
env['sale.order'].search([('partner_id', '=', 42)]).unlink()
partner.unlink()

# 3. Set ondelete='set null' on the field (if you control the model)
partner_id = fields.Many2one('res.partner', ondelete='set null')

Type 3: Orphaned Records After Direct SQL

Direct SQL deletions bypass Odoo's cascade logic and create orphans:

# BAD: Direct SQL delete creates orphans
DELETE FROM res_partner WHERE id = 42;
# Now sale_order records referencing partner 42 have broken foreign keys

# Find orphaned records
sudo -u postgres psql -d mydb -c "
  SELECT so.id, so.name, so.partner_id
  FROM sale_order so
  LEFT JOIN res_partner rp ON so.partner_id = rp.id
  WHERE rp.id IS NULL AND so.partner_id IS NOT NULL;
"

# Fix orphans: set to NULL or reassign
sudo -u postgres psql -d mydb -c "
  UPDATE sale_order SET partner_id = 1
  WHERE partner_id NOT IN (SELECT id FROM res_partner);
"

Type 4: Data Import Foreign Key Failures

# CSV import fails: partner_id references non-existent partner

# Use external IDs in imports to avoid ID mismatches:
partner_id/id
base.res_partner_admin

# Or import related records first:
# Step 1: Import partners
# Step 2: Import sale orders referencing those partners

Understanding ondelete Options

# Odoo Many2one field ondelete parameter:
partner_id = fields.Many2one('res.partner', ondelete='restrict')   # DEFAULT: block deletion
partner_id = fields.Many2one('res.partner', ondelete='set null')   # Set to NULL on delete
partner_id = fields.Many2one('res.partner', ondelete='cascade')    # Delete this record too

# 'restrict' causes the foreign key error
# Change to 'set null' if the relationship is optional
# Change to 'cascade' if child records should be deleted with parent

Fix 5: Module Uninstall Foreign Key Errors

# When uninstalling a module, foreign keys from other modules may block it

# Check dependencies
sudo -u postgres psql -d mydb -c "
  SELECT name, state FROM ir_module_module
  WHERE name IN (
    SELECT name FROM ir_module_module_dependency
    WHERE depend_id = (SELECT id FROM ir_module_module WHERE name='my_module')
  );
"

# Uninstall dependent modules first, then the target module

Prevention

DeployMonkey validates referential integrity before data operations. The AI agent detects orphaned records during maintenance and ensures proper deletion order to prevent foreign key violations.