sqlite foreign key constraint not enforced

My question is how to get MATLAB's sqlite databases to enforce foreign key constraints.

Here is a minimal working example illustrating what I thought should work, but does not (I am new to this stuff, so maybe I am making an obvious error):

dbFile = 'fkcheck.db';
conn = sqlite(dbFile, 'create');
% exec(conn,'PRAGMA foreign_keys=ON');  % Enabling this line makes no difference

% Create an 'artist' table; later, a foreign key will reference this table.
sCreateTbl1 = 'CREATE TABLE artist(artistid INTEGER PRIMARY KEY AUTOINCREMENT, artistname  TEXT)';
exec(conn,sCreateTbl1);

% Create a 'track' table with a foreign key referencing the artist ID in the artist table
sCreateTbl2 = ['CREATE TABLE track(trackid INTEGER PRIMARY KEY AUTOINCREMENT, trackname TEXT, '...
    'trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid))'];
exec(conn,sCreateTbl2);

% Load an artist into the artist table, just so it is not empty.
insert(conn,'artist',{'artistname'},{'Pink Floyd'});

% Load a track into the track table.
% This insert should fail because it gives a non-existent value of the foreign key (77).
% But the insert does not fail, and the resulting track table has
% a row with trackartist=77.
insert(conn,'track',{'trackname','trackartist'},{'Money', 77});

close(conn);

I found this link to a similar question from 2016. The answer says there was a bug in the Database Toolbox R2015b, but the work-around suggested there fails with 2020a (Database Toolbox Version 9.2.1) because 'conn' has no 'Handle' property.

So, how can I get MATLAB's sqlite databases to enforce foreign key constraints?

Thanks.

Answer 1

[Jeff Miller]

In case anyone else is interested, here is the answer from MATLAB technical support:

This [is] a bug in executing PRAGMA queries using 'exec()'.

We will be working on this issue to resolve this bug for the upcoming future releases.

However, there is a workaround for this issue:

• As mentioned in the answer for How to enable foreign key for SQLite database, the issue of PRAGMA statements not getting executed by 'exec()' was fixed for R2016a and later releases.
• Hence, moving to a SQLite JDBC workflow would resolve this issue.
• Please refer to the documentation of SQLite JDBC for Windows for the details of how to set up a data source and connect to an SQLite database using the Database Explorer app or the command line.
• Connecting to a database is mentioned in Step 3. Connect using the Database Explorer app or the command line
• Now, you may proceed with using 'exec()' functions with PRAGMA queries and you shall be able to see expected behaviour.