Resolving PRIMARY KEY Constraint Failures on Composite Keys in SQLite

Composite Primary Key Constraint Failure Despite Perceived Unique Values

Understanding the Composite Key Constraint Error

The error SQLITE_CONSTRAINT_PRIMARYKEY occurs when an INSERT or UPDATE operation violates a primary key’s uniqueness requirement. For a composite primary key (e.g., (ID_FSTkurz, ID_Bef) in TBL2), the combination of values across all key columns must be unique. The error indicates that the database engine has detected a duplicate entry for the composite key.

While the user asserts that the composite key values are unique, the error persists. This discrepancy suggests an underlying issue that prevents the database from recognizing the uniqueness of the inserted values. Potential causes include hidden data inconsistencies, schema design oversights, or SQLite’s handling of data types and collations.

Root Causes of Uniqueness Validation Failures

1. Non-Unique Composite Key Values

   • Invisible Characters or Formatting Differences: Text values in ID_FSTkurz may contain trailing spaces, non-printable characters (e.g., tabs, line breaks), or case variations (e.g., 'ABC' vs. 'abc') that are not visually apparent.
   • Data Type Mismatches: Inserting a numeric string (e.g., '123') into ID_Bef (an INTEGER column) may implicitly convert it to an integer, inadvertently creating duplicates if another row has the same numeric value.
   • Incorrect Quoting: Using double quotes ("value") instead of single quotes ('value') for text values may cause SQLite to interpret the value as an identifier (e.g., a column name), leading to unexpected values being stored.

2. Index Corruption or Schema Misconfiguration

   • Corrupted Composite Key Index: While PRAGMA integrity_check reports no errors, rare edge cases may involve partial index corruption that evades detection.
   • Foreign Key Collation Mismatches: If TBL1.ID_FSTkurz uses a collation sequence (e.g., NOCASE) that differs from TBL2.ID_FSTkurz, foreign key validation may succeed while composite key uniqueness checks fail due to case sensitivity differences.

3. Application-Layer Issues

   • Transaction Isolation Conflicts: Concurrent writes to TBL2 in multi-threaded environments may result in race conditions where two transactions attempt to insert the same composite key.
   • Trigger Interference: A BEFORE INSERT trigger on TBL2 might modify the values of ID_FSTkurz or ID_Bef, inadvertently creating duplicates.

Diagnostic and Remediation Strategies

Step 1: Validate Existing Data for Duplicates
Execute a targeted query to confirm whether the composite key values already exist:

SELECT * FROM TBL2 
WHERE ID_FSTkurz = 'inserted_value' AND ID_Bef = inserted_number;

Replace 'inserted_value' and inserted_number with the exact values from the failing INSERT statement. If this returns a row, the composite key is duplicated.

Step 2: Inspect Data for Hidden Anomalies
Use the HEX() function to detect non-printable characters in ID_FSTkurz:

SELECT ID_FSTkurz, HEX(ID_FSTkurz) FROM TBL2;

Compare the hexadecimal representations of seemingly identical values to identify discrepancies.

Step 3: Verify Insert Statement Syntax
Ensure the INSERT statement uses single quotes for text values and no quotes for integers:

-- Correct:
INSERT INTO TBL2 (ID_FSTkurz, ID_Bef) VALUES ('abc', 123);

-- Incorrect (uses double quotes for text):
INSERT INTO TBL2 (ID_FSTkurz, ID_Bef) VALUES ("abc", 123);

Step 4: Rebuild Indexes
Force SQLite to rebuild indexes for TBL2:

REINDEX TBL2;

Step 5: Test with a Minimal Example
Reproduce the issue in an isolated environment:

-- Create tables
CREATE TABLE TBL1 (
    ID_FSTkurz TEXT PRIMARY KEY
);
CREATE TABLE TBL2 (
    ID_FSTkurz TEXT,
    ID_Bef INTEGER,
    PRIMARY KEY (ID_FSTkurz, ID_Bef),
    FOREIGN KEY (ID_FSTkurz) REFERENCES TBL1(ID_FSTkurz)
);

-- Insert sample data
INSERT INTO TBL1 VALUES ('test');
INSERT INTO TBL2 VALUES ('test', 1);

-- Attempt conflicting insert
INSERT INTO TBL2 VALUES ('test', 1);  -- Fails as expected

Step 6: Check Collation Sequences
Ensure collation consistency between TBL1 and TBL2:

-- Example: Explicitly define collation for TBL2
CREATE TABLE TBL2 (
    ID_FSTkurz TEXT COLLATE NOCASE,
    ID_Bef INTEGER,
    PRIMARY KEY (ID_FSTkurz, ID_Bef),
    FOREIGN KEY (ID_FSTkurz) REFERENCES TBL1(ID_FSTkurz)
);

Step 7: Audit Triggers and Foreign Key Constraints
List triggers associated with TBL2:

SELECT sql FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'TBL2';

Temporarily disable triggers to test if they affect inserts.

Final Fixes

• Use parameterized queries to avoid quoting and data type errors.
• Apply TRIM() to text inputs to remove leading/trailing spaces.
• Recreate the table with explicit collation rules if case sensitivity is a factor.

By systematically addressing these areas, the composite key constraint error can be resolved, ensuring valid inserts while maintaining referential integrity.