MySQL 使列的组合唯一
我有一个表，用于存储用户对网站上的图像所做的评论。该表由四列构成：row_id（主键）、image_id、user_id 和 comment。我想要做的是确保用户只能为每张图片留下一条评论。我是否只需在两列上创建唯一索引？
CREATE UNIQUE INDEX imgusr ON comments (image_id, user_id);
这个想法是让以下查询起作用：
INSERT INTO comments SET image_id = '1', user_id = '2', comment = 'nice' ON DUPLICATE KEY UPDATE comment = 'nice';
问题是该表是 innoDB，因为它预计会变得非常大。尽管存在主键，这种方法是否有效？
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?
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，这会完美地工作。
在另一个主题中，为什么有
row_id？您可以简单地将主键设置为(image_id, user_id)，这也可以。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.