Search code examples
mysqlunique-index

MYSQL: Create a unique index to avoid duplicate entries when many entries show 0

I am using one table, reviews, to store reviews for multiple topics, songs, artists and albums and the reviews come from multiple users. I would like to find a way to prevent duplicate reviews from one user for a given song, artist or album. While I try to do this in PHP, I'd like MYSQL to enforce the uniqueness using a UNIQUE index on userid and songid.

However, I've run into the problem that entries show 0 for fields that are not applicable. For example, if the review is for a songid, the table shows 0 for the artistid and albumid.

Reviews
    reviewid (primary key)|text|songid|albumid|artistid|userid
    1|great song|222|0|0|22
    2|great album|0|333|0|22
    3|great singer|0|0|444|22

    //I want to exclude the following entry:
    4|lousy song|222|0|0|22

Can anyone suggest a way to create a Unique index that somehow works around this problem?

Thanks for any suggestions.

Solution

  • The only way you can use the unique indexes is if you change all those 0's to null. nulls are considered distinct from each other in most cases (group by still groups them together).

    Assuming changing them to nulls will not break tons of other queries, it can be accomplished in a single query:

    UPDATE reviews 
SET songid = NULLIF(songid, 0)
  , albumid = NULLIF(albumid, 0)
  , artistid = NULLIF(artistid, 0)
;