# SQLITE CONSTRAINT Error

**URL:** <https://discourse.nodered.org/t/sqlite-constraint-error/29649>\
**Category:** General\
**Created:** [7 July 2020 11:12 UTC](https://discourse.nodered.org/t/sqlite-constraint-error/29649 "2020-07-07T11:12:45Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [7 July 2020 11:12 UTC](https://discourse.nodered.org/t/sqlite-constraint-error/29649/1 "2020-07-07T11:12:45Z")

</div>

Good Day Everone,

I have an unfamiliar issue that appears out of the blue and I have no reason why.

When we have built the flow last night, it all worked fine, and this morning after redstart the flow just seems not to right to the SQL DB table due to an SQLITE\_CONSTRAINT\_Error .but nothing has changed ( NOTHING )

I have deleted the flow and imported the backup confirmed working flow that passed the data successfully to the db last night.

the Error from the **SQLite node** :

```auto
Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: accreq.DATE

```

here with the **Function node** code / values that right to the... **SQLite node**

```auto
tvar now = (new Date()).toISOString().slice(0,-5);
var dateParts = now.split("T");

var door_id= msg.payload["psa001/d1/user_input "]["Door ID"];
var door_otp= msg.payload["psa001/d1/user_input "].OTP;
var hashkey= msg.payload.accreq;
var _msgid= msg._msgid;
var date = dateParts[0];
var time = dateParts[1];

//create SQL command
msg.topic = `INSERT INTO ACCREQ (
  DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY
) values (
   '${date}', '${time}', '${_msgid}', ${door_id}, ${door_otp} ,'${hashkey}');`
return msg;ype or paste code here

```

What I don't understand is why it worked perfectly last night and now it complains about a missing date value that is clearly passed to the SQL node or am I missing something hare ??

Here is the complete flow ( Or the ones that matter )

```auto
[{"id":"2c2bad1c.7298d2","type":"mqtt in","z":"6aaa3f67.0cbdf","name":"PSA001 - QR Value","topic":"accreq","qos":"0","datatype":"auto","broker":"57c985f0.a39e8c","x":270,"y":300,"wires":[["a759afa8.e4e22"]]},{"id":"f94cb424.90bce8","type":"mqtt in","z":"6aaa3f67.0cbdf","name":"PSA001 - OTP","topic":"psa001/d1/user_input ","qos":"0","datatype":"json","broker":"57c985f0.a39e8c","x":260,"y":460,"wires":[["a759afa8.e4e22"]]},{"id":"ce08ea1f.168018","type":"function","z":"6aaa3f67.0cbdf","name":"PSA DB - Insert - ACCREQ","func":"var now = (new Date()).toISOString().slice(0,-5);\nvar dateParts = now.split(\"T\");\n\nvar door_id= msg.payload[\"psa001/d1/user_input \"][\"Door ID\"];\nvar door_otp= msg.payload[\"psa001/d1/user_input \"].OTP;\nvar hashkey= msg.payload.accreq;\nvar _msgid= msg._msgid;\nvar date = dateParts[0];\nvar time = dateParts[1];\n\n\n//create SQL command\nmsg.topic = `INSERT INTO ACCREQ (\n DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY\n) values (\n '${date}', '${time}', '${_msgid}', ${door_id}, ${door_otp} ,'${hashkey}');`\nreturn msg;","outputs":1,"noerr":0,"x":860,"y":380,"wires":[["5d519068.a4712","2673434c.d6c74c"]]},{"id":"a759afa8.e4e22","type":"join","z":"6aaa3f67.0cbdf","name":"Wait and Join ","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":580,"y":380,"wires":[["ce08ea1f.168018"]]},{"id":"5d519068.a4712","type":"debug","z":"6aaa3f67.0cbdf","name":"Insert data debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1210,"y":300,"wires":[]},{"id":"2673434c.d6c74c","type":"sqlite","z":"6aaa3f67.0cbdf","mydb":"9db34194.66aca","sqlquery":"msg.topic","sql":"","name":"PSA-DB ","x":1180,"y":380,"wires":[["f90aaa9d.95c438"]]},{"id":"f90aaa9d.95c438","type":"debug","z":"6aaa3f67.0cbdf","name":"Response from DB ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1430,"y":380,"wires":[]},{"id":"57c985f0.a39e8c","type":"mqtt-broker","z":"","name":"PSA Server 1","broker":"localhost ","port":"1883","clientid":"psa003","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"9db34194.66aca","type":"sqlitedb","z":"","db":"psa.db","mode":"RWC"}]

```

I would really appreciate your help, Thank you in advance

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 July 2020 11:30 UTC](https://discourse.nodered.org/t/sqlite-constraint-error/29649/2 "2020-07-07T11:30:12Z")

</div>

Hi again.

> [@Peter](#):
>
> `UNIQUE constraint failed`

Looks like you have a primary key or UNIQUE set on the date field & tried to write data with a same DATE as is already in the database.

See [here](https://www.sqlitetutorial.net/sqlite-unique-constraint/#:~:text=You%20can%20define%20a%20UNIQUE,UNIQUE%20constraint%20across%20multiple%20columns.&text=Once%20a%20UNIQUE%20constraint%20is,error%20and%20abort%20the%20operation.)

The solution is to understand what you _need_ to be primary or unique and fix your database (node-red is not likely to be the issue here)

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [7 July 2020 11:33 UTC](https://discourse.nodered.org/t/sqlite-constraint-error/29649/3 "2020-07-07T11:33:39Z")

</div>

Hi Steve,

Thanks for your response . i am sure its not Node red ,

so , you saying the " Primary Key value " is already exciting , thus it dont allow a duplicated ?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 July 2020 11:41 UTC](https://discourse.nodered.org/t/sqlite-constraint-error/29649/4 "2020-07-07T11:41:32Z")

</div>

> [@Peter](#):
>
> is already exciting , thus it dont allow a duplicated

Primary key == new data row **must be different**

I cant help you here as I am not 100% on sqlite

If you explain what uniqueness or constraints on which field(s) you need - I am certain someone else can assist. And if you want a quick reply, you would be wise to also post your database table schema like this...

```auto
CREATE TABLE myTable (
    ...,
    DATE type UNIQUE,
    ... etc 
    ... etc 
);

```

so that people who know SQLITE will be easily able to help you.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [7 July 2020 12:26 UTC](https://discourse.nodered.org/t/sqlite-constraint-error/29649/5 "2020-07-07T12:26:27Z")

</div>

HI Steve ,

OK , again you where right as always. It was the PRIMARY KEY that was set to the Data collum ,

so i have dropped the table and created a new table exactly but changed the Primary key to the msg\_id ,

Thanks again for your help  
and now its working like a charm , every time after time . have done a couple of tests and all good so far

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 July 2020 12:26 UTC](https://discourse.nodered.org/t/sqlite-constraint-error/29649/6 "2020-07-21T12:26:30Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.