How to set default enum value if we insert a invalid enum value in MYSQL?

Question

I have a table

CREATE TABLE `enum_test` ( 
`id` int(10) default NULL, 
`value` enum('YES','NO') NOT NULL default 'YES' 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

---

then execute below query

INSERT INTO enum_test(id) VALUES (2);  set 'YES' in `enum` field.
INSERT INTO enum_test(id,value) VALUES (2,1);  set 'YES' in `enum` field.

---

But

 //why below query set blank value in enum field??  
  INSERT INTO enum_test(id, value) VALUES (2, 'YESS');

I want that if a user insert a value that does not exist in enum field then it shoud set the default value of enum how to do that?

Answer 1

Use a trigger function, like this:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON enum_test
-> FOR EACH ROW
-> BEGIN
->     IF (NEW.value != 'YES' AND NEW.value != 'NO')
->         SET NEW.value = '';
->     END IF;
-> END;//
mysql> delimiter ;

More on triggers here: Mysql manual, trigger syntax

edit: As suggested, it's also possible to define stored procedure and call if from the trigger, which I'd consider better for database maintainability. Triggers are part of database definition, so you add this to any sql file with CREATE TABLE statements.

Answer 2

See Tomas' answer for a trigger or maybe use a stored procedure.

You basically are getting a default value inserted. In strict mode an error will be thrown. Otherwise:

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.