SQL SQLite3 UNIQUE 约束失败错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35415469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite3 UNIQUE constraint failed error
提问by LEJ
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.
我正在尝试创建一个数据库，它允许用户创建“待办事项”列表并用要完成的项目填充它们。但是，当将数据插入表时，它给了我一个 UNIQUE 约束失败错误，我不知道如何解决它。这是我创建数据库和插入数据的代码。
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!
任何帮助，将不胜感激！
采纳答案by Dan O'Leary
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.
您已将 list_id 设置为列表表上的主键，这意味着每个记录的值必须是唯一的。因此，尝试使用相同的 list_id 表插入多条记录会导致错误。
The issue is the same for the item table.
项目表的问题是相同的。
回答by Natesh bhat
You get a UNIQUE constraint failederror when the data that you are inserting has an entry which is already in the corresponding columnof the table that you are inserting into.
UNIQUE constraint failed当您插入的数据有一个条目已经在您插入的表的相应列中时，您会收到错误消息。
If you want SQL to IGNORE that error and continue adding other records, then do this :
如果您希望 SQL忽略该错误并继续添加其他记录，请执行以下操作：
INSERT or IGNORE into tablename VALUES (value1,value2 , so on );
If you want to replace the values in the tablewhenever 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.
这可以节省您的大量处理工作，并且非常有用。
