How to enable foreign key for sqlite database

Question

Alexander Dallinger 2016-2-29

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/270561-how-to-enable-foreign-key-for-sqlite-database

回答： Alexander Dallinger 2016-3-2

The sqlite database is connected via the database toolbox, using the the java JDBC driver (from Xerial , Version 3.8.11.2).

According to the official Sqlite Website foreign keys can be switched on by using an sql query (PRAGMA foreign_keys = ON;). But that does not affect anything. Example, the last exec query should not be accepted by sqlite, but is:

dbpath  = 'c:\tmp\test.db';
if exist(dbpath,'file')==2
    delete(dbpath)
end
%   connect to database
conn = database(dbpath,[],[],'org.sqlite.JDBC','jdbc:sqlite:');
curs = exec(conn,'PRAGMA foreign_keys=ON')
%   create table
curs=exec(conn,'CREATE TABLE artist(artistid INTEGER PRIMARY KEY, artistname  TEXT)');
curs=exec(conn,'INSERT INTO artist (artistid,artistname) VALUES (1,''Dean Martin'')');
curs=exec(conn,'INSERT INTO artist (artistid,artistname) VALUES (2,''Frank Sinatra'')');
curs=exec(conn,'CREATE TABLE track(trackid INTEGER, trackname TEXT, trackartist INTEGER,FOREIGN KEY(trackartist) REFERENCES artist(artistid))');
curs=exec(conn,'INSERT INTO track (trackid,trackname,trackartist) VALUES (11,''Thats Amore'',1)');
curs=exec(conn,'INSERT INTO track (trackid,trackname,trackartist) VALUES (12,''Christmas Blues'',1)');
curs=exec(conn,'INSERT INTO track (trackid,trackname,trackartist) VALUES (13,''My Way'',2)');
curs=exec(conn,'INSERT INTO track VALUES (14,''Mr. Bojangles'',3)');
dat1=fetch(conn,'SELECT * FROM artist')
dat2=fetch(conn,'SELECT * FROM track')
%   close connection
close(conn);

Other web-sites ( forum link ) say, that code like the following has to used. But how can that be implemented in Matlab?

public static final String DB_URL = "jdbc:sqlite:database.db";  
public static final String DRIVER = "org.sqlite.JDBC";  
public static Connection getConnection() throws ClassNotFoundException {  
    Class.forName(DRIVER);  
    Connection connection = null;  
    try {  
        SQLiteConfig config = new SQLiteConfig();  
        config.enforceForeignKeys(true);  
        connection DriverManager.getConnection(DB_URL,config.toProperties());  
    } catch (SQLException ex) {}  
    return connection;  
}

Foreign keys are essential to database consistency and should be supported also by Matlab. Any help is highly appreciated.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Alexander Dallinger 2016-3-2

在 MATLAB Online 中打开

From Mathworks Support:

There is a bug in Database Toolbox R2015b which prevents foreign keys from working. To work around this issue, execute the query on a lower level using:

% Get a handle to the underlying JDBC connection
h = conn.Handle
% Create your own statement
s = h.createStatement
% Use this statement to execute the query
s.execute('PRAGMA foreign_keys=ON')
% Close the statement
s.close