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
Name = YYYYYY
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]
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.
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:
CREATE TABLE IF NOT EXISTS `usergroups` (
`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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `users` (
`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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
ALTER TABLE `users`
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)
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:
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.
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():
$arrayForeignKeys=$this->tableSchema->foreignKeys;
foreach ($this->attributes as $name=>$value) {
if (array_key_exists($name, $arrayForeignKeys) && $this->metadata->columns[$name]->allowNull && trim($value)=='') {
$this->$name=null;
}
