MySQL column set to NOT NULL but still allowing NULL values

Question

I have every column set to NOT NULL but for some reason I am still able to add a NULL value in each column. Here is my table info (create syntax):

CREATE TABLE `addresses` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `address` varchar(100) NOT NULL,
    `city` varchar(100) NOT NULL,
    `state` varchar(4) NOT NULL,
    `zip` varchar(30) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4545 DEFAULT CHARSET=utf8;

Here is a sample INSERT that works:

INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES ('', '', '', '');

Any ideas as to why this is happening?

Check this out . . . http://stackoverflow.com/questions/15905420/mysql-inserts-record-with-null-value-in-not-null-column. — Gordon Linoff, Sep 20 '13 at 03:24
Oh I see, well then how can I throw an error for empty values? Is that possible? — doitlikejustin, Sep 20 '13 at 03:28
@doitlikejustin you can use stored procedure and raise an error — Arun Killu, Sep 20 '13 at 03:32

Sudhir Bastakoti · Accepted Answer · 2013-09-20T03:40:22.797

You are inserting empty strings, and empty string are not NULL, to check for NULL error do:

INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES (NULL, NULL, NULL, NULL);

and you will see error. The NOT NULL checks only for values that are not NULL.

To prevent empty string either you have to use triggers, or do the checks on server side programming language to convert empty strings to NULL before performing INSERT query. An example trigger for INSERT may be like: (this is just an example)

CREATE TRIGGER avoid_empty
    BEFORE INSERT ON addresses
        FOR EACH ROW
        BEGIN
        IF street = '' THEN SET street = NULL END IF;
END;

I see. That does throw an error but can I prevent empty values? — doitlikejustin, Sep 20 '13 at 03:30

score 2 · Answer 2 · edited May 23 '17 at 12:25

2

try below

INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES (NULL, NULL, NULL, NULL);

above will not work

INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES ('', '', '', '');

above insert empty string

The empty string specifically means that the value was set to be empty; null means that the value was not set.

to prevent insert empty string check this SO question

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

edited May 23 '17 at 12:25

Community

1
1

answered Sep 20 '13 at 03:30

Damith

62,401
13
102
153

1

Can I prevent empty values? – doitlikejustin Sep 20 '13 at 03:31

score 1 · Answer 3 · answered Sep 20 '13 at 03:30

1

'' isn't NULL, NULL is NULL

This will fail:

INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES (NULL, NULL, NULL, NULL);

answered Sep 20 '13 at 03:30

Greg

3,442
3
29
50

1

Can I prevent empty values? – doitlikejustin Sep 20 '13 at 03:32
You could use a trigger like @Sudhir has done above. I'm not sure if you can do a constraint like that. The other option is to validate it in the code that is generating the insert statement. – Greg Sep 20 '13 at 04:52

peterm · Answer 4 · 2013-09-20T03:51:00.713

If you have control over all of inserts to the table then you can either check for empty strings in your client code, or if you want MySQL to return an error you can wrap inserting values in NULLIF() function. Assuming that you use php your INSERT statement may look like

INSERT INTO addresses(city, address, state, zip) 
VALUES (NULLIF(:address, ''), NULLIF(:city, ''), NULLIF(:state, ''), NULLIF(:zip, ''));

Another way to prevent empty strings from being inserted is to use BEFORE INSERT trigger

CREATE TRIGGER tg_bi_addresses
BEFORE INSERT ON addresses
FOR EACH ROW
  SET NEW.address = 
      CASE WHEN 
       CHAR_LENGTH(NEW.address) = 0 OR
       CHAR_LENGTH(NEW.city)    = 0 OR
       CHAR_LENGTH(NEW.state)   = 0 OR
       CHAR_LENGTH(NEW.zip)     = 0 THEN NULL
       ELSE NEW.address
      END;

The idea is to detect an empty string and violate NOT NULL constraint on one of the fields. If you are using MySQL 5.5 or higher you can use SIGNAL.

Here is SQLFiddle demo. Try to uncomment last insert statement and the trigger won't allow it to succeed.

MySQL column set to NOT NULL but still allowing NULL values

4 Answers4

Linked