CODEWITHSUNDEEP
mysqlunique-index
Wige
Wige

Reputation: 3918

MySQL Make a combination of columns unique

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?

Upvotes: 22

Views: 10169

Answers (1)

krtek
krtek

Reputation: 26607

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.

Upvotes: 12

Related Questions