We’re migrating 500+ supplier records from our legacy ERP system into Mastercontrol Quality Excellence 2022.1 and hitting critical database errors during bulk import.
The import process fails with two types of errors:
ERROR: duplicate key value violates unique constraint "supplier_pkey"
DETAIL: Key (supplier_id)=(SUP-10234) already exists.
ERROR: insert or update on table "supplier_contacts" violates foreign key constraint
DETAIL: Key (country_code)=(UK) is not present in table "ref_countries".
We’ve verified our source data has unique supplier IDs, but the primary key conflicts suggest existing records. Additionally, country codes from our ERP don’t match Mastercontrol’s reference tables - we use ‘UK’ while the system expects ‘GB’.
How do we handle primary key uniqueness checks before import and validate foreign key references against the correct lookup tables? We need to synchronize our reference data properly to avoid these constraint violations during the migration.
For the country code issue specifically, I built a reusable mapping table that handles common variations. UK→GB, USA→US, etc. Store this in a custom reference table and use it in all imports. The key is maintaining referential integrity throughout the process. One trick: use MERGE statements instead of INSERT to handle the duplicate key scenario gracefully - it’ll update existing records and insert new ones in a single operation.
Check your import configuration settings in the Supplier Management module. There should be options for duplicate handling (skip, update, or error). For reference data, export Mastercontrol’s ref_countries table to see all valid codes, then create a mapping table in your ETL process. The UK/GB mismatch is exactly why you need this translation layer. Have you considered using the data import validation feature to catch these issues before the actual import runs?
Classic migration challenge! The duplicate key error means you likely have partial data already loaded from a previous import attempt. Before re-running, query the supplier table to identify existing records and either update them or exclude from your import file. For the foreign key issue, you need to map your legacy reference data to Mastercontrol’s controlled master data tables first - this is a critical pre-migration step that’s often overlooked.
The validation isn’t built into the standard import wizard - you’ll need to create a pre-import validation script. I typically write a SQL query that checks incoming data against all reference tables before the actual import. For your scenario, validate supplier_id uniqueness against existing records, and join your import staging table with ref_countries, ref_supplier_types, and any other lookup tables to identify mismatches. This catches issues without triggering constraint violations.
Don’t forget about the supplier status codes and supplier types - those are also foreign key references that commonly cause import failures. I’ve seen situations where the legacy system had ‘Active/Inactive’ but Mastercontrol uses ‘Approved/Pending/Rejected’. You need a comprehensive mapping document for ALL reference data fields, not just countries. Create a staging table approach where you load raw data first, run transformation scripts to map all foreign keys, then insert into production tables only after validation passes.
Here’s a comprehensive approach that addresses all three critical areas:
Primary Key Uniqueness:
Before import, run this validation query to identify conflicts:
SELECT i.supplier_id, s.supplier_name
FROM import_staging i
INNER JOIN suppliers s ON i.supplier_id = s.supplier_id;
Any results indicate duplicates. Decide whether to update existing records or assign new IDs.
Foreign Key Lookup Validation:
Create a comprehensive validation script that checks ALL foreign key references:
// Pseudocode - Pre-import validation steps:
1. Load source data into staging table (import_staging)
2. Validate country_code against ref_countries table
3. Validate supplier_type against ref_supplier_types table
4. Validate status_code against ref_status_codes table
5. Generate validation report with all mismatches
6. Apply mapping transformations for known variations
7. Flag records that require manual review
// See: MC Data Import Guide Section 7.3
Build a mapping table for common variations:
- Create custom_reference_mappings table with columns: source_system, field_name, source_value, target_value
- Populate with UK→GB, USA→US, Active→Approved, etc.
- Use this in your ETL transformation layer
Reference Data Synchronization:
The critical step everyone misses - synchronize reference data BEFORE importing transactional data:
- Export all Mastercontrol reference tables (ref_countries, ref_supplier_types, ref_status_codes)
- Compare with your legacy system’s lookup values
- Either extend Mastercontrol’s reference tables with your legacy values (if permitted) OR create the mapping table approach
- For country codes specifically, use ISO 3166-1 alpha-2 standard that Mastercontrol follows
- Document all mappings in your migration specification
For the actual import process, use a staged approach:
- Stage 1: Load to temporary staging table with NO constraints
- Stage 2: Run validation queries and apply transformations
- Stage 3: Insert/update production tables only after 100% validation success
This prevents partial imports that cause the duplicate key issues you’re experiencing. The staging approach also gives you a rollback point if something goes wrong.
One more critical point: check if Mastercontrol has any business rules or triggers on the supplier tables that might cause additional validation failures beyond the database constraints. These can include required field validations, format checks, or workflow initiations that aren’t obvious from the schema alone.
Thanks for the suggestions. We did run a test import last week that partially succeeded, so that explains the duplicate keys. I can clean those up. However, I’m not seeing a clear validation feature in the import wizard that would catch the foreign key mismatches before execution. Is this something in the Advanced Import Options, or do we need to run SQL queries manually against the reference tables first?