Dbo: How to enable foreign key constraints in Sqlite3?
Added by Rob Farmelo over 14 years ago
Hi,
I've discovered that sqlite3 needs to be setup properly to support FK constraints. My setup is compiled for them and I can make them work using the sqlite command line by entering the special SQL command "pragma foreign_keys = on;".
I tried sending this command down to sqlite3 using a session.execute() command but my constraints aren't being maintained in subsequent operations. Am I missing something?
Also, to confirm that the pragma got set correctly, I can run this sql "pragma foreign_keys;" which will return an int (I belive). I tried running this with query() but get an exception:
terminate called after throwing an instance of 'std::logic_error'
what(): Error parsing SQL query: "PRAGMA foreign_keys;"
I'm guessing query() doesn't know how to handle this type of statement. Is there any other way to get the results of this statement? Is there a way I drop down into sqlite3 API and run it using the same connection used to set the pragma as show above?
Thanks!
Rob
Replies (1)
RE: Dbo: How to enable foreign key constraints in Sqlite3? - Added by Koen Deforche over 14 years ago
Hey Rob,
Indeed, while I was aware of the fact that foreign key contraints were a recent addition, I didn't know you still had to explicitly enable their support per connection. This is now done in SQlite3.C in git.
The following is the simple one-line patch that you could apply to your Wt version:
diff --git a/src/Wt/Dbo/backend/Sqlite3.C b/src/Wt/Dbo/backend/Sqlite3.C
index 8faa35f..10c32cb 100644
--- a/src/Wt/Dbo/backend/Sqlite3.C
+++ b/src/Wt/Dbo/backend/Sqlite3.C
@@ -504,6 +504,8 @@ Sqlite3::Sqlite3(const Sqlite3& other)
if (err != SQLITE_OK)
throw Sqlite3Exception(sqlite3_errmsg(db_));
+
+ executeSql("pragma foreign_keys = ON");
}
Sqlite3::~Sqlite3()
The documention of SQlite3 is not clear if 'pragma foreign_keys' also returns an int (as SQL result) when not used from the command line.
I doubt it returns something because the following does not work as expected, indicating that 'pragma foreign_keys' is not an SQL expression:
sqlite> select 1;
1
sqlite> select 1 + (select 1);
2
sqlite> pragma foreign_keys;
1
sqlite> select 1 + (pragma foreign_keys);
Error: near "foreign_keys": syntax error
sqlite>
Regards,
koen