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?

Are you sure that your application is inserting null into the region field and not something like a zero? To debug, I would turn off the foreign key, insert the record, and see what is there. Is it null or did it get set to a value. — Rob Di Marco
– Rob Di Marco, Commented Jul 31, 2009 at 13:28
Good tip. I turned off the foreign key, and realized that my application was entering a 0 into the DB, even though in my application, if the variable is not set, then I assign NULL to it. Then I realized, the region field in the DB is an integer, so NULL will be interpreted as 0. Thanks! — littleK
– littleK, Commented Jul 31, 2009 at 13:40

Quassnoi · Accepted Answer · 2009-07-31 13:37:25Z

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.

answered Jul 31, 2009 at 13:37

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

littleK Over a year ago

The problem is the field in my DB is set as an int, so it will interpret NULL as 0...Thanks.

littleK Over a year ago

For anyone else who might run into this issue, the way to get around it is by, in your SQL query, NOT inserting the NULL value.

Collectives™ on Stack Overflow

MySQL Foreign Key Constraint Confusion

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related