# SQLite Error: UNIQUE constraint failed
**URL:** https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758
**Category:** General
**Created:** [6 April 2021 11:21 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758 "2021-04-06T11:21:17Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 11:21 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/1 "2021-04-06T11:21:17Z")
Good day everyone,
i dont know where is false that i cannot show infos in DB in my tempelate.
(i explain that i want to insert my csv.file to DB and then show Infos from DB)
```auto
[{"id":"68a0a347.56efbc","type":"tab","label":"Flow 6","disabled":false,"info":""},{"id":"7ba892f.dae406c","type":"debug","z":"68a0a347.56efbc","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":970,"y":200,"wires":[]},{"id":"24833298.0ddbae","type":"inject","z":"68a0a347.56efbc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":200,"wires":[["8668a74b.1bb218"]]},{"id":"8668a74b.1bb218","type":"file in","z":"68a0a347.56efbc","name":"csvfile","filename":"C:\\Users\\samira.talebi\\example\\chart test80.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":510,"y":80,"wires":[["a534d010.18f43"]]},{"id":"a534d010.18f43","type":"csv","z":"68a0a347.56efbc","name":"","sep":",","hdrin":true,"hdrout":"all","multi":"one","ret":"\\n","temp":"Data,Time,Temperature","skip":"0","strings":true,"include_empty_strings":"","include_null_values":true,"x":590,"y":140,"wires":[["7ba892f.dae406c","75faf199.d8861"]]},{"id":"98c15b4d.bcec28","type":"inject","z":"68a0a347.56efbc","name":"create database table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE STAR ( DATA TXT NOT NULL , TIME TXT NOT NULL, TEMPERATURE FLOAT NOT NULL)","payload":"","payloadType":"date","x":260,"y":320,"wires":[["219cb6f3.e7708a"]]},{"id":"219cb6f3.e7708a","type":"sqlite","z":"68a0a347.56efbc","mydb":"b4b8d6bc.ef48c8","sqlquery":"msg.topic","sql":"INSERT or IGNORE INTO STAR (Data, Time, Temperature) VALUES ($Data, $Time, $Temperature)","name":"firstDB","x":710,"y":380,"wires":[["7fb4e458.255d3c"]]},{"id":"75faf199.d8861","type":"function","z":"68a0a347.56efbc","name":"firstDB","func":" var payload=msg.payload;\n\n var Data= payload[\"Data\"];\n var Time= payload[\"Time\"];\n var Temperature= payload[\"Temperature\"];\n node.warn( [Data, Time, Temperature] ); //check debug sidebar\nquery= \" values (\"+ \"\\'\"+Data+\"\\',\"+\"\\'\"+Time+\"\\',\"+\"\\'\"+Temperature+\"\\'\" +\")\";\nmsg.topic=\"INSERT INTO STAR (DATA, TIME, TEMPERATURE)\"+ query ;\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":490,"y":260,"wires":[["3ceab86e.0c6678","219cb6f3.e7708a"]]},{"id":"7fb4e458.255d3c","type":"debug","z":"68a0a347.56efbc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":970,"y":280,"wires":[]},{"id":"3ceab86e.0c6678","type":"debug","z":"68a0a347.56efbc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":970,"y":400,"wires":[]},{"id":"918c6da6.1294","type":"inject","z":"68a0a347.56efbc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"7","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM STAR ORDER BY TIME ASC LIMIT 10;","payload":"","payloadType":"date","x":450,"y":560,"wires":[["732661bd.0e9f6"]]},{"id":"732661bd.0e9f6","type":"sqlite","z":"68a0a347.56efbc","mydb":"b4b8d6bc.ef48c8","sqlquery":"msg.topic","sql":"","name":"firstDB","x":630,"y":540,"wires":[["25509790.d08518","1b59f45a.e0930c"]]},{"id":"25509790.d08518","type":"ui_template","z":"68a0a347.56efbc","group":"bbf9c10f.b099","name":"","order":3,"width":0,"height":0,"format":"\n\n\n\n
\n \n | Index | \n Data | \n Time | \n Temperature | \n
\n \n | {{$index}} | \n {{msg.payload[$index].Data}} | \n {{msg.payload[$index].Time}} | \n {{msg.payload[$index].Temperature}} | \n
\n
\n
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [6 April 2021 12:29 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/2 "2021-04-06T12:29:21Z")
The error says that you are trying to add a record to a table with a value for a field that is defined as UNIQUE with the same value as one already in the database. I would expect the error to tell you which field that is. If you then look at the data you are trying to insert you should be able to see the problem.
If you can't see us the problem then show us the full error message, the database schema (column names and types) and what a debug node shows when displaying the data going into the database node.
Is this a continuation of your [previous thread](https://discourse.nodered.org/t/insert-csv-file-to-sqlite/42216/19)?
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [6 April 2021 12:49 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/3 "2021-04-06T12:49:43Z")
How were you able to create a table using your create statement??? `TXT` is NOT a valid sqlite data type.
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 12:55 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/4 "2021-04-06T12:55:40Z")
Hi Colin,
tnx for your Replay. Yes, it is a Continuation of previous thread , i wrote in new topic because i could not write in previous Topic.
Yes correct. i understand the meanning of error but i didnt set Primary key and this Error is comming from TIME column of my csv file. Column Time has not same value in my table.
but now i inserted "INSERT or IGNORE INTO table.name" and there ist not error more.
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 13:03 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/5 "2021-04-06T13:03:33Z")
> [@zenofmud](#):
>
> How were you able to create a table using your create statement??? `TXT` is NOT a valid sqlite data type.
Hi zenofmud,
my csvfile out put is utf8. According to "[Datatypes In SQLite](https://sqlite.org/datatype3.html)" i can use txt datatype.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [6 April 2021 13:04 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/6 "2021-04-06T13:04:41Z")
> [@Samira05](#):
>
> i can use txt datatype.
Where in there does it mention TXT? It does describe TEXT type.
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 13:13 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/7 "2021-04-06T13:13:22Z")
do you mean my error is here? i have set my data and time as text because i got with INT another errors and i saw in some Tutorials that they used TXT.
i edited txt and wrote TEXT but i got same error.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [6 April 2021 13:19 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/8 "2021-04-06T13:19:26Z")
> [@Samira05](#):
>
> i edited txt and wrote TEXT but i got same error.
Did you delete and recreate the table?
Show us the schema for the table. If you don't know how to do that, this link shows how. [Practical SQLite Commands That You Don't Want To Miss](https://www.sqlitetutorial.net/sqlite-commands/)
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [6 April 2021 13:53 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/9 "2021-04-06T13:53:13Z")
Please add the following node to your flow and connect it to your `sqlite` node:
```auto
[{"id":"d33d8a9.eb8f2f8","type":"inject","z":"68a0a347.56efbc","name":"display CREATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT sql FROM sqlite_master WHERE name='STAR';","payload":"","payloadType":"date","x":110,"y":360,"wires":[["219cb6f3.e7708a"]]}]
```
this will create the 'CREATE' command you used to define the database. Please show the `debug` output from this.
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 14:02 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/10 "2021-04-06T14:02:14Z")
i do it . aber before that please say me , how can i see my database by cmd. i installed Sqlite3 and run cmd but when i want to see database , that dont show. it mean my DB is not build up.
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [6 April 2021 14:05 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/11 "2021-04-06T14:05:08Z")
If you want to use CLI to look at the database, I suggest you take an sqlite tutorial. There are many on the internet and I'm sure you can google some and find them.
Meanwhile, it should take you less than 5 minutes to do what I've asked you do so we can see how you created your database and hopefully determine what your problem is.
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 14:10 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/12 "2021-04-06T14:10:31Z")
i thank you zenofmud. i wanted to see , do i build sqlite database or not . therefore i said at frist i want to do it. but ok i add your code to my programm and say you what is happend.
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 14:33 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/13 "2021-04-06T14:33:11Z")
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [6 April 2021 14:41 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/14 "2021-04-06T14:41:04Z")
You have not displayed the output of that command, please disable the inject node that you have set to automatically run at an interval
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 14:52 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/15 "2021-04-06T14:52:55Z")
Do you mean output of new command, yes?
---
### Author:  [@UnborN](https://discourse.nodered.org/u/UnborN)
#### Post date: [6 April 2021 14:59 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/16 "2021-04-06T14:59:33Z")
Expand the object and the sql in order to see the structure of the database in the msg reply
For Example (from my db) :
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [6 April 2021 15:09 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/17 "2021-04-06T15:09:06Z")
Great, now that is NOT the SQL you used in the flow to create the table, When did you change it?
It does explains some of what is happening.
Can you tell me what you think `TIME INT PRIMARY KEY NOT NULL` means? (You may want to look it up)
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 15:18 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/18 "2021-04-06T15:18:36Z")
now i got it. At frist i set Time and date as INT and primary key. then i read in net and look some of codes and decision to change my data type but i didnt know that i must delete and recreate(Oh my God) .I did such as some tutorials but i dont know why is happend ☹
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [6 April 2021 15:32 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/19 "2021-04-06T15:32:45Z")
So I'm unsure of where you stand right now, Do you know what your error is and this issue is resolved or do you still have a question? If so, what is your question?
---
### Author:  [@Samira05](https://discourse.nodered.org/u/Samira05)
#### Post date: [6 April 2021 15:37 UTC](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758/20 "2021-04-06T15:37:16Z")
now, i know i must my table delete and recreate again. Can u say me, please, how can i see or know is there my database or how many database have i ? without using CLI.
i try to check my codes. i hope it is done.
[Next page](https://discourse.nodered.org/t/sqlite-error-unique-constraint-failed/43758.md?page=2)