# 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:** 10\
**Page:** 2

<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 13:06 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/21 "2019-10-12T13:06:20Z")

</div>

Colin,

I tried both, without avail. Fixed statement does not allow you to include variables, prepare does and is cleaner and safer then via msg.topic. But i will try a 'batch without response as this will allow multiple statements.

---

<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 14:46 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/22 "2019-10-12T14:46:36Z")

</div>

Have you got multiple processes writing to the database? If not then then you could do it by checking to see if there are any references to it first and only delete if none. I know that is not ideal, but it would at least get you up and running.  
In fact could you do it in a single statement by including in the where clause that COUNT of references to it is 0?

---

<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 15:50 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/23 "2019-10-12T15:50:37Z")

</div>

Did you set any foreign key constraints?

> **[SQLite Foreign Key: Enforce Relationships Between Tables](https://www.sqlitetutorial.net/sqlite-foreign-key/)**
>
> This tutorial shows you how to use the SQLite foreign key constraint to enforce the relationships between correlated tables.

---

<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 16:15 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/24 "2019-10-12T16:15:26Z")

</div>

Methinks someone didn't read the first post in the thread 🙂

---

<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 17:32 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/25 "2019-10-12T17:32:51Z")

</div>

@Colin if you are referring to me Colin, I see no reference to ON UPDATE ... or ON DELETE... in the SQL provided in the first or any post. However after spending two days babysitting two of the grandsons, I could have missed anything (is it Monday yet???)🤪

---

<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 18:39 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/26 "2019-10-12T18:39:48Z")

</div>

> [@zenofmud](#):
>
> I see no reference to ON UPDATE ... or ON DELETE...

My understanding (which may be faulty) is that just by providing the foreign key definition that implicitly provides the constraint. The ON UPDATE and ON DELETE modifiers allow for such actions as cascading deletes through the linked records rather than triggering errors. So in the example @AsHex_66 provides, deleting an artist who has linked tracks should generate the error.

---

<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 20:25 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/27 "2019-10-12T20:25:50Z")

</div>

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 above explains.

Here is the flow , sorry no comments in the flow since I’m away from my computer

