SQLite3 FOREIGN KEY constraint failed

Q&A
,
#1

My Code:

SQL helper

db = 'practice.db'

def run_query(q):
    with sqlite3.connect(db) as conn:
        return pd.read_sql(q,conn)
    
def run_command(c):
    with sqlite3.connect(db) as conn:
        # By default, SQLite doesn't enforce foreign key relationships. 
        # To ensure the integrity of our data we need to 
        # enforce Foreign Key Constraints within SQLite
        conn.execute('PRAGMA foreign_keys = ON;')
        # Enforcing auto-commit of changes to the database
        conn.isolation_level = None
        conn.execute(c)

Table 1

c1='''
CREATE TABLE IF NOT EXISTS customer(
customer_id VARCHAR PRIMARY KEY,
gender TEXT,
dob REAL, 
status INTEGER,
verified INTEGER,
language TEXT,
created_at TEXT,
updated_at TEXT
);
'''

c2='''
INSERT OR IGNORE INTO customer
SELECT 
    akeed_customer_id,
    gender,
    dob,
    status,
    verified,
    language,
    created_at,
    updated_at
FROM customers;
'''

q='''
SELECT * FROM customer 
LIMIT 5;
'''

run_command(c1)
run_command(c2)
run_query(q)

Table 2

c1='''
CREATE TABLE IF NOT EXISTS location(
    location_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id TEXT,
    location_number INTEGER,
    location_type TEXT,
    latitude REAL, 
    longitude REAL,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
'''

run_command(c1)

c2='''
INSERT OR IGNORE INTO location(customer_id,location_number,location_type,latitude,longitude)
SELECT 
    customer_id,
    location_number,
    location_type,
    latitude, 
    longitude
FROM locations;
'''

run_command(c2)

What I expected to happen:
well, I hoped for 2 tables that related to each other through foreign keys

What actually happened:
When running the ‘Table 2’ code, I get the following error

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
<ipython-input-11-9b9dd17d8385> in <module>
     24 '''
     25 
---> 26 run_command(c2)
     27 
     28 

<ipython-input-3-7a9de4c47a6c> in run_command(c)
     16         # Enforcing auto-commit of changes to the database
     17         conn.isolation_level = None
---> 18         conn.execute(c)
     19 
     20 def show_tables():

IntegrityError: FOREIGN KEY constraint failed

Any ideas how I can fix this?

#2

I have found that troubleshooting foreign key constraint errors with sqlite to be troublesome, particularly on large data sets. However the following approach helped me identify the offending relation.

  1. disable foreign key checking: PRAGMA foreign_keys = 0;
  2. execute the statement that cause the error - in my case it was an INSERT of 70,000 rows with 3 different foreign key relations.
  3. re-enable foreign key checking: PRAGMA foreign_keys = 1;
  4. identify the foreign key errors: PRAGMA foreign_key_check(table-name); ( In my case, table-name = location)

I found I few thousand rows which contained invalid references.