MySQL Foreign Key Constraint Confusion

Question

Hey everyone, I have the following 'users' table in MySQL:

 CREATE TABLE `users` (
  `uid` int(11) NOT NULL auto_increment,
  `fname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `role` varchar(75) NOT NULL,
  `region` tinyint(4) unsigned default NULL,
  `username` varchar(25) NOT NULL,
  `password` varchar(75) NOT NULL,
  `new_pass` varchar(5) default NULL,
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `username` (`username`),
  KEY `role` (`role`),
  KEY `region` (`region`),
  CONSTRAINT `users_ibfk_3` FOREIGN KEY (`role`) REFERENCES `role` (`role`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `users_ibfk_4` FOREIGN KEY (`region`) REFERENCES `region` (`region`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8

I have 'region' set as a foreign key to a region table - region.region'

Notice, that users.region is declared as NULL. I was under the impression that in MySQL, a foreign key contstraint is enforced ONLY if the key is set as NOT NULL.

However, when I try to insert a user with a NULL region in my PHP application, I get the following error:

 ERROR: Cannot add or update a child row: a foreign key constraint fails (`reslife4/users`, CONSTRAINT `users_ibfk_4` FOREIGN KEY (`region`) REFERENCES `region` (`region`) ON DELETE CASCADE ON UPDATE CASCADE)

BUT, if I were to add this user outside of my PHP application, for example in phpMyAdmin, it would allow me to.

Does anyone know what's going on?

Answer 1

Your application puts a non-NULL value into region.

Enable the query log and see what exactly your PHP tries to insert into the table.