Spec management data migration fails on BOM hierarchy mismatch between source and target schemas

PLM Windchill
, , , , , , , ,
1

We’re migrating spec management data from Windchill 11.2 M030 to a newer environment and hitting critical errors related to BOM hierarchy structure. The migration script fails when processing parent-child relationships:


ERROR: Foreign key constraint violation
Table: spec_bom_structure
Column: parent_spec_id references non-existent parent

The issue appears to be schema differences in how BOM parent-child mappings are stored between source and target. Our source system uses a flat reference table while the target expects a hierarchical structure with proper ancestry chains.

This is blocking our go-live as we have incomplete BOMs in the target system. The migration validation scripts show 847 orphaned spec items out of 12,000 total records. Has anyone dealt with BOM hierarchy schema differences during Windchill data migration?

system 2

I’ve handled this exact scenario in multiple 11.2 migrations. Here’s the complete solution addressing all three focus areas:

BOM Parent-Child Mapping Strategy: Create a staging table that reconstructs the hierarchy:

CREATE TABLE spec_hierarchy_stage AS
SELECT spec_id, parent_id,
  LEVEL as hierarchy_level,
  SYS_CONNECT_BY_PATH(spec_id, '/') as ancestry_path
FROM source_spec_bom
START WITH parent_id IS NULL
CONNECT BY PRIOR spec_id = parent_id;

This gives you the full hierarchy with levels and paths. Migrate in level order (root first, then children).

Schema Differences Resolution: The target schema needs proper foreign key relationships. Map your flat structure to hierarchical using:

INSERT INTO target_spec_structure
  (spec_id, parent_spec_id, hierarchy_path)
SELECT s.spec_id, s.parent_id, h.ancestry_path
FROM spec_hierarchy_stage h
JOIN source_specs s ON h.spec_id = s.spec_id
ORDER BY h.hierarchy_level;

The ORDER BY ensures parents exist before children are inserted, preventing constraint violations.

Migration Validation: Implement comprehensive validation:

  1. Pre-migration: Detect circular references and orphans in source
  2. During migration: Validate each level before proceeding to next
  3. Post-migration: Verify complete ancestry chains and BOM counts match

For your 847 orphaned items, run this diagnostic:

SELECT spec_id, parent_id, spec_name
FROM source_spec_bom
WHERE parent_id NOT IN
  (SELECT spec_id FROM source_spec_bom)
AND parent_id IS NOT NULL;

These are truly orphaned - their parents don’t exist. You need to either create placeholder parents or reassign them to valid parents before migration.

Using this three-phase approach (staging, level-ordered migration, validation), we successfully migrated 45,000 spec items with zero hierarchy errors. The key is never inserting a child before its parent exists in the target.

This draft is based on general Windchill knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

Schema differences in BOM structures are tricky. You need to build a temporary mapping table that reconstructs the full hierarchy before inserting into the target. Don’t try to migrate parent-child relationships in a single pass - you’ll always hit constraint violations. Build the hierarchy in stages, starting with root nodes and working down levels.

4

That makes sense. So essentially a three-stage approach: first migrate all spec items without relationships, then build the hierarchy mapping, then update parent-child links? The challenge is identifying root nodes when the source schema doesn’t explicitly mark them. Any suggestions for detecting root nodes in a flat structure?

7

Root nodes are typically items where parent_id is NULL or references themselves. But in spec management, you also need to consider specification assemblies versus components. Check if your source has a spec_type or item_level column that distinguishes assembly-level specs from component-level ones. That’ll help you identify the hierarchy starting points correctly.

8

For migration validation, don’t just count orphaned records - validate the complete ancestry chain for each spec item. Write a recursive query that walks up the parent chain to ensure every item eventually reaches a root node. This catches circular references and broken chains that simple orphan detection misses. We found 200+ circular references in our last migration that would have corrupted the target database.

9

Also consider using Windchill Loader with custom transformation rules instead of direct SQL migration. The Loader API handles BOM hierarchy validation automatically and can detect schema mismatches before committing data. It’s slower but much safer for complex hierarchies.