# SQLite Foreign key ignored

**URL:** <https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624>\
**Category:** General\
**Created:** [12 October 2019 08:15 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624 "2019-10-12T08:15:05Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 08:15 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/1 "2019-10-12T08:15:05Z")

</div>

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:

```auto
[{"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"}]

```

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [12 October 2019 08:24 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/2 "2019-10-12T08:24:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 08:47 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/3 "2019-10-12T08:47:18Z")

</div>

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 🙂  
" 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 🙂

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [12 October 2019 09:19 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/4 "2019-10-12T09:19:21Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [12 October 2019 09:36 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/5 "2019-10-12T09:36:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 10:14 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/6 "2019-10-12T10:14:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [12 October 2019 10:20 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/7 "2019-10-12T10:20:01Z")

</div>

What version of sqlite3? From the sqlite3 docs:

> the support for foreign key constraints introduced in version 3.6.19

`

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [12 October 2019 10:24 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/8 "2019-10-12T10:24:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 10:24 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/9 "2019-10-12T10:24:40Z")

</div>

# node-red-node-sqlite 0.4.0

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [12 October 2019 10:26 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/10 "2019-10-12T10:26:09Z")

</div>

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

Also does it work in the SQLite cli?

---

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 10:29 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/11 "2019-10-12T10:29:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [12 October 2019 10:32 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/12 "2019-10-12T10:32:53Z")

</div>

It should drag in 4.1.0 of the sqlite3 lib.

> **[mapbox/node-sqlite3](https://github.com/mapbox/node-sqlite3)**
>
> Asynchronous, non-blocking SQLite3 bindings for Node.js - mapbox/node-sqlite3

---

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 11:08 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/13 "2019-10-12T11:08:05Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [12 October 2019 11:23 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/14 "2019-10-12T11:23:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 12:04 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/15 "2019-10-12T12:04:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [12 October 2019 12:32 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/16 "2019-10-12T12:32:50Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 12:47 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/17 "2019-10-12T12:47:05Z")

</div>

> [@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\_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:

```auto
[{"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

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [12 October 2019 12:54 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/18 "2019-10-12T12:54:37Z")

</div>

Unfortunately your flow isn't currently importable.Please read the following post [How to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506) and then edit the above message.

---

<div class="post-metadata">

**Author:** ![AsHex\_66](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ashex_66/32/31151_2.png) [@AsHex\_66](https://discourse.nodered.org/u/AsHex_66)\
**Post date:** [12 October 2019 12:59 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/19 "2019-10-12T12:59:59Z")

</div>

ukmoose,

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [12 October 2019 13:00 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/20 "2019-10-12T13:00:29Z")

</div>

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.

[Next page](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624.md?page=2)