SQLite – Troubleshooting Unexpected Foreign Key Behavior

foreign keypythonsqlite

I want to use Sqlite3 with Python and created queries as follows:

import sqlite3
from sqlite3 import Error

db = sqlite3.connect("temp.sqlite")
cursor = db.cursor()

cursor.execute("PRAGMA foreign_keys = ON") #Turns foreign_key support on

cursor.execute("""DROP TABLE IF EXISTS tx""")
cursor.execute("""CREATE TABLE tx (
                    tx_index BIGINT NOT NULL PRIMARY KEY,
                    hash CHAR(64) NOT NULL,
                    rbf INT NOT NULL, 
                    tx_in_value BIGINT NOT NULL,
                    tx_out_value BIGINT NOT NULL
                    );""")

cursor.execute("""INSERT INTO tx VALUES ('1','2','3','11','11')""")
cursor.execute("""INSERT INTO tx VALUES ('2','2','3','11','11')""")

cursor.execute("""DROP TABLE IF EXISTS txin""")
cursor.execute("""CREATE TABLE txin (
                    addr BIGINT NOT NULL,
                    value BIGINT NOT NULL,
                    tx_index BIGINT NOT NULL,
                    FOREIGN KEY (tx_index) REFERENCES tx (tx_index)
                    );""")

list_tuple = []
list_tuple.append(('11','12','1'))
list_tuple.append(('12','12','2'))
# Uncommenting the line below will throw an error
#list_tuple.append(('12','12','3'))

cursor.executemany("""INSERT INTO txin VALUES (?,?,?)""", list_tuple)

cursor.execute("""SELECT * FROM tx""")
cursor.execute("""SELECT * FROM txin""")

db.commit()
db.close()

The above queries, when tested without Python here, works fine but in the Python, execution gives following error:

cursor.execute("INSERT INTO txin VALUES (?,?,?)", insert_trx_in)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

It is simpler version of the problem. In real problem, I insert using executemany Any idea?

Best Answer

I believe the error was due to the mismatch in the foreign keys that cursor.executemany was trying to insert. If the foreign key to be inserted in TABLE2 is not present in TABLE1 (primary key holder), it throws an error.

It means that if TABLE 1 has 2 values in the primary key column then while inserting values in TABLE2, the values for the foreign key must exactly match to one of the primary key values in the TABLE1.

Related Question