CODEWITHSUNDEEP
mysql
Captain Nemo
Captain Nemo

Reputation: 355

Validate before insert into mysql table

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

enter image description here

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?

Upvotes: 0

Views: 3163

Answers (3)

Aishwarya
Aishwarya

Reputation: 433

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

Upvotes: 1

Ravi Chauhan
Ravi Chauhan

Reputation: 1458

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;
$$

Upvotes: 1

gayatri darade
gayatri darade

Reputation: 104

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)

Upvotes: 0

Related Questions