SQLite Foreign Key Mismatch Due to Missing Primary Key Constraint

Issue Overview: Accidental Omission of Primary Key Constraint Causing Foreign Key Enforcement Failures

The core problem arises when a table is created with an invalid column constraint syntax that SQLite silently accepts, leading to unintended consequences in foreign key relationships. In this scenario, a developer attempted to declare a column as a primary key using the syntax fld int master key instead of fld int primary key. SQLite did not reject the table creation, but subsequent foreign key references to this column failed with "mismatch keys" errors during insert operations.

Key Technical Components Involved

1. Column Definition Parsing in SQLite: SQLite interprets everything between the column name and the first valid constraint keyword as part of the column’s declared type. The parser does not validate whether the text resembles a known constraint unless explicitly defined.
2. Type Affinity System: SQLite uses flexible type affinity rules where strings like "int master key" are resolved to INTEGER affinity.
3. Foreign Key Enforcement: When a foreign key constraint references a column that is not a PRIMARY KEY or UNIQUE constraint, SQLite will allow the reference but enforce strict value matching. However, mismatches occur if the referenced column lacks uniqueness guarantees.

Failure Mechanism Breakdown

• The typo master key was parsed as part of the column’s declared type rather than a constraint.
• The absence of an explicit PRIMARY KEY or UNIQUE constraint on fld allowed duplicate values in the column.
• Foreign key references to this non-unique column caused insertion failures because SQLite could not guarantee referential integrity.

Possible Causes: Flexible Parsing Rules and Silent Constraint Omission

1. Column Definition Ambiguity in Non-STRICT Tables

SQLite’s parsing rules for non-STRICT tables allow arbitrary text to follow the column name. For example:

CREATE TABLE tbl (
  col1 text fluffy bunny,  -- "text fluffy bunny" is the declared type
  col2 integer primary key -- explicit constraint
);

Here, "fluffy bunny" is treated as part of the type declaration. Constraints like primary key, not null, or unique must appear after the declared type. Since "master key" does not match any valid constraint keyword, it becomes part of the type declaration.

2. Missing Strict Mode Enforcement

When a table is declared without STRICT, SQLite does not validate whether the declared type corresponds to a known type. This allows unconventional type names but hides syntax errors. In STRICT mode:

CREATE TABLE tbl (fld int master key) STRICT; -- Fails: "unknown datatype"

The strict table definition forces validation of the declared type against SQLite’s recognized types (INTEGER, TEXT, etc.), catching the typo immediately.

3. Foreign Key Referential Integrity Requirements

Foreign key constraints require the referenced column to be part of a PRIMARY KEY or UNIQUE constraint. If the parent column lacks these constraints:

• Inserts into the child table will fail unless the value exists exactly once in the parent column.
• Updates or deletions on the parent column will not cascade properly.

In the original scenario, the foreign key reference assumed fld was a primary key, but its absence caused strict value matching, leading to insertion failures even when values existed.

Troubleshooting Steps, Solutions & Fixes

Step 1: Diagnose Table Schema and Constraint Definitions

Action: Use .schema or PRAGMA table_info() to inspect the table structure:

sqlite> .schema tblb
CREATE TABLE tblb (fld int master key);

The output reveals that fld has no PRIMARY KEY constraint. The "int master key" is parsed as the column’s declared type with INTEGER affinity.

Solution: Correct the column definition:

CREATE TABLE tblb (fld INTEGER PRIMARY KEY);

Step 2: Validate Foreign Key Configuration

Action: Ensure foreign key enforcement is enabled and verify constraints:

PRAGMA foreign_keys = ON; -- Enable enforcement
PRAGMA foreign_key_check; -- Check existing violations

If the child table references tblb(fld), recreate the foreign key after correcting the parent table:

CREATE TABLE child (
  id INTEGER,
  fld_ref INTEGER,
  FOREIGN KEY (fld_ref) REFERENCES tblb(fld)
);

Note: This will still fail unless tblb.fld has a uniqueness constraint. Always reference PRIMARY KEY or UNIQUE columns.

Step 3: Implement Strict Table Definitions

Action: Use STRICT tables to prevent ambiguous type declarations:

CREATE TABLE tblb (fld INTEGER PRIMARY KEY) STRICT;

If the original typo is attempted in strict mode:

CREATE TABLE tblb (fld int master key) STRICT; -- Error: unknown datatype

