B4J Question B4X SQLite Database

[Sergey_New]

Is it possible to set foreign keys on tables and cascading updates and deletions of records?

 

[aeric]

SQLite Foreign Key Support

 

[Sergey_New]

Can this be done in the B4J project? The booklet "B4X SQLite Database" does not say anything about this.

 

[aeric]

Can this be done in the B4J project? The booklet "B4X SQLite Database" does not say anything about this.

Are you creating the database by code?
I haven't tested, I think we can execute following command before creating a table:

SQL.ExecNonQuery("PRAGMA foreign_keys = ON;")

Then create your table like in the example.

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER REFERENCES artist
);
CREATE INDEX trackindex ON track(trackartist);

 

[Sergey_New]

aeric, thanks!
Will cascading updates and deletions of records work?
The question arose because of the following:
I created two linked tables in the third-party program "Navicat Premium". When adding a new record to the subordinate table with a key that is not in the main table, no exception is thrown. Why?
So I am trying to create tables in code.

 

[aeric]

I seldom create foreign key and do deletion of records. You can try to create a small project and see how it goes.

 

[Sergey_New]

Made an example::

Sub FillDb
    Dim db As SQL
    File.Copy(File.DirAssets,"empty.db",File.DirApp,"my.db")
    db.InitializeSQLite(File.DirApp, "my.db", True)
    db.ExecNonQuery("CREATE TABLE artist(artistid INTEGER PRIMARY KEY,artistname  TEXT)")
    db.ExecNonQuery("CREATE TABLE track(trackname TEXT,trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE)")
    db.ExecNonQuery("INSERT INTO track (trackname, trackartist) VALUES ('abc',2)")
End Sub

When adding an entry to the track table, I figured an exception should be thrown, since there is no entry with key 2 in the artist table. But there is no exclusion.
SQLite doesn't work like that?

 

[EnriqueGonzalez]

According to sqlite documentation,thet it seems you were getting your examples
https://www.sqlite.org/foreignkeys.html

" Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;
"

 

[Sergey_New]

sqlite> PRAGMA foreign_keys = ON;

Inserted after database initialization:

db.ExecNonQuery("PRAGMA foreign_keys = ON")

An exception has appeared. Thanks everyone!

 

[aeric]

File.Copy(File.DirAssets,"empty.db",File.DirApp,"my.db")

If you are creating a new database from code then you don’t need to add an empty file in asset folder.

 

[Sergey_New]

If you are creating a new database from code then you don’t need to add an empty file in asset folder.

Thank you, I understand that.
This is a line from code when I didn't create the database in code. I have a lot of tables with links, so I create an empty database in a third-party program, it's easier