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?
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;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments