B4J Question B4X SQLite Database

Sergey_New

Well-Known Member
Licensed User
Longtime User
Is it possible to set foreign keys on tables and cascading updates and deletions of records?
 

Sergey_New

Well-Known Member
Licensed User
Longtime User
Can this be done in the B4J project? The booklet "B4X SQLite Database" does not say anything about this.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
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:
B4X:
SQL.ExecNonQuery("PRAGMA foreign_keys = ON;")

Then create your table like in the example.
SQL:
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);
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
Made an example::
B4X:
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?
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
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;
"
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
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 :)
 
Upvote 0
Top