英文:
How to autoincrement ID value when there are 2 primary keys in an SQL table?
问题
我正在制作一个包括帖子和评论的社交媒体应用，因此我创建了Post和Comment表。Comment是一个弱实体，因此它有两个主键：comment_id和post_id。
在创建一条帖子的评论时，comment_id始终显示为None。我该如何为comment_id分配自动增加的值？
CREATE TABLE Post(
post_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title VARCHAR(100),
content TEXT,
date DATETIME,
FOREIGN KEY (user_id) REFERENCES NonAdmin(user_id)
);
CREATE TABLE Comment (
comment_id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER,
user_id INTEGER NOT NULL,
content TEXT,
date DATETIME,
FOREIGN KEY (user_id) REFERENCES NonAdmin(user_id),
FOREIGN KEY (post_id) REFERENCES Post(post_id)
);
英文:
I am making a social media app that includes posts and comments, therefore I created Post and Comment tables. Comment is a weak entity, so that it has 2 primary keys: comment_id and post_id.
When creating a comment on a post, comment_id always appears as None. How can I assign auto incremented values to comment_id?
CREATE TABLE Post(
post_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title VARCHAR(100),
content TEXT,
date DATETIME,
FOREIGN KEY (user_id) REFERENCES NonAdmin(user_id)
);
CREATE TABLE Comment (
comment_id INTEGER,
post_id INTEGER,
user_id INTEGER NOT NULL,
content TEXT,
date DATETIME,
PRIMARY KEY (comment_id, post_id),
FOREIGN KEY (user_id) REFERENCES NonAdmin(user_id),
FOREIGN KEY (post_id) REFERENCES Post(post_id)
);
答案1
得分: 3
你应该将comment_id设为主键，如果你想要一个规范化的表格。如果你想将相同的评论分配给多篇文章，那么你应该创建第三个带有适当关系的表格。然而，我不明白为什么相同的评论要附加到不同的文章上。
CREATE TABLE Comment (
comment_id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER,
user_id INTEGER NOT NULL,
content TEXT,
date DATETIME,
FOREIGN KEY (user_id) REFERENCES NonAdmin(user_id),
FOREIGN KEY (post_id) REFERENCES Post(post_id)
);
英文:
You should have only comment_id as your primary key if you want to have normalized table. If you want to assign the same comment to many posts, then you should create 3rd table with the proper relations. However, I don't see the reason why the same comment should be attached to the different posts.
CREATE TABLE Comment (
comment_id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER,
user_id INTEGER NOT NULL,
content TEXT,
date DATETIME,
FOREIGN KEY (user_id) REFERENCES NonAdmin(user_id),
FOREIGN KEY (post_id) REFERENCES Post(post_id)
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论