gpt4 book ai didi

mysql - 我无法填充我的 MySQL 表

转载 作者:行者123 更新时间:2023-11-29 06:07:51 26 4
gpt4 key购买 nike

我在 MySQL 数据库上创建了 3 个表并想填充它们,但我有一个异常(exception)。在这里:

Duplicate entry '1' for key 'unique_idx'

也许有人可以告诉我我做错了什么?

初始数据库:

DROP TABLE IF EXISTS user_roles;
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS users;

CREATE TABLE IF NOT EXISTS users
(
id INTEGER AUTO_INCREMENT,
login VARCHAR(45) NOT NULL,
password VARCHAR(45) NOT NULL,
full_name VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX users_unique_login_idx ON users (login);

CREATE TABLE IF NOT EXISTS user_roles
(
user_id INTEGER NOT NULL,
role VARCHAR(45),
CONSTRAINT user_roles_idx UNIQUE (user_id, role),
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS contacts (
id INTEGER AUTO_INCREMENT,
user_id INTEGER NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
patronymic VARCHAR(45) NOT NULL,
mobile_phone_number VARCHAR(15),
home_phone_number VARCHAR(15),
address VARCHAR(45),
email VARCHAR(30),
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX unique_idx ON contacts (user_id);

人口数据库:

DELETE FROM lardi.users;
DELETE FROM lardi.user_roles;
DELETE FROM lardi.contacts;

INSERT INTO lardi.users
(login,password,full_name) VALUES
('Bill', '112233', 'user'),
('John', '112233', 'user'),
('Mark', '112233', 'user');

INSERT INTO lardi.user_roles
(role,user_id) VALUES
('USER_ROLE',1),
('USER_ROLE',2),
('ADMIN_ROLE',3);

INSERT INTO lardi.contacts
(first_name, last_name, patronymic, mobile_phone_number, home_phone_number, address, email, user_id) VALUES
('Bill','Gates','','+380(66)1234567','','USA','bill@gmail.com', 1),
('Mark','Zukenberg','','+380(66)9876543','+380(44)1122334','USA','mark@gmail.com', 1),
('Barak','Obama','','+380(99)1234567','','USA','barak@gmail.com', 1),
('Michel','Obama','','+380(99)9876543','','USA','michel@gmail.com', 1),
('David','Camaron','','+380(50)5557799','+380(44)0000009','UK','david@gmail.com', 2),
('Steve','Jobs','','+380(00)1100999','','USA','steve@gmail.com', 2),
('Tim','Kuk','','+380(00)2244888','','USA','tim@gmail.com', 2),
('Jim','Carry','','+380(69)8881188','+380(44)1111119','USA','jim@gmail.com', 3),
('David','Backham','','+380(67)90000001','','UK','david@gmail.com', 3);

感谢您的帮助。

最佳答案

删除你的唯一索引

CREATE UNIQUE INDEX unique_idx ON contacts (user_id);

contactsuser_id 是唯一的。然后你不能插入重复数据到user_id

关于mysql - 我无法填充我的 MySQL 表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/40257285/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com