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

31

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.

Whakkee

출처 mysql constraints
  • 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
  • An empty string is not the same thing as a null, so there is no 'anyway' about it. - user207421

2 답변

20

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 PostgreSQL, which is considered to have better support for data integrity (among other things) by many people.

  • 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! - Whakkee
  • 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 ;-) - jholster
  • Support for CHECK CONSTRAINT available in MariaDB from 10.2.1 (MDEV-7563). - Samuel Harmer
10

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)

Linked

Related

Latest