INSERT not working when foreign_keys on

Question

I am trying to add information to a database and one of the details is a foreign key, but when I turn foreign keys on, nothing is inserted. When foreign keys are off all but the foreign key details are added.

def add_to_database(self, details):
    query = QSqlQuery()
    query.exec_("""PRAGMA foreign_keys = ON""")
    query.prepare("""insert into Product (Quantity, ProductStatus, Price, ProductTypeID) values
                      (?,?,?,?)""")
    query.addBindValue(details["quantity"])
    query.addBindValue(details["product_status"])
    query.addBindValue(details["price"])
    query.addBindValue(self.product_type_id)
    query.exec_()

Answer 1

Possible causes that your insertion may violate the foreign key constraint (e.g., is there an entry in, say, the ProductType table that has primary key entry "ProductTypeID").

A quick way to try to diagnose is to check the status return from query.exec_(). For most applications, it is good to test this always. It should return true if the query executed without error. You can quickly wrap your exec_:

if not query.exec_():
  print query.lastError()

query.lastError() will return a QSqlError type, so you can format the error type, text, etc. as needed. This error text is sometimes useful.

For myself, I discovered that I was having an issue with the SQLite data types that I was using, which caused prepare/bind calls to malfunction when I turned on foreign key restrictions. In particular, I used "int" as the data type for some primary/foreign keys, when QSQLITE wanted "integer."