I'm looking for a constraint to prevent the insert of an empty string in MySQL

Question

Ok, 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 course 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.

Answer 1

Try to use triggers - http://dev.mysql.com/doc/refman/5.1/en/triggers.html

Answer 2

Normally you would do that with CHECK constraint:

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

Unfortunately MySQL has limited support for constraints. From MySQL Reference Manual:

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

That's why you have to use triggers as a workaround, as people have pointed out.

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