I have this table:
ID | genre_id | is_best_in_genre | movie_name
--------------------------------
1 | 3 | 0 | Hateful Eight
2 | 3 | 0 | Django Unchained
2 | 3 | 1 | Inglorious B
2 | 3 | 0 | Once Upon A Time in Hollywood
is_best_in_genre can only be true (1) once for every genre_id. There can only be 1 best movie in each genre.
How would I make a constraint such as this?
Alternative idea:
The more proper, normalized way to handle this is probably to make a separate 'best_in_genre' table with a unique constraint on genre_id.
This is also easier to update, because you're not required to make sure that everything gets 0'd when selecting a new 'best'.
A better approach might be to "move" the column is_best_in_genre to a separate (joined) table. A simple table with two columns could do the job:
CREATE TABLE best (
idmovie int,
idgenre int,
PRIMARY KEY (idgenre))
This would need to be joined with the original table like:
SELECT m.*, CASE b.idmovie>0 THEN 1 ELSE 0 END best_in_genre
FROM movietable m
LEFT JOIN best b ON idmovie=id AND idgenre=genre_id
The PRIMARY KEY constraint in the table best will make sure that each genre can only appear once.