[SOLVED] Problem with foreign key on null fields

I explain my problem with an example:

I have 2 tables in mysql:


IdUser <- Primary key

IdUserGroup <-Integer, Accept Null





IdUserGroup <- Integer, Primary key


There is a foreign key defined in Mysql that connect Users.IdUserGroup to UserGroups.IdUserGroup, so 1 user belongs to one group and 1 group has many users, but this is optional because Users.IdUserGroup can be null.

So I try to insert a record in Users table with a crud Yii application with these values:

IdUser = XXXX


IdUserGroup = Null

When I save the record this error happens

[i]CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1452 Cannot add or update a child row: a foreign key constraint fails (mydb.users, CONSTRAINT fk_Users_UserGroups FOREIGN KEY (idUserGroup) REFERENCES userGroups (idUserGroup) ON DELETE NO ACTION ON UPDATE NO ACTION)


I checked in debug and I see that when Yii go to save it transforms the null value of IdUserGroup to 0, so I think MySql give the error because it doesn’t find a 0 record in userGroups table.

If insert the same record with phpMyAdmin there’s no problem (because probably it doesn’t transform null to 0)

Is there a solution to this?

MODIFICATION: I try also to modify in the 2 tables idUserGroup from integer to CHAR but the problem is the same, so in general if the field is null the foreign key gives error.


post the code of model and controller

I made other specific debugs and I have to modify the contest of the problem:

The problem exists only if the field of the foreign key (idUserGroup) is NOT NUMERIC, the transformation of numeric field from null to 0 was caused by some instructions in my beforesave routine, by eliminating those instructions the insert goes OK.

Instead the problem for char fields still exists, and you can reproduce them simply by creating these 2 tables:


  `idUserGroup` varchar(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' /> NOT NULL,

  `descrGroup` varchar(30) DEFAULT NULL,

  PRIMARY KEY (`idUserGroup`)



  `idUser` int(5) NOT NULL AUTO_INCREMENT,

  `idUserGroup` varchar(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' /> DEFAULT NULL,

  `userName` varchar(30) DEFAULT NULL,

  PRIMARY KEY (`idUser`),

  KEY `fk_users_userGroups` (`idUserGroup`)



  ADD CONSTRAINT `fk_users_userGroups` FOREIGN KEY (`idUserGroup`) REFERENCES `usergroups` (`idUserGroup`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Then create a CRUD application with yiic for ‘Users’ class, no modification to the code is needed, try to insert a new record in user class and you will get this error:

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1452 Cannot add or update a child row: a foreign key constraint fails (intrapportal.users, CONSTRAINT fk_users_userGroups FOREIGN KEY (idUserGroup) REFERENCES usergroups (idUserGroup) ON DELETE NO ACTION ON UPDATE NO ACTION)

I think I found the origin of the problem.

MySql gives error because try to verify the existence of the foreign key in the table userGroups, the only case when it doesn’t make the check is if the value is NULL, instead with the value ‘’ it make the check the same and gives error.

I’ve verified in Yii that for string fields the value ‘’ is saved and so it gives error, for numeric fields instead it goes well because Null value is saved. This is managed by a typecast function that make this check:


			return $this->type==='string' ? '' : null;

This is the complete path of functions called:




At this point I don’t know wich is the best solution, I think that if a field is a foreign key and it’s value is ‘’ it should be always transformed in Null (numeric or string).

I don’t know if this is a particular problem of Mysql or also concerns also others DBMS, however it needs a fix.


To close this issue…

as suggested by Qiang I solved by intercepting the situation that causes the error, so in beforesave() method I check for all the fields of the class if there are any that are foreign keys that admits null value and if they have empty value (==’’) I transform them in null.

this is an extraction of the code in beforeSave():


    foreach ($this->attributes as $name=>$value) {

      if (array_key_exists($name, $arrayForeignKeys) && $this->metadata->columns[$name]->allowNull && trim($value)=='') {