关系数据库设计-一张表中的双主键？

发布于 2024-09-24 09:58:46 字数 413 浏览 8 评论 0原文

我有一个表来保存项目的用户评分。我想允许每个用户只对一个项目评分一次，有什么方法可以强制数据库不允许 itemId 和 userId 重复？

alt text

我不希望每个单独的字段都是主键。我希望主键同时基于它们。例如，有一行：

itemId= 1 & userId = 1

应允许以下内容：

itemId= 2 & userId = 1
itemId= 1 & userId = 2

不应允许以下内容：

itemId= 1 & userId = 1

原文

I have a table that keeps the user ratings for items.
I want to allow each user to rate an item only once, is there any way to force the database not to allow duplicate for itemId and userId?

alt text

I don't want each individual field to be a primary key.
I want the primary key to be based on both of them at the same time.
for example there is a row:

itemId= 1 & userId = 1

following should be allowed:

itemId= 2 & userId = 1
itemId= 1 & userId = 2

following should NOT be allowed:

itemId= 1 & userId = 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

世界等同你 2024-10-01 09:58:46

是的。在 itemId 和 userId 上创建主键。

要在 T-SQL (SQL Server) 中执行此操作，您可以使用以下内容：（

CREATE TABLE rating (
  itemId int NOT NULL
    CONSTRAINT fk_rating_item FOREIGN KEY REFERENCES item ( itemId ),
  userId int NOT NULL
    CONSTRAINT fk_rating_user FOREIGN KEY REFERENCES [user] ( userId ),
  thumbsUp int,
  thumbsDown int,
  CONSTRAINT pk_rating PRIMARY KEY ( itemId, userId )
)

这假设您的项目表是“item”，用户表是“user”。）

我不确定为什么您的两个拇指都有一个值向上和向下？如果这是一个布尔值，您可能只需要一个：如果“向上”是 0，那么实际上就是“向下”。

编辑：复合键的定义

当您在一个表中的两列上创建主键时，这意味着它只需要两个值都是唯一的，即它允许任意数量的行具有只要每个 userId 不同，itemId 就相同，反之亦然。

两者的组合必须是唯一的，而不是密钥的每个部分单独存在。

Yes. Create a primary key on both itemId and userId.

To do this in T-SQL (SQL Server) you would use something like:

CREATE TABLE rating (
  itemId int NOT NULL
    CONSTRAINT fk_rating_item FOREIGN KEY REFERENCES item ( itemId ),
  userId int NOT NULL
    CONSTRAINT fk_rating_user FOREIGN KEY REFERENCES [user] ( userId ),
  thumbsUp int,
  thumbsDown int,
  CONSTRAINT pk_rating PRIMARY KEY ( itemId, userId )
)

(This assumes your items table is 'item' and your users table is 'user'.)

I'm not sure why you have a value for both thumbs up and thumbs down? If this is a boolean value, you might only need one: if thumbs up is 0, then that's effectively thumbs down anyway.

Edit: Definition of composite keys

When you create a primary key on two columns in one table, that means it is only required to be unique for both values, i.e. it will allow for any number of rows with the same itemId as long as each userId is different, and vice-versa.

It's the combination of the two that must be unique, not each part of the key individually.

回复收藏 0 原文

寻找我们的幸福 2024-10-01 09:58:46

使用复合主键（由 2 列组成的主键）。

CREATE TABLE Rating 
(
 itemID     INT NOT NULL
,userID     INT NOT NULL
,thumbsUP   INT NOT NULL
,thumbsDown INT NOT NULL
,PRIMARY KEY (itemID, userID)
);

Use a composite primary key (primary key consisting of 2 columns).

CREATE TABLE Rating 
(
 itemID     INT NOT NULL
,userID     INT NOT NULL
,thumbsUP   INT NOT NULL
,thumbsDown INT NOT NULL
,PRIMARY KEY (itemID, userID)
);

回复收藏 0 原文

无边思念无边月 2024-10-01 09:58:46

大多数数据库将允许由多个列组成的复合主键。您使用哪个数据库？

一个有趣的相关讨论：什么是使用复合/复合主键的缺点？

mySQL 类型示例：

createuserratings.sql
CREATE TABLE USERRATINGS
( 
    itemId     INT NOT NULL,
    userId     INT NOT NULL,
    thumbsUp   INT NOT NULL,
    thumbsDown INT NOT NULL,
    PRIMARY KEY (itemID, userID)
);

在 mySQL 文档中搜索复合/复合主键，您应该会得到很多信息（我不使用 mySQL）。

Most databases will allow for composite primary keys made from multiple columns. Which database are you using?

An interesting, related discussion: What are the down sides of using a composite/compound primary key?

mySQL type example:

createuserratings.sql
CREATE TABLE USERRATINGS
( 
    itemId     INT NOT NULL,
    userId     INT NOT NULL,
    thumbsUp   INT NOT NULL,
    thumbsDown INT NOT NULL,
    PRIMARY KEY (itemID, userID)
);

Do a search for composite/compound primary keys in the mySQL documentation and you should get a lot of info (I don't use mySQL).

回复收藏 0 原文

~没有更多了~