```auto
[{"id":"bc584f19.2e6128","type":"tab","label":"SQLITE TEST","disabled":false,"info":""},{"id":"95efe235.9ee4","type":"inject","z":"bc584f19.2e6128","name":"CREATE TABLE artist","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":220,"wires":[["a7b54bbb.bcd678"]]},{"id":"28970b24.c21bbc","type":"sqlite","z":"bc584f19.2e6128","mydb":"e839dd59.715d78","sqlquery":"msg.topic","sql":"PRAGMA foreign_keys = ON;\ndelete from artist\nwhere artistid = 3","name":"","x":740,"y":340,"wires":[["d3f2d01a.7d7138"]]},{"id":"17338de8.96634a","type":"comment","z":"bc584f19.2e6128","name":"Data Base example inside-- ","info":"-- DataBase example\n\nCREATE TABLE artist(\n artistid INTEGER PRIMARY KEY, \n artistname TEXT\n);\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)","x":180,"y":160,"wires":[]},{"id":"ed1dc042.ddc0b8","type":"catch","z":"bc584f19.2e6128","name":"","scope":null,"uncaught":false,"x":200,"y":80,"wires":[["1603f487.c5a72b"]]},{"id":"d3f2d01a.7d7138","type":"debug","z":"bc584f19.2e6128","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1030,"y":340,"wires":[]},{"id":"a7b54bbb.bcd678","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE artist(\n artistid INTEGER PRIMARY KEY, \n artistname TEXT\n);\n","output":"str","x":360,"y":220,"wires":[["28970b24.c21bbc","77521801.50b35"]]},{"id":"77521801.50b35","type":"debug","z":"bc584f19.2e6128","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":710,"y":220,"wires":[]},{"id":"1603f487.c5a72b","type":"debug","z":"bc584f19.2e6128","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":470,"y":80,"wires":[]},{"id":"ad60cc5d.df06b8","type":"inject","z":"bc584f19.2e6128","name":"DROP TABLE artist","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":560,"wires":[["5ade94e2.c253cc"]]},{"id":"5ade94e2.c253cc","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DROP TABLE artist;","output":"str","x":360,"y":560,"wires":[["28970b24.c21bbc"]]},{"id":"2dba2d29.d52372","type":"inject","z":"bc584f19.2e6128","name":"CREATE TABLE track","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":260,"wires":[["76bd86bc.0f347"]]},{"id":"76bd86bc.0f347","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE track(\n trackid INTEGER,\n trackname TEXT, \n trackartist INTEGER,\n FOREIGN KEY (trackartist) REFERENCES artist (artistid)\n );","output":"str","x":360,"y":260,"wires":[["28970b24.c21bbc"]]},{"id":"f4782433.a02bd8","type":"inject","z":"bc584f19.2e6128","name":"DROP TABLE track","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":600,"wires":[["a942d7f5.bb758"]]},{"id":"a942d7f5.bb758","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DROP TABLE track;","output":"str","x":360,"y":600,"wires":[["28970b24.c21bbc"]]},{"id":"e80993b8.748c38","type":"inject","z":"bc584f19.2e6128","name":"INSERT INTO artist","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":320,"wires":[["e5d555c2.17fd6"]]},{"id":"e5d555c2.17fd6","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO artist(artistid, artistname)\nVALUES\n (1,'abel'),\n (2,'paul')\n;\n","output":"str","x":360,"y":320,"wires":[["28970b24.c21bbc"]]},{"id":"69c2ce26.cc6ab8","type":"inject","z":"bc584f19.2e6128","name":"INSERT INTO track","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":360,"wires":[["389e7ac5.d267ee"]]},{"id":"389e7ac5.d267ee","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO track(trackid, trackname, trackartist)\nVALUES\n (1,'minecraft love songs', 1),\n (2,'I got the nether blues', 1),\n (3,'I forgot my sword and now I face a horde', 1),\n (1, 'The whats for dinner blues',2),\n (2, 'no more screen time scream',2),\n (3, 'flying...oops no wings',2)\n;\n","output":"str","x":360,"y":360,"wires":[["28970b24.c21bbc"]]},{"id":"b0295459.6123b8","type":"inject","z":"bc584f19.2e6128","name":"SELECT * FROM artist","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":440,"wires":[["6d5874ef.b628c4"]]},{"id":"6d5874ef.b628c4","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT * FROM artist;\n","output":"str","x":360,"y":440,"wires":[["28970b24.c21bbc"]]},{"id":"ed04c11b.36af28","type":"inject","z":"bc584f19.2e6128","name":"SELECT * FROM track","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":480,"wires":[["804ad2dc.5d5018"]]},{"id":"804ad2dc.5d5018","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT * FROM track;","output":"str","x":360,"y":480,"wires":[["28970b24.c21bbc"]]},{"id":"fcb29800.2f48d","type":"inject","z":"bc584f19.2e6128","name":"PRAGMA foreign_keys;","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":700,"wires":[["76c5e614.9393"]]},{"id":"76c5e614.9393","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"PRAGMA foreign_keys;","output":"str","x":360,"y":700,"wires":[["28970b24.c21bbc"]]},{"id":"96d0956.80839e8","type":"inject","z":"bc584f19.2e6128","name":"PRAGMA foreign_keys = ON;","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":760,"wires":[["ddb4e953.a2dd98"]]},{"id":"ddb4e953.a2dd98","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"PRAGMA foreign_keys = ON;","output":"str","x":420,"y":760,"wires":[["28970b24.c21bbc"]]},{"id":"31d14a94.6b2c6e","type":"inject","z":"bc584f19.2e6128","name":"DELETE FROM artist","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":740,"y":480,"wires":[["440b3750.f38c88"]]},{"id":"440b3750.f38c88","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DELETE FROM artist\nWHERE artistname = 'paul'\n;","output":"str","x":940,"y":480,"wires":[["28970b24.c21bbc"]]},{"id":"188d352d.1e291b","type":"inject","z":"bc584f19.2e6128","name":"CREATE TABLE artist","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":820,"y":80,"wires":[["11dc1fc1.bdc23"]]},{"id":"11dc1fc1.bdc23","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE artist(\n artistid INTEGER PRIMARY KEY, \n artistname TEXT\n);\n","output":"str","x":1020,"y":80,"wires":[["28970b24.c21bbc"]]},{"id":"9fb7c2c1.062708","type":"inject","z":"bc584f19.2e6128","name":"CREATE TABLE track","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":820,"y":120,"wires":[["eda2dce2.32767"]]},{"id":"eda2dce2.32767","type":"template","z":"bc584f19.2e6128","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE track(\n trackid INTEGER,\n trackname TEXT, \n trackartist INTEGER,\n FOREIGN KEY (trackartist) \n REFERENCES artist (artistid)\n on delete cascade\n );","output":"str","x":1020,"y":120,"wires":[["28970b24.c21bbc"]]},{"id":"e839dd59.715d78","type":"sqlitedb","z":"","db":"/tmp/test2.db","mode":"RWC"}]

```

---

<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 20:43 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/28 "2019-10-12T20:43:28Z")

</div>

I believe that @AsHex_66 does not want the record to be deleted, he/she wants it to throw an error. That is what I understood anyway.

---

<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 21:50 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/29 "2019-10-12T21:50:02Z")

</div>

@colin, You are correct. The record should not be deleted as there is a foreign key constraint.  
@zenofmud In the example I gave, the SQL code is in the comment.

I did try your example, and the problem was that I did not send the pragma command individually.  
I did combine that in the command. When separated, it works well. Thanks for your help.

(Do you sleep zenofmud ??? 😁 you appear to be online 24-7)

Thanks, appreciated...

---

<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 22:06 UTC](https://discourse.nodered.org/t/sqlite-foreign-key-ignored/16624/30 "2019-10-12T22:06:41Z")

</div>

Well It seems to me you should use ON DELETE RESTRICT

> RESTRICT
> 
> The `RESTRICT` action does not allow you to change or delete values in the parent key of the parent table.

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