I have an issue an unsure if this a bug or not. I created a test database (SQLite) as follows: -- DataBase example CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER -- Must map to an …

So I just did a test and if you add ON DELETE CASCADE to the table and then delete an artist, the titles of that artist DO NOT get deleted. However, if you issue a `PRAGMA foreign_keys = ONE before deleting the artist, the titles for that artist ARE deleted the way the information I referred to abo…

zenofmud, I am using SQLiteStudio 3.2.1 and in this it does work fine in this. It does restrict deletion on foreign key, and I am using the same command as I am trying in Node-Red delete from artist where artistid = 1 In the studio it does throw an error [18:10:46] Error while executing SQL que…

It should drag in 4.1.0 of the sqlite3 lib. [image] mapbox/node-sqlite3 Asynchronous, non-blocking SQLite3 bindings for Node.js - mapbox/node-sqlite3

Pulled it in with NPM but only dragged in 4.0. Tried it again in my cli: delete from artist where artistid = 1 Result: Error while executing SQL query on database 'test2': FOREIGN KEY constraint failed Then tried this in a fixed statement of an SQLite node: delete from artist where artistid …

You still need the pragma. It must be the default in sqlitestudio

Colin, Tried the following from Node-red PRAGMA foreign_keys = ON; delete from artist where artistid = 1; Result via msg {"_msgid":"fe61c466.d2f4e8","topic":"","payload":} e.g all processed, all good but item was not deleted delete from artist where artistid = 1; PRAGMA foreign_keys = ON; …

In the second case you are deleting the row and then telling it to respect the keys, by which time it is too late. As to why the first case did not generate an error, I don't know. Does a Catch node pick it up?

[image] AsHex_66: PRAGMA foreign_keys = ON; delete from artist where artistid = 1; Colin, I am not prolific enough with SQLite nor with node-red, but I think the the delimiter ';' stops any further execution of code. To proof this i tried (in Node-red, fixed statement): PRAGMA foreign_key…

ukmoose, Apology, should have read what you mentioned. Was a long day and missed the contents of your comment. Corrected now :slight_smile:

I thought you were using a prepared statement, I thought you said that earlier. Quite likely you cannot put two sequential commands other than in a prepared statement. I also am not that familiar with the full subtleties of that node so I do not know for certain.

Node-RED

Node-RED Forum

SQLite Foreign key ignored

General

ukmoose 12 October 2019 12:54 18

Unfortunately your flow isn't currently importable.Please read the following post How to share code or flow json and then edit the above message.

Topic		Replies	Views
Sqlite3 data insert problem General	29	2584	5 January 2022
"Error: SQLITE_ERROR: no such table: NCS" General	50	3390	31 August 2020
SQLite node syntax error but sqlitebrowser works General	24	1554	3 July 2021
Insert csv. file to SQLite General	18	1319	18 March 2021
Nodered and SQLite Integration - Error General function-node	15	265	3 February 2025

Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.

SQLite Foreign key ignored

Related topics