Foreign Key Constraint Failed When Changing Default Value

[tomhosker]

Details for the issue

What did you do?

I attempted to change the default value for the humour field in the article table of my almanack database from "" to "n". (I also tried " ", with the same result.)

What did you expect to see?

I expected the default value to be changed from "" to "n".

What did you see instead?

An error message. See the attached screenshot.

Useful extra information

I get a similar issue to the above when trying to carry out most other changes to the field, such as toggling Not null, or changing its foreign key constraint. (Currently, there is no foreign key constraint.)

What operating system are you using?

• Linux: ( _distro: Ubuntu)

What is your DB4S version?

• Other: Latest Debian.
  

[chrisjlocke]

Currently, there is no foreign key constraint

That would go against what the error is saying.
Any chance of posting a copy of the database sans data?

[mgrojo]

There might be some some foreign key contraint failing in another table which is triggered in the table modification transaction. The menu entry Tools > Integrity Check may be useful to find it out.

[tomhosker]

@chrisjlocke

That would go against what the error is saying.

If you have a closer look at that screenshot I put up: "FOREIGN KEY constraint failed (INSERT INTO sqlitebrowser_rename_column_new_table SELECT...". The foreign key constraint - the one being violated - is on one of the browser's internal tables, and not the user's - that is, mine.

@mgrojo
Hmmm, I couldn't find the Tools > Integrity Check option on my version of the browser. Perhaps my version is out of date? (This may be the problem!) I'll look into this and get back to you.

I'll attach a copy of the database. The data isn't sensitive in any way, so I'll leave it in (unless that's inconvenient for you guys).
the_database_is_in_here.zip

[tomhosker]

As a coda: I have to say I was really impressed by how quickly you guys got back to me. Sorry I couldn't return the favour. And the browser is a fantastic piece of software. It makes me want to throw my laptop out of the window less frequently than pretty much any other program!

[mgrojo]

@mgrojo
Hmmm, I couldn't find the Tools > Integrity Check option on my version of the browser. Perhaps my version is out of date? (This may be the problem!) I'll look into this and get back to you.

Latest Debian is probably 3.10. The tools menu was added in 3.11. Anyway, those tools simple run PRAGMA foreign_key_check; or PRAGMA integrity_check; in an SQL tab. That means you can still run it yourself.

I'll attach a copy of the database. The data isn't sensitive in any way, so I'll leave it in (unless that's inconvenient for you guys).
the_database_is_in_here.zip

Running PRAGMA integrity_check on that DB says Ok, butPRAGMA foreign_key_check detects these problems:

table	rowid	parent	fkid
article	778	non_author	0
article	783	author	2
article	796	author	2
article	803	author	2
article	804	source	1
article	807	source	1
article	809	author	2
article	828	non_author	0
article	836	author	2
article	842	source	1

Foreign key enforcement is disabled by default in SQLite, but you can enable it running PRAGMA foreign_keys = true. This is automatically done by DB Browser for SQLite at startup,unless disabled in Preferences.

As a coda: I have to say I was really impressed by how quickly you guys got back to me. Sorry I couldn't return the favour. And the browser is a fantastic piece of software. It makes me want to throw my laptop out of the window less frequently than pretty much any other program!

Thanks, it's great to read messages like that.

[tomhosker]

Thank you very much for the tips. Once I ironed out the issues you highlighted, DB4S allowed me to change the default value for the field in question.

Unfortunately, it still won't allow me to add a foreign key constraint for the same field. I get a similar error message to before (see attachment).

I've attached the latest version of my DB.

my_database_is_in_here.zip

[tomhosker]

Any progress on this? I'm still struggling with the same issue.

[mgrojo]

@tomhosker, according to the screenshot, you're not using the latest release. Could you check with the version 3.11.2? Your problem might already be solved.

[tomhosker]

Thank you very much for getting back to me again. I've updated my version of DB4S to 3.11.2, as you requested, and the update does change things: I get a different error!

Now, when I try to change the foreign key for the "humour" field of the "article" to "'humour'('name')", everything seems to work. But as soon as I close the "Modify Table" window, I get the error message: "Error checking foreign keys after table modification. Changes will be reverted." If I try the same thing a second time, I get a somewhat different error (see screenshots).

[scottfurry]

Sounds like closing w/o "commiting" transaction. IIRC, commit is available from the menu. If change name, commit, and save (no close DB4S) do you get the same error?

…

On Wed, Sep 11, 2019, 14:17 Tom Hosker, ***@***.***> wrote: Thank you very much for getting back to me again. I've updated my version of DB4S to 3.11.2, as you requested, and the update does change things: I get a *different* error! Now, when I try to change the foreign key for the "humour" field of the "article" to "'humour'('name')", everything seems to work. But as soon as I close the "Modify Table" window, I get the error message: "Error checking foreign keys after table modification. Changes will be reverted." If I try the same thing a second time, I get a somewhat different error (see screenshots). [image: Screenshot from 2019-09-11 20-59-46] <https://user-images.githubusercontent.com/36990655/64731629-840dcf00-d4d9-11e9-9467-ed5ec5a32d56.png> [image: Screenshot from 2019-09-11 21-02-03] <https://user-images.githubusercontent.com/36990655/64731631-840dcf00-d4d9-11e9-811a-015d7f9daa71.png> [image: Screenshot from 2019-09-11 21-04-29] <https://user-images.githubusercontent.com/36990655/64731633-84a66580-d4d9-11e9-802d-c61313d40aea.png> — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1981?email_source=notifications&email_token=ABTAO26DLREOL67TOWKZZ3TQJFG5TA5CNFSM4IRPCBY2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6PYJXQ#issuecomment-530547934>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABTAO2464NK6SDO5WC6SANDQJFG5TANCNFSM4IRPCBYQ> .