Validate before insert into mysql table

Question

What is the most efficient way add a constraint in my MySQL table in order to reject the 3rd insert of the same type?

CREATE TABLE `stack_over_t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(45) NOT NULL,
  `category` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
)

Ideally I would like to block an insert (trigger before insert?) with category=1, since there are already two entries with category 1. How can I do this? Can I extend it to more than one column?

Can you add some context about the category column? Why is it okay to have two rows, but not three? Is it possible that you can restructure your tables that you can use a normal `UNIQUE` constraint? Can you add another column as some kind of counter/indicator of the entry, which you could use in a combined `UNIQUE` check as in https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql? — Progman, Dec 20 '18 at 13:51

score 1 · Answer 1 · answered Dec 20 '18 at 12:34

If you are using trigger then it is possible either or you can set client side and server side validation.

DELIMITER $$

CREATE TRIGGER example_before_insert_allow_only_one_active
     BEFORE INSERT ON stack_over_t FOR EACH ROW
     BEGIN
          IF (SELECT COUNT(id) FROM stack_over_t 
               WHERE id=NEW.id AND data=NEW.data) > 0
          THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: only one active row allowed per type';
          END IF;
     END;
$$

CREATE TRIGGER example_before_update_allow_only_one_active
     BEFORE UPDATE ON stack_over_t  FOR EACH ROW
     BEGIN
          IF (SELECT COUNT(id) FROM stack_over_t 
               WHERE id=NEW.id AND data=NEW.data) > 0
          THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: only one active row allowed per type';
          END IF;
     END;
$$

Aishwarya · Answer 2 · 2018-12-20T15:23:42.530

1

DELIMITER $$

USE `database_name`$$

DROP TRIGGER /*!50032 IF EXISTS */  beforinsertblock $$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER beforinsertblock BEFORE INSERT ON `stack_over_t` 
FOR EACH ROW BEGIN
DECLARE COUNT INT(11);
select count(*) INTO COUNT from stack_over_t WHERE category='1';
  IF(COUNT>2) THEN 

     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'STATMENT';

    END IF ;
END;
$$

DELIMITER ;

YOU CAN BLOCK THE INSERT QUERY

edited Dec 20 '18 at 15:23

answered Dec 20 '18 at 12:37

Aishwarya

433
3
10

Shouldn't it be something like `COUNT>2` since having two rows is okay, but having three rows is not? – Progman Dec 20 '18 at 13:48

score 0 · Answer 3 · answered Dec 20 '18 at 12:34

Use the INSERT IGNORE command rather than the INSERT command. example:

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 0 rows affected (0.00 sec)

Validate before insert into mysql table

3 Answers3