Resolving Foreign Key Mismatches and Duplicate Data During CSV Import into Related Tables

Incorrect Foreign Key References and Duplicate Data Propagation During Table Normalization

Core Problem: Mismatched Foreign Key Constraints and Non-Unique Data Population

The primary issue revolves around attempts to normalize imported CSV data into two related tables (Table2 and Table3) while maintaining referential integrity. The user’s goal is to split a source table ("test") containing name, surname, and location into two normalized tables:

• Table3 to store unique location values with auto-generated IDs.
• Table2 to store name and surname alongside a foreign key (table3_id) linking to Table3’s ID.

The process fails because:

1. The foreign key in Table2 incorrectly references itself instead of Table3.
2. Duplicate location entries in Table3 lead to redundant IDs, preventing a clean relationship.
3. The join condition during data insertion uses an undefined test.id column, causing incorrect mapping between Table2 and Table3.

This results in Table2 having the same number of rows as Table3 instead of reflecting the original count from the "test" table. The root cause is a combination of schema design flaws, improper data insertion logic, and missing uniqueness enforcement.

Structural Flaws in Schema Design and Data Insertion Logic

1. Incorrect Foreign Key Definition
The foreign key in Table2 is declared as FOREIGN KEY (table3_id) REFERENCES Table2(id), which creates a self-referential relationship instead of linking to Table3. This violates the intended normalization goal, as Table2’s table3_id should point to Table3’s primary key.

2. Missing Uniqueness Enforcement in Table3
The location column in Table3 is not declared as UNIQUE, allowing duplicate entries. When inserting data from the "test" table without deduplication, Table3 ends up with multiple rows for the same location, each with a unique ID. This makes it impossible to reliably associate Table2 entries with the correct location ID.

3. Invalid Join Condition During Data Insertion
The INSERT INTO Table2 statement uses test.id = Table3.id as the join condition. However, the original CSV (and thus the "test" table) lacks an id column. This implies either:

• The "test" table was created without a primary key, leading to implicit rowid usage.
• The join logic assumes a direct correlation between test.rowid and Table3.id, which is invalid unless locations are inserted in the exact order they appear in the CSV.

4. Failure to Map Duplicate Locations to a Single ID
Without deduplication in Table3, identical locations receive distinct IDs. When joining "test" and Table3 on location, multiple Table3 IDs match the same location, causing unpredictable table3_id values in Table2.

Correcting Schema Design, Enforcing Uniqueness, and Refining Insertion Logic

Step 1: Fix Foreign Key Constraints
Redefine Table2 to reference Table3’s id:

CREATE TABLE Table3 (
  id INTEGER PRIMARY KEY,
  location TEXT UNIQUE -- Enforce uniqueness
);

CREATE TABLE Table2 (
  id INTEGER PRIMARY KEY,
  name TEXT,
  surname TEXT,
  table3_id INTEGER,
  FOREIGN KEY (table3_id) REFERENCES Table3(id) -- Corrected reference
);

Adding UNIQUE to Table3.location ensures one ID per unique location.

Step 2: Populate Table3 with Distinct Locations
Use DISTINCT to avoid duplicates:

INSERT INTO Table3 (location)
SELECT DISTINCT location FROM test;

This guarantees that each location in Table3 has a single ID, even if duplicated in the source data.

Step 3: Insert Data into Table2 with a Location-Based Join
Join "test" with Table3 on location to map each name/surname to the correct table3_id:

INSERT INTO Table2 (name, surname, table3_id)
SELECT 
  test.name, 
  test.surname, 
  Table3.id 
FROM test
INNER JOIN Table3 ON test.location = Table3.location;

This ensures all rows in "test" are preserved in Table2, with table3_id correctly pointing to the unique ID from Table3.

Step 4: Verify Referential Integrity
After insertion, validate the relationship:

-- Check for orphaned entries in Table2
SELECT * FROM Table2 WHERE table3_id NOT IN (SELECT id FROM Table3);

-- Confirm expected row counts
SELECT COUNT(*) FROM test; -- Original count
SELECT COUNT(*) FROM Table2; -- Should match test's count
SELECT COUNT(*) FROM Table3; -- Should match number of unique locations

Step 5: Handle Edge Cases

• Case Sensitivity: If location values differ by case (e.g., "Paris" vs. "paris"), use COLLATE NOCASE in the join:

  INNER JOIN Table3 ON test.location COLLATE NOCASE = Table3.location COLLATE NOCASE
  

• Null Locations: If the CSV contains empty location fields, decide whether to allow NULL in Table3 or assign a default value.

Final Schema and Workflow Summary

1. Schema Design:
   • Table3 stores unique locations with enforced uniqueness.
   • Table2 references Table3 via a corrected foreign key.
2. Data Insertion:
   • Deduplicate locations before inserting into Table3.
   • Use location-based joins to ensure accurate table3_id mapping.

By addressing these issues, the normalized tables will maintain referential integrity while preserving all original data from the CSV.