MySQL doesn't handle ENUM types error

Question

I have this script:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_ALL_TABLES';

SET SQL_MODE = `STRICT_ALL_TABLES`;

CREATE TABLE IF NOT EXISTS `w_bank_account` (
  `account_id` INT NOT NULL AUTO_INCREMENT,
  `bank_id` INT(11) NOT NULL,
  `account_number` VARCHAR(20) NOT NULL,
  `account_type` ENUM('1','2') NOT NULL,
  `balance` DECIMAL(19,4) NOT NULL DEFAULT 0,
  `created` TIMESTAMP NOT NULL,
  `modified` TIMESTAMP NULL,
  PRIMARY KEY (`account_id`),
  CONSTRAINT `fk_w_bank_account_n_bank1`
    FOREIGN KEY (`bank_id`)
    REFERENCES `n_bank` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_w_bank_account_n_bank1_idx` ON `w_bank_account` (`bank_id` ASC);

I try this two queries:

INSERT INTO `w_bank_account` (`bank_id`, `account_number`, `account_type`, `balance`) VALUES 
(1, '01234567890123456789', '1', 0.0000), 
(1, '01234567890123456789', '6', 0.0000);

And it works but in the second insert it leaves account_type empty (I think this goes to NULL) :-O why if I'm setting SQL_MODE as STRICT_ALL_TABLES? Should not the ENUM field gets the first value instead of leave field NULL? I'm using MySQL 5.5.31 on Debian

echo_Me · Accepted Answer · 2013-08-06 16:26:36Z

1

http://dev.mysql.com/doc/refman/5.0/en/enum.html

If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0. More about this later.

you can use this trigger function

     delimiter //
     CREATE TRIGGER upd_check BEFORE UPDATE ON `w_bank_account`
     FOR EACH ROW
     BEGIN
     IF (NEW.`account_type` != '2' AND NEW.`account_type` != '1')
     SET NEW.`account_type` = '1';
     END IF;
     END;//
     delimiter ;

edited Aug 6, 2013 at 16:26

answered Aug 6, 2013 at 16:21

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

2 Comments

Reynier Over a year ago

@echo-me MySQL should take care of this, something like CONSTRAINTS that fails upon insertion of wrong values :-(

echo_Me Over a year ago

@Reynier you right this can be a bug , i think this should be fixed.

Collectives™ on Stack Overflow

MySQL doesn't handle ENUM types error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related