SQLite3 UNIQUE约束失败错误
我正在尝试创建一个数据库,允许用户创建待办事项列表,并在其中填写要完成的项目。但是,当将数据插入表中时,它会给我一个UNIQUE约束失败错误,并且我不知道如何解决。这是我用于创建数据库和插入数据的代码。
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);
我已复制以下错误:
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
任何帮助将不胜感激!
您已将list_id设置为列表上的主键表,这意味着每个记录的值必须唯一。因此,尝试使用同一list_id表插入多个记录会导致错误。
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.
项目表的问题相同。