SQLite3 UNIQUE constraint failed error

Question

I am trying to create a database which allows users to create 'to do' lists and fill them with items to complete. However, when inserting data into the tables it gives me a UNIQUE constraint failed error and I don't know how to solve it. This is my code for creating the database and inserting data.

CREATE TABLE user (
    user_id integer  NOT NULL   PRIMARY KEY,
    first_name varchar(15)  NOT NULL,
    title varchar(5)  NOT NULL,
    username varchar(15)  NOT NULL,
    password varchar(20)  NOT NULL,
    email varchar(50)  NOT NULL,
    bio text  NOT NULL
);


CREATE TABLE list (
    list_id integer  NOT NULL   PRIMARY KEY,
    list_name varchar(10)  NOT NULL,
    user_user_id integer  NOT NULL,
    FOREIGN KEY (user_user_id) REFERENCES user(user_id)
);


CREATE TABLE item (
    item_id integer  NOT NULL   PRIMARY KEY,
    item text  NOT NULL,
    completed boolean  NOT NULL,
    list_list_id integer  NOT NULL,
    FOREIGN KEY (list_list_id) REFERENCES list(list_id)
);


-- Data:
INSERT INTO user VALUES (1, "Name1", "Title1", "Username1", "Password1", "Email1", "Bio1");
INSERT INTO user VALUES (2, "Name2", "Title2", "Username2", "Password2", "Email2", "Bio2");
INSERT INTO user VALUES (3, "Name3", "Title3", "Username3", "Password3", "Email3", "Bio3");

INSERT INTO list VALUES (1, "user1-list1", 1);
INSERT INTO list VALUES (2, "user1-list2", 1);
INSERT INTO list VALUES (3, "user1-list3", 1);
INSERT INTO list VALUES (1, "user2-list1", 2);
INSERT INTO list VALUES (1, "user3-list1", 3);
INSERT INTO list VALUES (2, "user3-list2", 3);

INSERT INTO item VALUES (1, "user1-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user1-list1-item2", "FALSE", 1);
INSERT INTO item VALUES (1, "user1-list2-item1", "FALSE", 2);
INSERT INTO item VALUES (1, "user1-list3-item1", "FALSE", 3);
INSERT INTO item VALUES (2, "user1-list3-item2", "FALSE", 3);
INSERT INTO item VALUES (1, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list3-item1", "FALSE", 2);

I have copied the errors I receive below:

Error: near line 43: UNIQUE constraint failed: list.list_id
Error: near line 44: UNIQUE constraint failed: list.list_id
Error: near line 45: UNIQUE constraint failed: list.list_id
Error: near line 49: UNIQUE constraint failed: item.item_id
Error: near line 50: UNIQUE constraint failed: item.item_id
Error: near line 51: UNIQUE constraint failed: item.item_id
Error: near line 52: UNIQUE constraint failed: item.item_id
Error: near line 53: UNIQUE constraint failed: item.item_id
Error: near line 54: UNIQUE constraint failed: item.item_id
Error: near line 55: UNIQUE constraint failed: item.item_id

Any help would be appreciated!

score 80 · Answer 1 · edited Jul 25 '17 at 11:11

80

You get a UNIQUE constraint failed error when the data that you are inserting has an entry which is already in the corresponding column of the table that you are inserting into.

If you want SQL to IGNORE that error and continue adding other records , then do this :

INSERT or IGNORE into tablename VALUES (value1,value2 , so on );

If you want to replace the values in the table whenever the entry already exists , then do this:

INSERT or REPLACE into tablename VALUES (value1,value2 , so on );

This saves lot of processing on your part and quite useful.

edited Jul 25 '17 at 11:11

Federico Pellegatta

3,977
1
17
29

answered Jul 25 '17 at 10:01

Natesh bhat

12,274
10
84
125

I have tried with the "INSERT or IGNORE" too but, the issue I was facing is the new record with repeated value was not getting added only. What might be an issue? – mukhtar alam Oct 13 '20 at 09:35
And what if this error is unexpected? How do you see which data is causing the error? – Daniel Kaplan Mar 07 '23 at 09:21

score 29 · Accepted Answer · answered Feb 15 '16 at 17:36

29

You have set list_id to be the primary key on the list table, which means that value must be unique for each record. Trying to insert multiple records with the same list_id table is therefore causing the error.

The issue is the same for the item table.

answered Feb 15 '16 at 17:36

Dan O'Leary

2,660
6
24
50

2

So what should I use as the primary key in each table? – LEJ Feb 15 '16 at 17:48
1

Usually, the primary key is generated for you by sql. To create an auto-incremented primary key, you can just add AUTOINCREMENT after PRIMARY KEY. You then won't need to actually specify a value for the key (ie only insert values for the other columns). – Dan O'Leary Feb 15 '16 at 17:57
1

@DanO'Leary You get an [autoincrementing column](http://www.sqlite.org/autoinc.html) even without AUTOINCREMENT. – CL. Feb 15 '16 at 18:49
1

It is so confuse to me the item cannot have same foreign key by default!? – Cheung Jul 26 '17 at 05:31

SQLite3 UNIQUE constraint failed error

2 Answers2

Linked

Related