SQLite3 foreign key mismatch

Question

I have following SQLite3 statement inside Qt/C++ class method:

bool UeSettings::ueSaveSettings(const QString& address,
                                const QString& port,
                                const QString& dbName,
                                const QString& dbUsername,
                                const QString& dbPassword,
                                const QString& hostName,
                                const QString& autoStockUpdate,
                                const QString& topN,
                                const QString& autoLogoffWorkerAtBill/*,
                                UeTypePrintersList* const printersList*/)
{
    bool result=false;
    bool connected=false;

    if(this->ueDatabase().isOpen())
    {
        connected=true;
    }
    else
    {
        if(this->ueConnectToDatabase())
        {
            connected=this->ueDatabase().open();
        }
        else
        {
            qDebug() << Q_FUNC_INFO
                     << this->ueDatabase().lastError().text();
        }   // if
    }   // if

    if(connected)
    {
        QSqlQuery querySaveSettings(UeApplicationSettings::UeSqlQueries::QUERY_SAVE_SETTINGS.arg(address)
                                                                                            .arg(port)
                                                                                            .arg(dbName)
                                                                                            .arg(dbUsername)
                                                                                            .arg(dbPassword)
                                                                                            .arg(hostName)
                                                                                            .arg(autoStockUpdate)
                                                                                            .arg(topN)
                                                                                            .arg(QDateTime::currentDateTime().toString())
                                                                                            .arg(autoLogoffWorkerAtBill),
                                    this->ueDatabase());


        if(querySaveSettings.lastError().isValid())
        {
            qDebug() << Q_FUNC_INFO
                     << querySaveSettings.lastError().text();
        }
        else
        {
            this->ueSetServerAddress(address);
            this->ueSetServerPort(port);
            this->ueSetDatabaseName(dbName);
            this->ueSetDatabaseUsername(dbUsername);
            this->ueSetDatabasePassword(dbPassword);
            this->ueSetHostname(hostName);
            this->ueSetAutomaticStockUpdateFromFirstWarehouse(autoStockUpdate=="1"?true:false);
            this->ueSetNumberOfTopSalesProductsShown(topN);
            this->ueSetAutoLogoffWorkerAtBill(autoLogoffWorkerAtBill=="1"?true:false);

            for(int printerIndex=0; printerIndex<this->uePrintersModel()->uePrintersList()->size(); printerIndex++)
            {
                QString querySavePrintersCommand=UeApplicationSettings::UeSqlQueries::QUERY_SAVE_PRINTERS.arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->uePrinterName())
                                                                                                         .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->uePrinterAddress())
                                                                                                         .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->uePrinterLocation())
                                                                                                         .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->ueIsKitchenPrinter())
                                                                                                         .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->ueIsReceiptPrinter())
                                                                                                         .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->ueIsReportPrinter());
                QSqlQuery querySavePrinters(querySavePrintersCommand,
                                            this->ueDatabase());
                if(querySavePrinters.lastError().isValid())
                {
                    qDebug() << Q_FUNC_INFO
                             << querySavePrinters.lastQuery()
                             << querySavePrinters.lastError().text();
                }
                else
                {
                    result=true;
                }   // if
            }   // for

            emit this->ueSignalSettingsSaved();
        }   // if
    }   // if

    return result;
}   // ueSaveSettings

Now, I want to save printer(s) settings into SQLITE3 database, in table named 'PRINTERS' this task should be executed in following statement of upper code:

for(int printerIndex=0; printerIndex<this->uePrintersModel()->uePrintersList()->size(); printerIndex++)
{
    QString querySavePrintersCommand=UeApplicationSettings::UeSqlQueries::QUERY_SAVE_PRINTERS.arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->uePrinterName())
                                                                                             .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->uePrinterAddress())
                                                                                             .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->uePrinterLocation())
                                                                                             .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->ueIsKitchenPrinter())
                                                                                             .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->ueIsReceiptPrinter())
                                                                                             .arg(this->uePrintersModel()->uePrintersList()->at(printerIndex)->ueIsReportPrinter());
    QSqlQuery querySavePrinters(querySavePrintersCommand,
                                this->ueDatabase());
    if(querySavePrinters.lastError().isValid())
    {
        qDebug() << Q_FUNC_INFO
                 << querySavePrinters.lastQuery()
                 << querySavePrinters.lastError().text();
    }
    else
    {
        result=true;
    }   // if
}   // for

