I have been trying to solve this problem for some time now. I'm writing an application using Swift/SwiftUI and FMDB over SQlite3. When a delete is executed on a table where there are existing foreign keys I expect the delete to fail. It doesn't and there is no error thrown.
In XCode I'm linking with libsqlite3.tbd listed as date modified 03/16/2021. Inside the tbd file I find: install-name: '/usr/lib/libsqlite3.dylib' current-version: 321.3 compatibility-version: 9
I find the libsqlite3.dylib in: /System/Volumes/Data/Applications/Xcode.app/Contents/Developer/Platforms/iPhoneOS.platform/Library/Developer/CoreSimulator/Profiles/Runtimes/iOS.simruntime/Contents/Resources/RuntimeRoot/usr/lib Info shows it was created 04/09/2021. No apparent version
What is happening is that I have foreign keys on a table named BudgetedExpense to a table named BudgetDescription.
BudgetDescription DDL
CREATE TABLE budget_description (
id INTEGER DEFAULT (0)
PRIMARY KEY AUTOINCREMENT
UNIQUE,
income_expense_code INTEGER (8) NOT NULL
DEFAULT (0),
description STRING NOT NULL
DEFAULT (0),
income_or_expense INTEGER NOT NULL
DEFAULT (0)
);
BudgetedExpense DDL
CREATE TABLE budgeted_expense (
expense_id INTEGER NOT NULL
DEFAULT (0)
PRIMARY KEY AUTOINCREMENT
UNIQUE,
account_code INTEGER (8) NOT NULL
DEFAULT (0),
expense_code INTEGER (8) NOT NULL
DEFAULT (0),
budget_year INTEGER (4) NOT NULL
DEFAULT (0),
budget_month INTEGER (2) NOT NULL
DEFAULT (0),
expense_budget DECIMAL (12, 2) NOT NULL
DEFAULT (0),
expense_spent DECIMAL (12, 2) NOT NULL
DEFAULT (0),
unexpected_expense DECIMAL (12, 2) NOT NULL
DEFAULT (0),
category_code INTEGER (8) NOT NULL
DEFAULT (0),
hidden BOOLEAN NOT NULL
DEFAULT (0),
hidden_id INTEGER DEFAULT (0),
FOREIGN KEY (
account_code,
budget_year,
budget_month
)
REFERENCES budget (account_code,
budget_year,
budget_month),
FOREIGN KEY (
expense_code
)
REFERENCES budget_description (income_expense_code) ON DELETE RESTRICT,
FOREIGN KEY (
category_code
)
REFERENCES categories (category_code)
);
My FMDB code that executes the database updates is:
func updateDatabaseTable(query: String)-> Bool
{
var updateSuccessful: Bool = false
let pragmaQuery = "PRAGMA foreign_keys=ON;"
if openDatabase() {
do {
try database.executeUpdate(pragmaQuery, values: nil)
} catch {
}
queue.inTransaction() {
database, rollback in
do {
try database.executeUpdate(query, values: nil)
updateSuccessful = true
if showQuerys && updateSuccessful {
print("Update query is:\n \(query)")
}
}
catch {
rollback.initialize(to: true)
print(error.localizedDescription)
print(database.lastError())
print(database.lastErrorMessage())
print("UPDATE QUERY FAILED")
print(query)
updateSuccessful = false
}
}
_ = closeDatabase()
}
return updateSuccessful
}
The SQL that executes to delete a row in the BudgetDescription table is:
// Delete a specific record for a given income/expense code.
func deleteBudgetDescriptionRecordSQL(forIncomeExpenseCode incomeExpenseCode: Int) -> String
{
let query =
"""
DELETE FROM \(budget_description_table)
WHERE \(income_expense_code_field) = \(incomeExpenseCode)
"""
return query
}
The function I've written is:
// **** DELETE BUDGET DESCRIPTION RECORD FOR A GIVEN ACCOUNT CODE **** //
func deleteBudgetDescriptionRecord(forIncomeExpenseCode incomeExpenseCode: Int) -> Bool
{
var updateResult: Bool
updateResult = updateBudgetDescriptions(updateQuery: deleteBudgetDescriptionRecordSQL(forIncomeExpenseCode: incomeExpenseCode))
if showQuerys {
print("\nQUERY: deleteBudgetDescriptionRecordSQL(forIncomeExpenseCode: incomeExpenseCode)")
print(deleteBudgetDescriptionRecordSQL(forIncomeExpenseCode: incomeExpenseCode))
}
if updateResult == false {
print("deleteBudgetDescriptionRecordSQL returned FALSE when executing QUERY:")
print(deleteBudgetDescriptionRecordSQL(forIncomeExpenseCode: incomeExpenseCode))
return updateResult
}
return updateResult
}
Which executes:
// ********************** PRIVATE DRIVER FUNCTIONS TO EXECUTE DBMANAGER UPDATE DATABASE TABLE METHOD ***********//
private func updateBudgetDescriptions(updateQuery: String) -> Bool
{
var updateResult: Bool
updateResult = DBManager.shared.updateDatabaseTable(query: updateQuery)
return updateResult
}
When I execute this DELETE sql in a utility program I have called SQLiteStudio, it fails with a foreign_key notice. But when executed on the same database with FMDB it executes without error or any logging notice.
I'm relying to an extent on the database integrity to throw errors if there is a foreign key when a delete occurs. I've worked for days on this.
I have an XCode project that will demonstrate this but don't know where or how to upload it for someone to examine though.
I'd appreciate any thoughts that might help me get the constraints to work.
Bob
Well after quite a few tests I've discovered what I need to do to be able to have the DELETE throw the "Foreign Key Constraint" error. There are three things I needed to do. 1). I need the PRAGMA statement as a part of the DELETE query like this:
PRAGMA FOREIGN_KEYS = ON;
DELETE FROM \(budget_description_table)
WHERE \(income_expense_code_field) = \(incomeExpenseCode);
// EXECUTE A SERIES OF SQL STATEMENTS
func executeSQLStatements(query: String)-> Bool
{
// let time1 = Date()
if openDatabase() {
// queue.inTransaction() {
// database, rollback in
if !database.executeStatements(query) {
// rollback.initialize(to: true)
print("Failed to execute the query \(query)")
print(database.lastError())
print(database.lastErrorMessage())
if database.lastErrorMessage()=="FOREIGN KEY constraint failed" {
print("You can't do dat")
}
} else {
if showQuerys
{
print("IN executeSQLStatements - Successfully executed the query \(query)")
}
}
// }
_ = closeDatabase()
}
return true
}
//***********
2021-05-22 14:31:57.089895-0400 SqliteTest[2400:219563] Error inserting batch: FOREIGN KEY constraint failed Failed to execute the query PRAGMA FOREIGN_KEYS = ON; DELETE FROM budget_description WHERE income_expense_code = 20010 Error Domain=FMDatabase Code=19 "FOREIGN KEY constraint failed" UserInfo={NSLocalizedDescription=FOREIGN KEY constraint failed} FOREIGN KEY constraint failed
//***********
I tried a number of combinations of how to use the PRAGMA statement with the DELETE statement and this combination is the only one that worked. And it only worked in combination without using the FMDatabaseQueue.
When used with the database.executeUpdate even without the FMDatabaseQueue only one statement processed and the row wasn't deleted, but that is because only the PRAGMA statement was executed. There was no error thrown, as the DELETE wasn't executed.
When used with database.executeStatements with the FMDatabaseQueue the row was deleted and no error was thrown...
So 1) Use the PRAGMA foreign_keys=ON; with the DELETE 2) Use executeStatements 3) DON'T use FMDatabaseQueue.
Regards, Bob