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?

Answer 1

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

Answer 2

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;