Update or delete on table "vpx_entity" violates foreign key constraint "fk_vpx_enti_ref_vpx_enti" on table "vpx_entity"

Products

• VMware vCenter Server

Issue/Introduction

The following symptoms can be observed while deleting a Datacenter on vCenter.

• Deleting a Datacenter crashes vCenter GUI.
• VPXD crashes and vCenter has to be rebooted.
• Deleting the entity from VCDB table 'vpx_entity' throws foreign key constraint 'fk_vpx_enti_ref_vpx_enti'.

Environment

VMware vCenter Server 7.0.x
VMware vCenter Server 8.0.x

Cause

VCDB table 'vpx_entity' has foreign key constraint 'fk_vpx_enti_ref_vpx_enti'.

Resolution

• Take proper snapshot of the VC. 
• Backing up VCDB is also recommended.
• Stop VPXD service: service-control --stop vpxd
• Connect to VCDB: /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
  
  NOTE: Before altering the table confirm that the vpx_entity(id) referenced in vpxd.log correlates with the ID in the VCDB
  
  select * from vpx_entity where name like '%Datacenter_Name%';
  
  
• Alter table: ALTER TABLE vpx_entity DROP CONSTRAINT fk_vpx_enti_ref_vpx_enti, ADD  CONSTRAINT fk_vpx_enti_ref_vpx_enti FOREIGN KEY (PARENT_ID) REFERENCES vpx_entity (id) ON DELETE CASCADE;

• Delete the desired entity: DELETE FROM vpx_entity where id=<item_id>;
• Exit VCDB: \q
• Start VPXD service: service-control --start vpxd

Verify that the Datacenter has been deleted.