Repository navigation
UI inconsistent with database when using deferred foreign keys #770
Description
Details for the issue
I am attempting to design a DB using foreign keys. It has already been discussed that there are deficiencies if a column has a FK as well as a non-null, since you cannot add a row. I thought I might be able to work around it, and discovered (I think) another bug. I attempted to make my FK "DEFERRABLE INITIALLY DEFERRED" (https://www.sqlite.org/foreignkeys.html#fk_deferred)
So my test schema is (create this DB using sqlite3):
CREATE TABLE one ( pk INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT );
INSERT INTO "one" VALUES(1,'test');
CREATE TABLE two( pk INTEGER PRIMARY KEY AUTOINCREMENT, fk INTEGER NOT NULL, FOREIGN KEY( fk ) REFERENCES one(pk) DEFERRABLE INITIALLY DEFERRED );
After that, if you open the DB inside sqlitebrowser, turn on the foreign key pragma, click save, then enter a row in table "two" that violates the FK, not only does it not report an error, but the UI and the database get out of sync. The UI shows the row entered, but if you quit sqlitebrowser and open the DB in sqlite3, it is clearly not there.
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
- Linux: ( gentoo )
- Mac OS
- Other: ___
I'm using DB4S version:
- 3.9.0
- 3.8.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
dflogeras commentedon Sep 9, 2016
Arrg I don't know where the text I just typed went. OK again,
I tried to work around the fact that sqlitebrowser doesn't work when you have a foreign key with NOT NULL.
Using sqlite3, create the following schema (note the DEFERRABLE INITIALLY DEFERRED, see https://www.sqlite.org/foreignkeys.html#fk_deferred):
CREATE TABLE one ( pk INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT );
INSERT INTO "one" VALUES(1,'test');
CREATE TABLE two( pk INTEGER PRIMARY KEY AUTOINCREMENT, fk INTEGER NOT NULL, FOREIGN KEY( fk ) REFERENCES one(pk) DEFERRABLE INITIALLY DEFERRED );
Then open that DB in sqlitebrowser, enable foreign keys pragma, click save.
Now, add a row to the "two" table that violates the FK. Sqlitebrowser lets you add it, doesn't complain, and shows it added in the UI, but if you quit and look at the DB in sqlite3, it is (correctly) not there.
If you do this without the DEFERRABLE INITIALLY DEFERRED, you just plain cannot add any data to the table (as discussed in #638).
justinclift commentedon Sep 9, 2016
Oops. When you wrote it out, you put it inside the
<!--and-->tags. Browsers don't show stuff between those tags, so it effectively disappeared. I've just removed those two tags from around what you typed, so it shows up now. 😄Btw, would you be ok trying the NOT NULL foreign key support with our very latest code from the
masterbranch? @MKleusberg was improving our foreign key support last week, so there's a chance that's working now. (I'd call it 50/50 😉)dflogeras commentedon Sep 9, 2016
Thanks Justin (re the missing text), I thought that might have been the case :) oops.
I just tried master, and it is slightly workable. It seems now new rows are inserted with 1 in the FK column, instead of 0. That works, as long as I have a 1 in the column the FK refers to, which may or may not be the case :)
If the idea suggested in other tickets to have a manual BEGIN/COMMIT process was workable, and this bug regarding DEFERRABLE FKs was fixed, then I think that would achieve a desirable result. Users could click BEGIN, add a row, doctor it to taste even if its initial state violates the FK, then find out if it is accepted when they hit COMMIT.
I totally understand though that such a (seemingly) simple task might be very disruptive under the hood.
justinclift commentedon Sep 9, 2016
Excellent, thanks for testing it and letting us know @dflogeras. 😄
@MKleusberg Sounds like this will be of interest to you. 😄
MKleusberg commentedon Oct 12, 2016
Hmm, this is going to be an interesting issue 😉 What I can do is print an error message when trying to save, i.e. when doing the COMMIT. At least this way you'd know that there is an error and that the DB wasn't actually saved as expected. However, the only error message we can get is "FOREIGN KEY constraint failed" but not any details.
dflogeras commentedon Oct 12, 2016
IMHO that's better than silently letting the UI and the DB diverge. If the user is using foreign keys, they should understand what "FOREIGN KEY constraint failed" implies.
MKleusberg commentedon Oct 12, 2016
Yep, working on it right now 😃
Improve error handling when saving
MKleusberg commentedon Oct 12, 2016
I've just pushed a commit which changes the behaviour here. Now it prints an error message when trying to save the database. Can you test this by just building the latest source from master, @dflogeras, and confirm if it's working for you? 😃
dflogeras commentedon Oct 12, 2016
Seems to work at e76e5b3 for the test described in the first comment wrt deferred foreign keys. Thanks!
MKleusberg commentedon Oct 13, 2016
Thanks for reporting back so quickly, @dflogeras 👍 Also good working finding this bug - I honestly would have never found it 😉 Are we ok to close this issue then?
dflogeras commentedon Oct 13, 2016
Hey Martin, thanks for addressing it! Closing.
revolter commentedon Nov 28, 2016
@sqlitebrowser/developers, As the actual issue is not fixed, wouldn't help to open a new issue with this and hopefully get it fixed sometimes? We do want to support all SQLite features in the future, right?
justinclift commentedon Nov 28, 2016
@revolter Yeah, that makes sense.
revolter commentedon Nov 28, 2016
@justinclift Care to create it yourself please? 😄
justinclift commentedon Nov 28, 2016
Maybe later. My head is buried in other stuff at the moment.
Improve error handling when saving