Foreign Key Constraint Mismatch Blocking Inserts in SQLite

Issue Overview: Foreign Key Constraints and Unique Index Requirements

The core issue arises when attempting to insert data into tables truey or falsey after creating table ed, resulting in the error: "foreign key mismatch – ‘ed’ referencing ‘hc’ (1)". This occurs due to a violation of SQLite’s foreign key constraint rules when referencing non-unique columns in the parent table. The problem is rooted in the relationship between three components:

1. Table hc: Acts as a shared registry for primary keys from truey and falsey via triggers. It initially lacks a unique constraint on columns pc1 and pc2.
2. Table ed: Created with a foreign key constraint experiment that references hc(pc1, pc2).
3. Foreign Key Enforcement: SQLite requires referenced columns in parent tables to either be part of the primary key or have a unique index/constraint.

The absence of a unique constraint on hc(pc1, pc2) violates SQLite’s foreign key integrity checks. When ed is created with a foreign key pointing to these columns, SQLite validates the relationship and discovers that hc(pc1, pc2) does not guarantee uniqueness. This mismatch blocks subsequent insertions into truey or falsey because the triggers attempt to write to hc, which is now subject to stricter foreign key enforcement from ed.

Possible Causes: Misconfigured Constraints and Schema Design Flaws

1. Missing Unique Constraint on Referenced Columns

• Parent Table (hc) Design: Columns pc1 and pc2 in hc were not declared as unique or part of the primary key. Foreign key constraints require the referenced columns to uniquely identify rows in the parent table.
• Consequence: The foreign key experiment in ed cannot enforce referential integrity because multiple rows in hc could have identical pc1 and pc2 values, leading to ambiguity.

2. Incorrect Primary Key Syntax in Table ed

• AUTOINCREMENT Misuse: The original ed table definition used primary key (pk autoincrement) instead of declaring pk as INTEGER PRIMARY KEY AUTOINCREMENT. While this does not directly cause the foreign key error, it reflects a schema design anti-pattern that can lead to unintended behavior in other scenarios.

3. Trigger Logic Assumptions

• Implicit Uniqueness: The triggers on truey and falsey assume that NEW.pk values will always be unique across both tables. However, autoincrement counters are table-specific, meaning truey.pk and falsey.pk can produce overlapping values unless explicitly coordinated.

4. Boolean Type Handling

• Type Affinity: SQLite does not enforce strict type checking, so BOOL is treated as INTEGER. While using 0 and 1 is valid, this design choice should be documented to avoid confusion.

Troubleshooting Steps, Solutions & Fixes

Step 1: Add Unique Constraint to Parent Table

Modify the hc table to include a unique constraint on pc1 and pc2:

CREATE TABLE hc (
  pk INTEGER PRIMARY KEY AUTOINCREMENT,
  pc1 BOOL NOT NULL,
  pc2 INT NOT NULL,
  UNIQUE (pc1, pc2)  -- Critical addition
);

This ensures that every combination of pc1 and pc2 is unique, satisfying SQLite’s foreign key requirements.

Step 2: Correct Primary Key Definition in ed

Redefine the ed table with proper syntax for autoincrement:

CREATE TABLE ed (
  pk INTEGER PRIMARY KEY AUTOINCREMENT,
  pc1 BOOL NOT NULL,
  pc2 INT NOT NULL,
  CONSTRAINT experiment FOREIGN KEY (pc1, pc2) REFERENCES hc (pc1, pc2),
  CONSTRAINT x UNIQUE (pc1, pc2)
);

The AUTOINCREMENT keyword is now correctly applied to the pk column, avoiding potential issues with rowid management.

Step 3: Validate Trigger Logic for Key Uniqueness

Ensure that the triggers on truey and falsey generate globally unique pc2 values. Since truey and falsey use separate autoincrement counters, consider using a single sequence or a combined key strategy if absolute uniqueness across both tables is required.

Step 4: Test Insertions After Schema Changes

After modifying hc and ed, test insertions into truey and falsey:

INSERT INTO truey (x) VALUES ('bye');
INSERT INTO falsey (x) VALUES ('bye');

Verify that the hc table is updated correctly and that no foreign key errors occur.

Step 5: Monitor for Race Conditions (Advanced)

In high-concurrency environments, ensure that the autoincrement counters and triggers do not introduce race conditions. Use transactions to maintain atomicity when inserting into truey/falsey and their associated hc entries.

Alternative Approach: Single Source of Truth for Primary Keys

If the goal is to maintain a shared pool of primary keys across truey and falsey, consider using a single table for key generation:

CREATE TABLE key_pool (
  pk INTEGER PRIMARY KEY AUTOINCREMENT,
  source_table BOOL NOT NULL  -- 1 for truey, 0 for falsey
);

CREATE TABLE truey (
  pk INTEGER PRIMARY KEY,
  x INT,
  FOREIGN KEY (pk) REFERENCES key_pool(pk)
);

CREATE TABLE falsey (
  pk INTEGER PRIMARY KEY,
  x INT,
  FOREIGN KEY (pk) REFERENCES key_pool(pk)
);

Insert into key_pool first, then use last_insert_rowid() to populate truey or falsey. This approach eliminates the need for triggers and ensures global uniqueness.

Final Verification Checklist

1. Confirm hc has unique constraint on (pc1, pc2)
2. Validate foreign key syntax in ed
3. Test insertion workflow with debug output:

PRAGMA foreign_keys = ON;
INSERT INTO falsey (x) VALUES ('test');
SELECT * FROM hc;

4. Check SQLite error logs for constraint violations
5. Use .schema command to verify table definitions

By addressing the unique constraint requirement in the parent table and ensuring proper schema design, the foreign key mismatch error is resolved while maintaining the intended key pooling functionality.