Best Practice: Adopt STRICT tables for new schemas to catch typos early. For existing databases, migrate tables to strict mode cautiously.

Step 4: Correct Data Type Affinity Misalignments

Action: If non-strict tables have columns with unintended type affinities, alter the schema:

-- Create new table with correct constraints
CREATE TABLE new_tblb (fld INTEGER PRIMARY KEY);
-- Copy data
INSERT INTO new_tblb SELECT fld FROM tblb;
-- Drop old table and rename
DROP TABLE tblb;
ALTER TABLE new_tblb RENAME TO tblb;

Caution: Use transactions to prevent data loss during schema changes.

Step 5: Audit Foreign Key Relationships

Action: Use PRAGMA foreign_key_list(child_table) to list all foreign keys and their referenced columns:

PRAGMA foreign_key_list(child);

Ensure every foreign key references a PRIMARY KEY or UNIQUE column. If not, redefine the parent table or adjust the foreign key.

Step 6: Handle Data Inconsistencies

Scenario: Existing data in the parent table has duplicates in fld, causing foreign key mismatches.
Action: Clean up duplicates before reapplying constraints:

-- Identify duplicates
SELECT fld, COUNT(*) FROM tblb GROUP BY fld HAVING COUNT(*) > 1;
-- Delete duplicates (example using CTE)
WITH duplicates AS (
  SELECT rowid, ROW_NUMBER() OVER (PARTITION BY fld) AS rn
  FROM tblb
)
DELETE FROM tblb WHERE rowid IN (SELECT rowid FROM duplicates WHERE rn > 1);

After deduplication, add a UNIQUE constraint to prevent future duplicates:

CREATE TABLE new_tblb (fld INTEGER UNIQUE);
INSERT INTO new_tblb SELECT fld FROM tblb;
DROP TABLE tblb;
ALTER TABLE new_tblb RENAME TO tblb;

Step 7: Utilize PRAGMA Functions for Schema Validation

Action: Combine PRAGMA table_info, PRAGMA foreign_key_list, and PRAGMA integrity_check to validate the entire database:

-- Check table structure
PRAGMA table_info(tblb);
-- Verify foreign keys
PRAGMA foreign_key_check;
-- Full database integrity check
PRAGMA quick_check;

Outcome: These commands identify missing constraints, foreign key violations, and structural inconsistencies.

Step 8: Adopt Migration Strategies for Legacy Tables

Scenario: Existing tables cannot be recreated as STRICT due to dependencies.
Action: Use CHECK constraints and triggers to emulate strictness:

-- Add CHECK constraint for integer type
ALTER TABLE tblb ADD CHECK (TYPEOF(fld) = 'integer');
-- Create trigger to enforce primary key uniqueness
CREATE TRIGGER tblb_pk_check 
BEFORE INSERT ON tblb 
WHEN EXISTS (SELECT 1 FROM tblb WHERE fld = NEW.fld)
BEGIN
  SELECT RAISE(ABORT, 'Duplicate primary key');
END;

Trade-off: Triggers add overhead but provide runtime enforcement for non-strict tables.

Step 9: Educate Teams on SQLite’s Typing Nuances

Action: Conduct training sessions highlighting:

• SQLite’s type affinity vs. rigid type systems.
• Importance of explicit constraints (PRIMARY KEY, UNIQUE).
• Differences between STRICT and non-STRICT tables.
• Foreign key dependency requirements.

Documentation: Maintain internal guidelines with examples of common pitfalls and corrective actions.

Step 10: Implement CI/CD Checks for Schema Changes

Action: Integrate schema validation into deployment pipelines using tools like sqlite3 and sqlglot:

# Example script to validate primary keys
if ! sqlite3 db.sqlite ".schema tblb" | grep -q "PRIMARY KEY"; then
  echo "Error: tblb lacks primary key"
  exit 1
fi

Outcome: Automated checks prevent typos from reaching production databases.

Final Recommendations

1. Always Use Explicit Constraints: Never rely on column names or types to imply keys.
2. Prefer STRICT Tables: Unless backward compatibility is required.
3. Enable Foreign Keys Early: Set PRAGMA foreign_keys = ON at connection startup.
4. Regular Schema Audits: Use PRAGMA functions and automated tools to detect anomalies.
5. Comprehensive Testing: Validate schema changes against real-world data scenarios before deployment.