A constraint to prevent the insert of an empty string in MySQL

In this question I learned how to prevent the insert of a NULL value. But, unfortunately, an empty string is being inserted anyway. Apart from preventing this on the PHP side, I'd like to use something like a database constraint to prevent this. Of cou开发者_JAVA百科rse a check on the application side is necessary, but I'd like it to be on both sides.

I am taught that whatever application is talking to your database, it should not be able to insert basically wrong data in it. So...

CREATE TABLE IF NOT EXISTS tblFoo (
  foo_id int(11) NOT NULL AUTO_INCREMENT,
  foo_test varchar(50) NOT NULL,
  PRIMARY KEY (foo_id)
);

Would still allow me to do this insert:

INSERT INTO tblFoo (foo_test) VALUES ('');

Which I would like to prevent.

Normally you would do that with CHECK constraint:

foo_test VARCHAR(50) NOT NULL CHECK (foo_test <> '')

Prior to Version 8.0 MySQL had limited support for constraints. From MySQL Reference Manual:

The CHECK clause is parsed but ignored by all storage engines.

If you must stick to an old version use triggers as a workaround, as people have pointed out.

In future, you may want to take a look at PostgreSQL, which is considered to have better support for data integrity (among other things) by many people.

You could use triggers to prevent insertion of blankstring.

It's not fast, not very concise and not pretty, but...

Example:

1. Create your table:

   mysql> create table yar (val VARCHAR(25) not null);
   Query OK, 0 rows affected (0.02 sec)
   

2. Create your 'before insert' trigger to check for blankstring and disallow.

   mysql> delimiter $
   
   mysql> create trigger foo before insert on yar
       -> for each row
       -> begin
       -> if new.val = '' then
       -> signal sqlstate '45000';
       -> end if;
       -> end;$
   Query OK, 0 rows affected (0.01 sec)
   
   mysql> delimiter ;
   

3. Try to insert null and blankstring into your column:

   mysql> insert into yar values("");
   ERROR 1644 (45000): Unhandled user-defined exception condition
   
   mysql> insert into yar values(NULL);
   ERROR 1048 (23000): Column 'val' cannot be null
   
   mysql> insert into yar values ("abc");
   Query OK, 1 row affected (0.01 sec)
   
   mysql> select * from yar;
   +-----+
   | val |
   +-----+
   | abc |
   +-----+
   1 row in set (0.00 sec)