In order to preserve a one-to-one ration between three fields, I would like to create a UNIQUE index on the three fields. In other words, for a table, Reviews if one field is reviewid, I would like to prevent instances where more than one reviewid is associated with a given songid for a given userid.
In fact, I would like to do this for several combinations reviewid and songid, reviewid and artistid and reviewid and albumid for a given userid.
I cannot change the data schema at this point. While right now, I am trying to do this in PHP code, it would be better if MYSQL enforced the uniqueness through Indices to ensure one never slips through.
Reviews
reviewid (primary key)|text|songid|albumid|artistid|userid
1|great song|222|null|null|22
2|great album|null|333|null|22
3|great singer|null|null|444|22
//I want to exclude the following entry:
4|lousy song|222|null|null|22
I think this should be possible with a UNIQUE index on the fields in question, but I can't get MYSQL to accept this. It says that you cannot have a Unique index and Primary index on the same field, in this case, reviewid.
Can anyone suggest how to do this?
Thanks in advance for any suggestions
It seems to me you just need separate unique keys on songId, artistid, and albumid. Since reviewId is the pk, if any songid appeared on multiple records it would necessarily be with a different reviewId. In fact, an index on both fields would accomplish the opposite of what you want, allowing multiple occurrences of songId as long as it was with a different reviewId; unique indexes on multiple fields ensure the combination is unique.
Edit: It's covered in the comments below, but the exact solution was that the unique indexes needed to be on the userId and the song/artist/album being reviewed; multiple reviews were allowed, just only once per user.