I have a table that stores comments users make about images on the site. The table is structured with four columns, the row_id, which is the primary key, the image_id, the user_id and the comment. What I want to do is ensure that a user can only leave one comment per image. Do I simply create a unique index on the two columns?
我有一个表，用于存储用户对网站上图像的评论。该表由四列组成，row_id是主键，image_id，user_id和注释。我想要做的是确保用户每张图片只能留一个评论。我只是在两列上创建一个唯一索引吗？
CREATE UNIQUE INDEX imgusr ON comments (image_id, user_id);
The idea is to get the following query to work:
我们的想法是让以下查询起作用：
INSERT INTO comments SET image_id = '1', user_id = '2', comment = 'nice' ON DUPLICATE KEY UPDATE comment = 'nice';
The gotchya (gotme?) is that the table is innoDB because it is expected to get very large. Is this the approach that will work, despite the presence of a primary key?
gotchya（gotme？）是表是innoDB，因为它会变得非常大。这是一种可行的方法，尽管存在主键吗？
1 个解决方案
#1
12
Yes this will work perfectly.
是的，这将完美地运作。
In another topic, why did you have a row_id ? You can simply put the primary key as (image_id, user_id), this will works too.
在另一个主题中，为什么你有一个row_id？您可以简单地将主键设置为（image_id，user_id），这也可以。
#1
12
Yes this will work perfectly.
是的，这将完美地运作。
In another topic, why did you have a row_id ? You can simply put the primary key as (image_id, user_id), this will works too.
在另一个主题中，为什么你有一个row_id？您可以简单地将主键设置为（image_id，user_id），这也可以。