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

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)