SQL insert/delete query with junction table

55 Views Asked by At

I have a sql database (mysql/mariadb) with following schema:

CREATE TABLE IF NOT EXISTS `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name` (`name` ASC) VISIBLE)

CREATE TABLE IF NOT EXISTS `role` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name` (`name` ASC) VISIBLE)

CREATE TABLE IF NOT EXISTS `userRoleMap` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  UNIQUE INDEX `user_role_map` (`user_id` ASC, `role_id` ASC) VISIBLE,
  CONSTRAINT `userRoleMap_userfk_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `userRoleMap_rolefk_1`
    FOREIGN KEY (`role_id`)
    REFERENCES `role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

I would like to add a role to a user and remove a role from a user providing user.name and role.name as input.

When adding a role to a user (in user_role_map), if the user or the role does not exist there should be an error.

When removing a role from a user (in user_role_map) if the user does not exist there should be an error but not if the role does not exist.

How to efficiently perform these operations?

Is it possible to perform each operation in 1 statement?

Thanks

0

There are 0 best solutions below