sqlite foreign key constraint not enforced

3 views (last 30 days)
Jeff Miller
Jeff Miller on 4 Feb 2021
Answered: Jeff Miller on 12 Feb 2021
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.

Answers (1)

Jeff Miller
Jeff Miller on 12 Feb 2021
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:

Products


Release

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!