Instead of saving printer data to database, I get following qDebug() output - SQLITE3 error:

bool UeSettings::ueSaveSettings(const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&) "INSERT INTO PRINTERS (ID, NAME, ADDRESS, LOCATION, ISKITCHEN, ISRECEIPT, ISREPORT) VALUES (last_insert_rowid(), \"Canon-MP280\", \"\", \"\", \"0\", \"0\", \"0\");" "foreign key mismatch - \"PRINTERS\" referencing \"SETTINGS\" Unable to execute statement"
bool UeSettings::ueSaveSettings(const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&) "INSERT INTO PRINTERS (ID, NAME, ADDRESS, LOCATION, ISKITCHEN, ISRECEIPT, ISREPORT) VALUES (last_insert_rowid(), \"PDF\", \"\", \"\", \"0\", \"0\", \"0\");" "foreign key mismatch - \"PRINTERS\" referencing \"SETTINGS\" Unable to execute statement"
bool UeSettings::ueSaveSettings(const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&) "INSERT INTO PRINTERS (ID, NAME, ADDRESS, LOCATION, ISKITCHEN, ISRECEIPT, ISREPORT) VALUES (last_insert_rowid(), \"POS58\", \"\", \"\", \"1\", \"1\", \"0\");" "foreign key mismatch - \"PRINTERS\" referencing \"SETTINGS\" Unable to execute statement"
bool UeSettings::ueSaveSettings(const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&) "INSERT INTO PRINTERS (ID, NAME, ADDRESS, LOCATION, ISKITCHEN, ISRECEIPT, ISREPORT) VALUES (last_insert_rowid(), \"POS80\", \"\", \"\", \"0\", \"0\", \"0\");" "foreign key mismatch - \"PRINTERS\" referencing \"SETTINGS\" Unable to execute statement"
bool UeSettings::ueSaveSettings(const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&, const QString&) "INSERT INTO PRINTERS (ID, NAME, ADDRESS, LOCATION, ISKITCHEN, ISRECEIPT, ISREPORT) VALUES (last_insert_rowid(), \"TS-M210-087\", \"\", \"\", \"0\", \"0\", \"0\");" "foreign key mismatch - \"PRINTERS\" referencing \"SETTINGS\" Unable to execute statement"

Here is also settings database layout, I mean, with following script I create database at runtime (if it does not exists):

static const QString QUERY_CREATE_TABLE_SETTINGS="CREATE TABLE "+UeApplicationSettings::UeDatabase::SETTINGS_TABLE_NAME+
                                           " (ME_TIMESTAMP_REMOVED TEXT, ME_TIMESTAMP_ADDED TEXT, SETTINGS_ID INTEGER PRIMARY KEY, "
                                           "ST_AUTOUPDATE NUMERIC, GE_HOSTNAME TEXT, DB_SERVER_ADDRESS TEXT, DB_SERVER_PORT TEXT, "
                                           "DB_NAME TEXT, DB_USERNAME TEXT, DB_PASSWORD TEXT, GE_TOP_N TEXT,"
                                           "GE_AUTOLOGOFF_WORKER_AT_BILL INTEGER, PE_ID_PRINTERSETTINGS INTEGER); ";
static const QString QUERY_CREATE_TABLE_PRINTERS="CREATE TABLE "+UeApplicationSettings::UeDatabase::PRINTERS_TABLE_NAME+
                                           " (ID INTEGER PRIMARY KEY, NAME TEXT, ADDRESS TEXT, LOCATION TEXT, ISKITCHEN INTEGER, "
                                           "ISRECEIPT INTEGER, ISREPORT INTEGER, "
                                           "FOREIGN KEY(ID) REFERENCES "+UeApplicationSettings::UeDatabase::SETTINGS_TABLE_NAME+"(PE_ID_PRINTERSETTINGS));";

Why am I getting this error and how do I get rid of it?

P.S.: I wanted to post this question to general SO site, but I think it is database related since the code itself compiles just fine.

And while opening connection to it, I also issue (first) command to enable foreign key support:

static const QString QUERY_TURN_ON_FOREIGN_KEYS_SUPPORT="PRAGMA foreign_keys=ON;";

Answer 1

last_insert_rowid() returns the rowid of the most recently executed INSERT statement in the same connection, which is not what you want.

You have to use the ID of the setting that you want to refer to.

Also, as documented, the PE_ID_PRINTERSETTINGS column needs to be the primary key, or have a UNIQUE constraint.