MySQL Make a combination of columns unique

Question

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?

Ethelyn

Did you try it? Are you worried about it failing, or about it performing poorly? – Lucianaluciano

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. – Ethelyn

Answer 1

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.

Kerstin

I didn't realize I could do that in an InnoDB table. Thanks for the suggestion. – Ethelyn