CODEWITHSUNDEEP
mysqlsql
doitlikejustin
doitlikejustin

Reputation: 6353

MySQL column set to NOT NULL but still allowing NULL values

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?

Upvotes: 4

Views: 24156

Answers (5)

YanAlex
YanAlex

Reputation: 119

Use virtual field, add restriction on it

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `is_not_null` int(11) GENERATED ALWAYS AS (coalesce(`c1`,`c2`)) VIRTUAL NOT NULL,
  PRIMARY KEY (`id`)
);

Warning, seems MariaDb is not suitable to use (NOT NULL) on virtuals

Upvotes: 0

peterm
peterm

Reputation: 92795

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.

Upvotes: 1

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

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;

Upvotes: 15

Damith
Damith

Reputation: 63065

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

Upvotes: 2

Greg
Greg

Reputation: 3522

'' isn't NULL, NULL is NULL

This will fail:

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

Upvotes: 1

Related Questions