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

Question

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.

I can see your need to prevent empty strings and I too was taught to prevent wrong data from being inserted at the database level. But experience has taught me that you need to make a careful cost/benefit judgement based on the complexity of the constraint. Some constraints are simple to express at the application level but can become very cumbersome at the database level. Worse, they tend to completely lock you into a database vendor if you overdo it. Just my $0.02. — Stijn de Witt
– Stijn de Witt, Commented Oct 3, 2014 at 23:34
An empty string is not the same thing as a null, so there is no 'anyway' about it. — user207421
– user207421, Commented Jun 24, 2018 at 2:40

Frank Neblung · Accepted Answer · 2020-07-21 14:10:39Z

39

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.

edited Jul 21, 2020 at 14:10

Frank Neblung

3,20523 silver badges40 bronze badges

answered Mar 25, 2010 at 9:34

jholster

5,1461 gold badge29 silver badges21 bronze badges

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

4 Comments

Whakkee Over a year ago

So... what in oracle is handled by a NOT NULL constraint, needs a NOT NULL constraint and two triggers (before update and before insert) in MySQL. And some hacking if I like an error to be thrown, according to this question: stackoverflow.com/questions/24 . The advice to look at PostgreSQL might be the best advice here! Thanks!

jholster Over a year ago

PostgreSQL has its own limitations, too. However, there are plenty of people never stepped outside their (first learnt) PHP/MySQL world, for whom I really recommend taking a fresh breath of air at least once to find out what they are missing. Then go back if you still want ;-)

Samuel Harmer Over a year ago

Support for CHECK CONSTRAINT available in MariaDB from 10.2.1 (MDEV-7563).

fgilio Over a year ago

MySQL 8.0 has better support for CHECK constrains: dev.mysql.com/doc/refman/8.0/en/…

Rich Apodaca · Accepted Answer · 2018-06-24 02:29:39Z

You could use triggers to prevent insertion of blankstring.

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

Example:

Create your table:

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

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 ;

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)

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related