MySQL Make a combination of columns unique

mysql unique-index
Wige picture Wige · May 10, 2011 · Viewed 10k times · Source

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?

Answer

krtek picture krtek · May 10, 2011

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.

Related questions

How do I delete all the duplicate records in a MySQL table without temp tables

I've seen a number of variations on this but nothing quite matches what I'm trying to accomplish. I have a table, TableA, which contain the answers given by users to configurable questionnaires. The columns are member_id, quiz_num, question_…

Read More
Removing duplicates with unique index

I inserted between two tables fields A,B,C,D, believing I had created a Unique Index on A,B,C,D to prevent duplicates. However I somehow simply made a normal index on those. So duplicates got inserted. It …

Read More
Check whether a field has the property `UNIQUE` in mysql and PHP

How can I check whether a field from a table is set as UNIQUE? For example I have a table named users with a field email set as UNIQUE and a field picture not set as UNIQUE, I want before …

Read More