CODEWITHSUNDEEP
mysqlconstraints
Whakkee
Whakkee

Reputation: 1927

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 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.

Upvotes: 44

Views: 33029

Answers (2)

jholster
jholster

Reputation: 5136

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.

Upvotes: 39

kpower
kpower

Reputation: 3891

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)

Upvotes: 16

Related Questions