We’re running automated test data imports in our QA environment and consistently hitting duplicate key errors. The bulk import process loads test parts and documents but fails midway through with primary key constraint violations. We’ve verified legacy data conflicts exist from previous test runs, but our pre-import validation scripts aren’t catching these duplicates. The error occurs in the WTPart table around 60% through the import:
ERROR: duplicate key value violates unique constraint "wtpart_pkey"
DETAIL: Key (ida2a2)=(12345678) already exists
STATEMENT: INSERT INTO wtpart VALUES (...)
This blocks our test automation pipeline and we need a reliable way to handle existing records before imports. Has anyone implemented effective duplicate detection for bulk test data loads?
I’ve run into similar bulk import challenges. The issue stems from inadequate pre-import validation and legacy data that wasn’t properly cleaned up. Here’s a comprehensive solution addressing all three key areas:
Primary Key Constraint Handling:
First, modify your import process to use PostgreSQL’s ON CONFLICT clause for handling duplicate keys:
INSERT INTO wtpart (ida2a2, wtpartnumber, versionida2a2)
VALUES (?, ?, ?)
ON CONFLICT (ida2a2) DO UPDATE SET
wtpartnumber = EXCLUDED.wtpartnumber;
This allows graceful handling of existing records rather than hard failures.
Legacy Data Conflict Resolution:
Implement a cleanup utility that runs before each test cycle. Query for orphaned test records and remove them systematically:
QuerySpec qs = new QuerySpec(WTPart.class);
qs.appendWhere(new SearchCondition(WTPart.class,
"name", "LIKE", "TEST_%"), null);
QueryResult qr = PersistenceHelper.manager.find(qs);
// Delete with proper cascade handling
Pre-Import Data Validation:
Create a validation framework that profiles the import dataset against existing data:
Duplicate Detection: Hash part number + revision combinations and compare against database
Business Rule Checks: Validate that part numbers follow your naming conventions and don’t conflict with production data patterns
We built a three-stage validation pipeline:
Stage 1: Structural validation (required fields, data types)
Stage 2: Duplicate detection (query existing IDA2A2 and part numbers)
Stage 3: Business rule enforcement (naming patterns, valid lifecycles)
The validation runs in about 90 seconds for 10,000 test parts and catches 95% of potential conflicts before import begins. This reduced our test automation failures from 40% to under 2%. The key is treating test data with the same rigor as production data - proper cleanup, validation, and conflict handling at every stage.
This draft is based on general ENOVIA knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.
I’ve seen this exact issue. The problem is that standard ENOVIA bulk loaders don’t check for existing keys before insertion. You need to add a pre-validation step that queries existing IDA2A2 values and either skips or updates those records. We built a simple Java utility that reads the import file first and flags duplicates.
Tested this on our ENOVIA bulk import pipeline — the PostgreSQL ON CONFLICT clause on ida2a2 eliminated duplicate key failures across 50,000 wtpart records cleanly.
The constraint violation happens because test cleanup isn’t removing all records. Check if your teardown scripts are properly deleting child objects first - BOM links, document associations, etc. Parent records can’t be deleted while references exist. Also verify that your import uses UPSERT logic instead of pure INSERT statements to handle existing keys gracefully.
We solved this by implementing a pre-import data profiling step. Before each bulk load, we query the target tables for existing part numbers and IDA2A2 values, then filter the import dataset to exclude matches. Takes about 2 minutes extra but eliminates all duplicate key errors. The profiling query looks like:
SELECT wtpartnumber, ida2a2 FROM wtpart
WHERE wtpartnumber IN (SELECT part_num FROM import_staging);
This gives you a conflict list to either skip or handle with UPDATE statements instead.
Consider using ENOVIA’s native versioning capabilities for test data. Instead of deleting and recreating parts, version them up for each test cycle. This avoids primary key conflicts entirely since each version gets a new IDA2A2. You’d need to adjust your test queries to always fetch the latest iteration, but it’s cleaner than managing deletes and duplicate checks.
The real issue is that bulk imports bypass business rule enforcement that normally prevents duplicates. Your validation scripts need to replicate ENOVIA’s duplicate checking logic. We implemented attribute completeness checks that verify not just part numbers but also name/revision combinations before allowing imports. This catches logical duplicates even when IDA2A2 differs.