确保数据库中每个用户、每个页面的单次投票
我正在 Java Web 应用程序中开发一个小功能，该功能与 stackoverflow.com 上的投票赞成/反对功能非常相似。我有一个如下所示的数据库：
VOTE TABLE
id (bigint): surrogate primary key
question_id (bigint):: the question the vote is for
vote_type (int): whether the vote is up or down
user_id (varchar): the username of the person who the vote belongs to
我想确保每个人每个问题在数据库中只有一票。执行此操作的最佳方法是什么？如何使用我上面描述的数据库模式来强制执行它？
我目前遇到的问题是，用户发出两个投票请求，然后数据库包含该用户的 2 个“投票”，而他们应该只有一票。
I'm working on a small feature in a Java web application that very closely resembles the vote up/down feature here at stackoverflow.com. I have a database that looks like the following:
VOTE TABLE
id (bigint): surrogate primary key
question_id (bigint):: the question the vote is for
vote_type (int): whether the vote is up or down
user_id (varchar): the username of the person who the vote belongs to
I want to ensure that there is only one vote in the DB per person, per question. What is the best way to enforce this? How would enforce it with the database schema I have described above?
I am currently having problems where a user fires off two vote-up requests and the database then contains 2 'up votes' for that user, when they should only have one vote.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以通过主键或唯一索引强制执行 - 这两个概念在数据库中非常通用。将其放在两列上：Question_id 和 User_id。这将只允许每个用户每个问题 1 个条目。这将由数据库强制执行，即使您的应用程序代码允许它们投票两次，数据库也会在尝试插入的第二条记录上抛出错误。 （即使您使用事务等，数据库也会正确执行它。）
You can enforce is via a Primary Key or Unique index - both concepts are pretty univeresal within databases. Place it on the two columns together, Question_id and User_id. That would only permit 1 entry per user, per question. This will be enforced by the database, even if your application codes lets them vote twice, the database will throw an error on the second record attempting to be inserted. (Even if you are using transactions etc, the database will enforce it correctly.)
如果您想通过数据库模式强制执行它，正确的方法是使 (question_id, user_id) 成为唯一键。
If you want to enforce it via database schema, the correct way would be to make (question_id, user_id) an unique key.
仅当 user_id 和 Question_id 不存在时才执行 INSERT，例如
，但是您可以将 user_id 和 Question_id 设置为主键（或另一个唯一键），因此物理上只允许一条记录。
Only do the INSERT when user_id and question_id NOT EXISTS e.g.
However you could make user_id and question_id the Primary Key (or another Unique key) so only one record is physically allowed.