Menu
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?
887
You cannot designate the same column or combination of columns as both a primary key and a unique key or as both a primary key and a cluster key.
To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
Creating Composite IndexCREATE TABLE table_name ( c1 data_type PRIMARY KEY, c2 data_type, c3 data_type, c4 data_type, INDEX index_name (c2,c3,c4) ); In the above statement, the composite index consists of three columns c2, c3, and c4.
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.
78
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
