SQLite Foreign key ignored

General
1

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 artist.artistid!
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

If I try to delete an entry from the artist table, that has been used in the track table, then SQLite throws an error: [18:10:46] Error while executing SQL query on database 'test2': FOREIGN KEY constraint failed. This is what I expect.

Now I try to delete this via Node-Red, and it happily deletes the entry, even while I have the foreign key constraint. I did try setting the "PRAGMA foreign_keys = 1" in the database without avail. Am I missing something or is this a bug?

Test code below:

[{"id":"833221a7.dc2c8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"ef90ba5c.668768","type":"inject","z":"833221a7.dc2c8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":300,"y":300,"wires":[["e3e9e7d2.56a9f8"]]},{"id":"2eac59af.e497c6","type":"debug","z":"833221a7.dc2c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":810,"y":300,"wires":[]},{"id":"72ee0096.7c029","type":"complete","z":"833221a7.dc2c8","name":"","scope":["16b53d78.419833"],"uncaught":false,"x":520,"y":350,"wires":[["d6b09823.132738"]]},{"id":"d6b09823.132738","type":"debug","z":"833221a7.dc2c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":710,"y":350,"wires":[]},{"id":"da1b8b79.b204d8","type":"catch","z":"833221a7.dc2c8","name":"","scope":["72ee0096.7c029","e3e9e7d2.56a9f8"],"uncaught":false,"x":500,"y":400,"wires":[["2d6333dd.56a36c"]]},{"id":"2d6333dd.56a36c","type":"debug","z":"833221a7.dc2c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":710,"y":400,"wires":[]},{"id":"8959a77a.24d018","type":"comment","z":"833221a7.dc2c8","name":"Data Base example inside","info":"-- DataBase example\n\nCREATE TABLE artist(\n  artistid    INTEGER PRIMARY KEY, \n  artistname  TEXT\n);\nCREATE TABLE track(\n  trackid     INTEGER,\n  trackname   TEXT, \n  trackartist INTEGER     -- Must map to an artist.artistid!\n  FOREIGN KEY(trackartist) REFERENCES artist(artistid)\n);","x":330,"y":200,"wires":[]},{"id":"e3e9e7d2.56a9f8","type":"sqlite","z":"833221a7.dc2c8","mydb":"70ad58fe.56e7b8","sqlquery":"fixed","sql":"PRAGMA foreign_keys = ON;\ndelete from artist\nwhere artistid = 3","name":"test","x":500,"y":300,"wires":[["2eac59af.e497c6"]]},{"id":"70ad58fe.56e7b8","type":"sqlitedb","z":"","db":"D:\\DBase\\test2.db","mode":"RWC"}]
2

Maybe like this ? https://stackoverflow.com/questions/9937713/does-sqlite3-not-support-foreign-key-constraints

3

dceejay, Thanks for your prompt reply.
Like mentioned in my original post, I did set the "PRAGMA foreign_keys = on or 1"
Even when querying the status of this in SQLite, it confirms this is active.
However, Node-Red SQLite ignores this and will delete, without error.
The only thing i do not fully comprehend is the comment :slight_smile:
" In SQLite 3.x, you have to make the following query every time you connect to an SQLite database"
The every time is something I am unsure how to do as the fixed statement:
delete from artist
where artistid = 1;
--n PRAGMA foreign_keys = 0;
or any combination will through an error (ignore --n)

So how can I force the foreign key constraint????

Thanks again :slight_smile:

4

Sorry - I don't use SQLITE enough to know. - What does PRAGMA foreign_keys; return ? The syntax seems to suggest you need to set it ON . maybe it is case sensitive ?

5

I think maybe you have to send the node the sql statement
PRAGMA foreign_keys = ON
before you send it your delete statement. You should only need to send the pragma command once after startup. I have not tried this however.

6

Thanks both for the reply.

I did try before in a Node-red prepared command:

PRAGMA foreign_keys = On;
delete from artist
where artistid = 1;

Result, does not delete nor error

delete from artist
where artistid = 1;
PRAGMA foreign_keys = On;

Result, does not delete nor error neither

So as soon as I put this statement in the prepare query, it does not delete but it does not give me an error in the console either. I am missing something or it might be a bug in the SQLite Node red.

7

What version of sqlite3? From the sqlite3 docs:

the support for foreign key constraints introduced in version 3.6.19

`

8

I just updated the node to ensure it pulls the latest. so may need to re-install/update it

9

node-red-node-sqlite 0.4.0

@zenofmud, so am not sure what version of sqlite tis is?

10

Thats the version of the node not sqlite3. But reinstall it re Dave’s post.

Also does it work in the SQLite cli?

11

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 query on database 'test2': FOREIGN KEY constraint failed

Node-red ignores this, but I will try the update and test again.

Thanks both for the feedback, thought I was going nut, and appreciate the help

12

It should drag in 4.1.0 of the sqlite3 lib.

13

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 = 1;

Result:
{"_msgid":"54fa2f14.e1fa3","topic":"","payload":}
which did delete the artist without error or foreign key restraint

This is doing my head in as I am now struggling with this for the last 18 hours...
Need sleep but my brain is fighting this...

Any other suggestions?

14

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

15

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;
result via msg
{"_msgid":"8536dc5a.f3a1c","topic":"","payload":}
e.g all good however item was deleted despite the foreign key restraint...

I am missing a fundamental point here or there is something i do not know...

Again, all, thank you for your input, appreciated

16

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?

17

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_keys = ON;
delete from artist
where artistid = 1;
Did executed quite happily, but this did not delete the item.

Then i tried:
PRAGMA foreign_keys = ON;
delete from artist
where artistid = 3;
Did executed quite happily, but this did not delete the item either and the artist id was not used in the track table, but defined in the artist table. Hence my thought that if a command ends with ';' the query stops and does not continue with the rest of the commands.

The more I look at it, the more I think it is a bug, but my brain might be playing games after 19 hours of torture. Will give it a rest soon and sleep over it. Any suggestions will be appreciated

The catch node did not reveal any further feedback...

Latest test code:

[{"id":"833221a7.dc2c8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"ef90ba5c.668768","type":"inject","z":"833221a7.dc2c8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":300,"y":300,"wires":[["e3e9e7d2.56a9f8"]]},{"id":"2eac59af.e497c6","type":"debug","z":"833221a7.dc2c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":810,"y":300,"wires":[]},{"id":"72ee0096.7c029","type":"complete","z":"833221a7.dc2c8","name":"","scope":["16b53d78.419833"],"uncaught":false,"x":520,"y":350,"wires":[["d6b09823.132738"]]},{"id":"d6b09823.132738","type":"debug","z":"833221a7.dc2c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":710,"y":350,"wires":[]},{"id":"da1b8b79.b204d8","type":"catch","z":"833221a7.dc2c8","name":"","scope":["72ee0096.7c029","e3e9e7d2.56a9f8"],"uncaught":false,"x":500,"y":400,"wires":[["2d6333dd.56a36c"]]},{"id":"2d6333dd.56a36c","type":"debug","z":"833221a7.dc2c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":710,"y":400,"wires":[]},{"id":"8959a77a.24d018","type":"comment","z":"833221a7.dc2c8","name":"Data Base example inside","info":"-- DataBase example\n\nCREATE TABLE artist(\n  artistid    INTEGER PRIMARY KEY, \n  artistname  TEXT\n);\nCREATE TABLE track(\n  trackid     INTEGER,\n  trackname   TEXT, \n  trackartist INTEGER     -- Must map to an artist.artistid!\n  FOREIGN KEY(trackartist) REFERENCES artist(artistid)\n);","x":330,"y":200,"wires":[]},{"id":"e3e9e7d2.56a9f8","type":"sqlite","z":"833221a7.dc2c8","mydb":"70ad58fe.56e7b8","sqlquery":"fixed","sql":"PRAGMA foreign_keys = ON;\ndelete from artist\nwhere artistid = 3","name":"test","x":500,"y":300,"wires":[["2eac59af.e497c6"]]},{"id":"70ad58fe.56e7b8","type":"sqlitedb","z":"","db":"D:\\DBase\\test2.db","mode":"RWC"}]

A

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.

19

ukmoose,

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

20

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.