Repository navigation
Foreign Key Pragma doesn't persists between connections #848
Description
Details for the issue
We are using sqllitedb browser to maintain a local db on the device. The issue is everytime we close and reopen the device the foreign key constraint/pragma is unchecked and as a part user is able to insert corrupt content in the DB. Please resolve.
Useful extra information
I'm opening this issue because:
- DB4S is crashing
- DB4S has a bug
- DB4S needs a feature
- DB4S has another problem
I'm using DB4S on:
- Windows: ( version: ___ )
- Linux: ( distro: ___ )
- Mac OS: ( version: ___ )
- Other: ___
I'm using DB4S version:
- 3.9.1
- 3.9.0
- Other: ___
I have also:
- Tried out the nightly version: https://github.com/sqlitebrowser/sqlitebrowser#windows
- Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20
Activity
justinclift commentedon Oct 27, 2016
Ouch, that doesn't sound good. 😦
Does it happen even when you save using a project file?
Open database
Change Pragma setting
File → Save Project (then pick a filename to save as)
Close the database
File → Open Project
In theory, settings are supposed to be saved ok when using project files. They're a way to wrap "meta" information about a database in a safe way.
But, it's definitely possible that's not working properly for this. 😦
tomcumming commentedon Jan 8, 2017
Same issue on windows portable build. Also seems to ignore the pragma after I turn it on (i can still insert invalid rows)
Still seems broken in nightly
MKleusberg commentedon Jan 8, 2017
As a first thought: is the check box in View -> Preferences, Database, Foreign keys checked or unchecked?
tomcumming commentedon Jan 8, 2017
Yes, it is checked. I'm sorry I can't build myself and check what's going on for you but I don't do any dev on windows
MKleusberg commentedon Jan 8, 2017
That's odd. You said it's also ignoring the pragma after manually turning it on. I assume you did click the Save button at the bottom of the window? Sounds like a stupid question but I sometimes forget that button 😉 If you're sure you saved the modified pragma settings. it seems like we have a general problem with foreign keys in your case. Not sure yet what to do then 😃
tomcumming commentedon Jan 8, 2017
yes I am :) i also tried
PRAGMA foreign_keys = on;in the sql box and write changes after running that. Still no luckMKleusberg commentedon Jan 14, 2017
I did some further research: you can't edit the foreign keys pragma inside a transaction (https://www.sqlite.org/pragma.html#pragma_foreign_keys). That makes it impossible to set that pragma from inside the SQL editor. Also, unlike other pragmas, it is not stored in the database file but must be set again each time a new connection is built (https://stackoverflow.com/a/15303742). This makes it impossible to set the foreign keys pragma in the Pragma editor and expect it to stay with that value. That's pretty stupid but we can't do much about that 😦
The only question is why the the check box in the Preferences dialog doesn't work for you. However, I've just changed the settings handling a bit, so tomorrow's nightly build might be worth checking out. Can you give that a try? 😃
justinclift commentedon Jan 14, 2017
If it's really needed to kept, we could store that pragma setting in the (per database) project file.
Save foreign keys settings in the project files
MKleusberg commentedon Jan 22, 2017
Just added the foreign keys settings to the project file 😃 I think that's all we can do at the moment.
justinclift commentedon Jan 22, 2017
Awesome. 😄
@tomcumming Would you have time to try out the nightly build (tomorrow), and check that the pragma setting works for you when the database is saved & loaded via project file?
tomcumming commentedon Jan 22, 2017
Sorry @justinclift , I won't have access to a windows PC for a while !
justinclift commentedon Jan 22, 2017
No worries, was a thought. 😄
justinclift commentedon Jan 22, 2017
Oops. Sorry, wrong button. 😉
@tomcumming Our automatic nightlies get built for OSX too if that helps?
MKleusberg commentedon Oct 3, 2018
Any update here? 😄
chrisjlocke commentedon Jan 8, 2019
I've created two project files - one with the 'Foreign Keys' checked and one unchecked. When loading, the 'Foreign Keys' option is checked/unchecked, so I'll close this as it has been resolved.
@tomcumming - I appreciate its been a while since you opened this issue, but if you still use DB4S, you'd be recommended to try the latest nightly.