Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

sql - SQLite3 UNIQUE constraint failed error

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...