如何使用insert语句在子表中添加或插入外键值
如何使用insert语句在子表中添加或插入外键值
提问于 2022-01-17 20:24:33
我想在子表中使用父表的主键。我创建了一个外键来连接问答表。以下是我的数据库代码：
用户表
CREATE TABLE user (
user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- unique index
username VARCHAR(255) NOT NULL,
password VARBINARY(255) NOT NULL, -- password hash, binary
firstname VARCHAR(255) NOT NULL,
lastname VARCHAR(255) DEFAULT NULL,
email VARCHAR(255) DEFAULT NULL,
address VARCHAR(255) DEFAULT NULL,
phone BIGINT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE question (
question_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- unique index
questions TEXT NOT NULL,
question_text TEXT NOT NULL,
user_id INT NOT NULL, -- id of the user who have asked
CONSTRAINT user_to_question FOREIGN KEY (user_id) REFERENCES user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE answer (
answer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- unique index
answer_text TEXT NOT NULL,
question_id INT NOT NULL, -- what question it answers on
user_id INT NOT NULL, -- id of the user who have answered
CONSTRAINT user_to_answer FOREIGN KEY (user_id) REFERENCES user (user_id),
CONSTRAINT question_to_answer FOREIGN KEY (question_id) REFERENCES question (question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;因此，在USER表中，我添加了一个新值：
insert into user values(1,"krish","123456789","Krish","D","krish@gmail.com","Mumbai MH","98776");现在，在质问表中，我想添加一个新值，并希望使用来自USER表的user_id值。因为user_id是试题表中的外键。因此，到目前为止，我已经尝试过这个查询，但它无法工作。
insert into question (question_id,questions,question_text)
SELECT user_id
FROM user
where user_id = 1;
values (1,"What is java","Please Explain in details");我已经提出了这个问题和解决办法，但却行不通。inserting data from parent table to child table in postgres
回答 2
Stack Overflow用户
回答已采纳
发布于 2022-01-17 21:36:09
您正在尝试的查询如下：
insert into question ( question_id,
questions,
question_text
)
SELECT user_id as question_id,
"What is java" as questions,
"Please Explain in details" as question_text
FROM user
where user_id = 1 ;但据我所见，您不能只插入这个值，因为
CREATE TABLE answer (
answer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
answer_text TEXT NOT NULL,
question_id INT NOT NULL,
user_id INT NOT NULL, 您有user_id INT NOT NULL，因此它需要一个值。
类似于：
insert into question ( question_id,
questions,
question_text,
user_id
)
SELECT user_id as question_id,
"What is java" as questions,
"Please Explain in details" as question_text,
user_id
FROM user
where user_id = 1 ;Stack Overflow用户
发布于 2022-01-18 16:26:48
如果您知道您从user_id表中选择的user，则根本不需要SELECT，只需将其放在VALUES列表中即可。
INSERT INTO question (question_id,questions,question_text, user_id)
VALUES (1,"What is java","Please Explain in details", 1);如果需要使用其他列查找user_id，则可以使用user_id。在这种情况下，可以将固定值添加到SELECT列表中。
INSERT INTO question (question_id,questions,question_text, user_id)
SELECT 1,"What is java","Please Explain in details", user_id
FROM users
WHERE username = 'krish';如果在创建用户之后立即执行此操作，还可以使用LAST_INSERT_ID()函数获取使用自动增量分配的user_id。
INSERT INTO question (question_id,questions,question_text, user_id)
VALUES (1,"What is java","Please Explain in details", LAST_INSERT_ID());页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/70747120
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号