Запрет добавление в таблицу данных MariaDB

230

30 сентября 2018, 18:20

CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `products` (
  `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`),
  );
CREATE TABLE `subadmins_products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` MEDIUMINT UNSIGNED NOT NULL, -- Внешний ключ из product
  `sumadmin_id` INT UNSIGNED NOT NULL, -- Внешний ключ из user
  PRIMARY KEY (`id`),
  FOREIGN KEY (`sumadmin_id`) REFERENCES users(`id`)
);
CREATE TABLE `moderators_products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` MEDIUMINT UNSIGNED NOT NULL, -- Внешний ключ из product
  `moderator_id` INT UNSIGNED NOT NULL, -- Внешний ключ из user,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`moderator_id`) REFERENCES users(`id`)
);

Суть: Если добавлять в таблицы moderators_products и subadmins_products записи, то возможна ситуация при которой один и тот же id из таблицы users будет соответствовать одному и тому же id из таблицы products. То есть, получается, что один юзер у одного и того же продукта будет как субадмином, так и модером. Это нужно исключитью. Есть варинт проверять это программно, но мне интересно есть ли решение средствами DB.

Answer 1

Решение 1: с помощью триггеров на вставку/обновление для таблиц subadmins_products и moderators_products, проверять наличие записи в другой таблице.

Решение 2: subadmins_products и moderators_products объединить в одну users_products, добавить колонку user_type=[субадмин или модер], наложить ограничение UNIQUE KEY на набор колонок product_id, users_id

  CREATE TABLE `users_products` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_id` MEDIUMINT UNSIGNED NOT NULL, -- Внешний ключ из product
    `users_id` INT UNSIGNED NOT NULL, -- Внешний ключ из user
    `user_type` INT NOT NULL, -- субадмин или модер
  PRIMARY KEY (`id`),
  UNIQUE KEY (`product_id`, `users_id`),
  FOREIGN KEY (`users_id`) REFERENCES users(`id`)
  );