0

I'm using mysql (mariadb). I have one table "events_ratings", which contains 4 columns

CREATE TABLE `event_ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_event` int(11) NOT NULL,
  `rating` int(1) NOT NULL,
  `id_user` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
  PRIMARY KEY `id`,
  ADD CONSTRAINT `idevent` FOREIGN KEY (`id_event`) REFERENCES `events_fb` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `iduser` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`) ON UPDATE CASCADE;
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The aim of this table is to provide user rating, so :

  • Each user can rate an event (based on his unique "id_user"). The user could rates many events as he wants but never the same one.

So basically id_user AND id_event should never have duplicated values. I know how to make a column unique but how to make a constraint based on two fields ?

thanks

yoann84
  • 518
  • 1
  • 3
  • 14

1 Answers1

3

You would need to add a unique constraint and specify both fields in it like this:

ALTER TABLE `events_rating` ADD UNIQUE `unique_user_event`(`id_user`, `id_event`);
Aliaksandra Neviarouskaya
  • 86
  • 5
  • Thank you ! What if I want to erase last value and update with the new value on update ? For now it works fine but the user can't update its rating. Any clue ? – yoann84 Jun 11 '21 at 11:47
  • 1
    @yoann84 hey, try smth like `INSERT INTO events_ratings VALUES() as new ON DUPLICATE KEY UPDATE ratings=new.ratings`. This one will try to insert a new row, if duplicate met it will replace old ratings with new ratings. – Aliaksandra Neviarouskaya Jun 13 '21 at 18:04