23

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?

Wige
  • 3,788
  • 8
  • 37
  • 58
  • Did you try it? Are you worried about it failing, or about it performing poorly? – outis May 10 '11 at 20:12
  • Both. I am actually converting from one system to another, and the initial table is going to be fairly massive (4 million rows immediately, expected to grow to 20 million in the first few weeks) so I want to be absolutely sure I am going about this the right way from the start. – Wige May 10 '11 at 20:34

1 Answers1

13

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.

krtek
  • 26,334
  • 5
  • 56
  • 84