Possible bug when adding "on delete cascade" via DB Browser for SQLite

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Possible bug when adding "on delete cascade" via DB Browser for SQLite

Magnus Andersson
Today I used DB Browser for SQLite, version 3.10.1 on a windows 7 machine,
and encountered what seems to be a bug, either in the SQLite browser or
SQLite as such.

 

If this is not the correct mailing list to post this in I apologize, just
let me know.

 

I had a table with foreign keys, and added "on delete cascade" via the
function "Modify table". The "create table" script then ended up like this.

 

(Strange one that caused wrong cascaded deletes)

CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
`speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
KEY(`speechInLanguageID`), FOREIGN KEY(`speechID`) REFERENCES
`tblSpeech`(`speechID`) ON DELETE CASCADE )

 

I then tested the cascade action, and everything in the table was deleted,
not just those rows that should have been deleted.

 

I then went back to a backup, did the whole thing again, and then everything
worked, but I noticed that the "create table" script was a little different.

 

(Corrrect one that worked)

CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
`speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
KEY(`speechID`) REFERENCES `tblSpeech`(`speechID`) on delete cascade,
FOREIGN KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
KEY(`speechInLanguageID`) )

 

 

As you see (as far as I can see) everything is the same in both, except for
the order in which they appear (if you paste both scripts on two lines after
each other in something like notepad without word wrap, you can see it more
clearly).

 

Magnus

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug when adding "on delete cascade" via DB Browser for SQLite

Chris Locke-3
DB Browser for SQLite is a third party product which just uses SQLite.  Any
support issues should be directed to their gitHub support page.
https://github.com/sqlitebrowser/sqlitebrowser/issues

To confirm whether its an issue in DB Browser for SQLite or SQLite itself,
you can 'reproduce' the issue using the SQLite command line tool.  If that
too fails, then the issue should be confirmed here where it will get
properly investigated.

I know DB Browser for SQLite is still in heavy development, and it does
have a number of little quirks like this..... ;)


Thanks,
Chris

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Jan 13, 2018 at 6:02 PM, Magnus Andersson <
[hidden email]> wrote:

> Today I used DB Browser for SQLite, version 3.10.1 on a windows 7 machine,
> and encountered what seems to be a bug, either in the SQLite browser or
> SQLite as such.
>
>
>
> If this is not the correct mailing list to post this in I apologize, just
> let me know.
>
>
>
> I had a table with foreign keys, and added "on delete cascade" via the
> function "Modify table". The "create table" script then ended up like this.
>
>
>
> (Strange one that caused wrong cascaded deletes)
>
> CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
> `speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
> KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
> KEY(`speechInLanguageID`), FOREIGN KEY(`speechID`) REFERENCES
> `tblSpeech`(`speechID`) ON DELETE CASCADE )
>
>
>
> I then tested the cascade action, and everything in the table was deleted,
> not just those rows that should have been deleted.
>
>
>
> I then went back to a backup, did the whole thing again, and then
> everything
> worked, but I noticed that the "create table" script was a little
> different.
>
>
>
> (Corrrect one that worked)
>
> CREATE TABLE "tblSpeechInLanguage" ( `speechInLanguageID` INTEGER,
> `speechID` INTEGER NOT NULL, `languageID` INTEGER NOT NULL, FOREIGN
> KEY(`speechID`) REFERENCES `tblSpeech`(`speechID`) on delete cascade,
> FOREIGN KEY(`languageID`) REFERENCES `tblLanguage`(`languageID`), PRIMARY
> KEY(`speechInLanguageID`) )
>
>
>
>
>
> As you see (as far as I can see) everything is the same in both, except for
> the order in which they appear (if you paste both scripts on two lines
> after
> each other in something like notepad without word wrap, you can see it more
> clearly).
>
>
>
> Magnus
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users