Foreign Key Mismatch Due to Non-Unique Referenced Column in SQLite

Invalid Foreign Key Reference to Non-Primary Key Column

The core issue arises when attempting to create a foreign key relationship between a column in a child table and a column in a parent table that is neither a primary key nor part of a unique constraint. In the provided schema, Table 3 ("testsheet_pe_3_swgrParams" or simplified "3") attempts to reference the "plants" column from Table 2 ("testsheet_pe_2_swgrDetails" or "2"), which is not a primary key or uniquely constrained. SQLite enforces strict rules for foreign key relationships: the referenced column(s) in the parent table must be a primary key or have a unique index. When this condition is violated, insert/update operations in the child table fail with a "foreign key mismatch" error, as seen when trying to link "pine" from Table 2’s "plants" column to Table 3.

This problem is compounded by the lack of indices on the referenced columns, which SQLite requires for efficient foreign key constraint enforcement. Additionally, the use of non-integer primary keys (e.g., TEXT) without explicit unique guarantees introduces risks of data duplication and referential integrity failures. The schema design further complicates matters by creating multi-generational dependencies (e.g., Table 3 referencing both Table 1 and Table 2) without ensuring that intermediate tables enforce uniqueness on the columns being referenced downstream. For instance, Table 2’s "fdr_no" is referenced by Table 3 but lacks a unique constraint, making it impossible for SQLite to guarantee a one-to-many or one-to-one relationship.

Causes of Foreign Key Mismatch and Constraint Failures

1. Referencing Non-Unique Columns in Parent Tables
The most immediate cause is the attempt to create a foreign key constraint against a column that is not a primary key or uniquely constrained. In Table 2, "plants" and "fdr_no" are ordinary TEXT columns with no uniqueness guarantees. SQLite cannot enforce referential integrity for such columns because multiple rows in Table 2 could have the same "plants" or "fdr_no" value, leading to ambiguity about which parent row a child row should reference. For example, if Table 2 contains two rows with "plants" = "pine", Table 3 cannot definitively link to either without violating uniqueness.

2. Missing Indices on Referenced Columns
Even if a column is uniquely constrained, SQLite requires an index on the referenced column(s) to efficiently validate foreign key constraints. Without an index, every insert/update in the child table would trigger a full table scan in the parent table, which is prohibitively slow. While SQLite automatically creates indices for primary keys, it does not do so for unique constraints unless explicitly defined. In the original schema, neither "plants" in Table 2 nor "fdr_no" in "testsheet_pe_2_swgrDetails" have indices, leading to potential performance issues and constraint validation failures.

3. Data Type and Schema Design Inconsistencies
Using TEXT for primary keys (e.g., "test_id") without rigorous validation opens the door to case sensitivity issues, trailing spaces, or typographical errors that break referential integrity. For instance, "ABC" and "abc" would be treated as distinct values in SQLite unless a collation sequence is specified. Additionally, the schema’s reliance on composite keys (e.g., "test_id" + "fdr_no") without formalizing them as composite unique constraints creates gaps in integrity enforcement. The lack of cascading update/delete actions in some foreign key definitions further risks orphaned rows when parent data is modified.

Resolving Foreign Key Mismatches and Ensuring Referential Integrity

Step 1: Redesign the Schema with Proper Unique Constraints
Modify Table 2 ("testsheet_pe_2_swgrDetails") to include a unique constraint on "fdr_no" if it is intended to be a unique identifier, or redefine the primary key to include both "test_id" and "fdr_no" for composite uniqueness. For example:

CREATE TABLE testsheet_pe_2_swgrDetails (
  no INTEGER,
  test_id TEXT NOT NULL,
  fdr_no TEXT NOT NULL,
  FOREIGN KEY (test_id) REFERENCES testsheet_pe_1_main(test_id) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  UNIQUE (fdr_no, test_id)  -- Composite unique constraint
);

This ensures that the combination of "fdr_no" and "test_id" is unique, allowing Table 3 to reference both columns as a composite foreign key:

CREATE TABLE testsheet_pe_3_swgrParams (
  test_id TEXT,
  fdr_no TEXT,
  comp TEXT,
  tmp_min TEXT,
  FOREIGN KEY (fdr_no, test_id) REFERENCES testsheet_pe_2_swgrDetails(fdr_no, test_id),
  FOREIGN KEY (test_id) REFERENCES testsheet_pe_1_main(test_id)
);

Step 2: Add Explicit Indices on Referenced Columns
Create indices on all columns involved in foreign key relationships to optimize constraint checks:

CREATE INDEX idx_swgr_details_fdr_test ON testsheet_pe_2_swgrDetails(fdr_no, test_id);
CREATE INDEX idx_main_test_id ON testsheet_pe_1_main(test_id);

For the simplified schema, add an index on Table 2’s "plants" if it must remain a foreign key target (though this is not recommended unless "plants" is made unique):

CREATE UNIQUE INDEX idx_2_plants ON "2"(plants);

Step 3: Validate Data Types and Cascading Actions
Ensure that referenced columns have compatible data types. For instance, if "test_id" in Table 1 is TEXT, the corresponding foreign keys in Tables 2 and 3 must also be TEXT. Implement cascading updates/deletes where appropriate to maintain integrity:

-- Modify Table 3’s foreign key to include cascading actions
CREATE TABLE testsheet_pe_3_swgrParams (
  ...
  FOREIGN KEY (fdr_no, test_id) REFERENCES testsheet_pe_2_swgrDetails(fdr_no, test_id)
    ON UPDATE CASCADE ON DELETE CASCADE
);

Step 4: Use Meaningful Table and Column Names
Replace numeric table names ("1", "2", "3") with descriptive ones (e.g., "main_tests", "swgr_details", "swgr_params") to improve readability and maintainability. Similarly, avoid generic column names like "id" in favor of context-specific names like "test_id" or "fdr_no".

Step 5: Test Constraints with Sample Data
After modifying the schema, validate the foreign key relationships by inserting test data:

-- Insert into parent table
INSERT INTO testsheet_pe_1_main (test_id, pe_name) VALUES ('test1', 'Project Alpha');

-- Insert into child table
INSERT INTO testsheet_pe_2_swgrDetails (test_id, fdr_no) VALUES ('test1', 'fdr123');

-- Insert into grandchild table (should succeed)
INSERT INTO testsheet_pe_3_swgrParams (test_id, fdr_no, comp) VALUES ('test1', 'fdr123', 'Component A');

-- Attempt invalid insert (should fail)
INSERT INTO testsheet_pe_3_swgrParams (test_id, fdr_no, comp) VALUES ('test1', 'invalid_fdr', 'Component B');

Use SQLite’s PRAGMA foreign_key_check to identify lingering integrity issues.

By addressing these areas, the foreign key mismatch error is resolved, and the schema enforces referential integrity effectively.