Mysql - make unique constraints on both fields [duplicate]

Question

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

Aliaksandra Neviarouskaya · Accepted Answer · 2021-06-11 10:09:32Z

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`);

answered Jun 11, 2021 at 10:09

Aliaksandra Neviarouskaya

1065 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yoann84 Over a year ago

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 ?

Aliaksandra Neviarouskaya Over a year ago

@yoann84 hey, try smth like INSERT INTO events_ratings VALUES(<put your values here>) 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.

Collectives™ on Stack Overflow

Mysql - make unique constraints on both fields [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related