cannot save model - foreign key constraint violation

Yii 1.1 General Discussion
1

hi,

i’m having a weird problem with model saving.

there’s one class called bodenplatte which has one attribute called farbe_id, which is a foreign key for the farbe class. but i also allow NULL values for farbe_id.

anyway when i’m trying to save the bodenplatte model with NULL as farbe_id i get an error:


CDbCommand konnte das SQL-Statement nicht ausführen: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`wasserbett_dev`.`tbl_bodenplatte`, CONSTRAINT `FK_bodenplatte_farbe` FOREIGN KEY (`farbe_id`) REFERENCES `tbl_farbe` (`id`) ON DELETE CASCADE). The SQL statement executed was: INSERT INTO `tbl_bodenplatte` (`lederlook`, `thermo`, `klettband`, `xxl`, `farbe_id`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4)

does anybody have any ideas what might be causing this, or where can i look for errors ?

P.S. the first part in german says that CDbCommand could not execute the following SQL statement

2

add:

‘enableParamLogging’=>true,

in your db connection component (in main.php) and check what is exactly inserted in FK column.

3

thanks redguy!

in addition to the previous error i got


Bound with :yp0=0, :yp1=1, :yp2=0, :yp3=0, :yp4=''

apparently ‘’ isn’t treated as NULL so i had to explicitly set it to NULL.

4

Hi,

if you want empty strings to be treated as NULL, add following code to you db connection config (main.php):




'nullConversion'=>PDO::NULL_EMPTY_STRING,

References: here and here.

5

…or add rule which will set null on empty string for specific attribute:




public function rules() {

        return array(

		array( 'farbe_id', 'default', 'setOnEmpty'=>true, 'value'=>null ),

		...
6

thank you both :) so far i just used a simple if statement but your solutions look more elegant and more universal :)

7

I have faced the same issue. I tried to use the nullConversion parameter, but it does not seem to work. The insert query fails and MySQL complains of foreign key violation. The query shown at the exception page shows that the empty string parameters (’’) are not converted to NULLs. It is only when I used the CDefaultValueValidator that the model was saved in the database. I think this is a bug and I’ve already opened an issue at the github issue tracker.

8

Hi,

even if you’ve discovered a bug, it’s not related to Yii inself. Yii’s DB interact classes are wrappers for PDO, and the nullConversion option value is the PDO’s option.

9

True, but is it possible that the nullConversion parameter is not referenced anywhere in the framework? I’ve searched for instances of it and it’s nowhere to be found, however I might be wrong.

10

Actually it’s referenced only twice by a getter and setter methods :) If you’re not familiar with the virtual attributes mechanism in Yii - read this.

11

So, after opening a PDO related bug upstream, a developer says that this specific attribute is for reading, not writing (which is what I need in my case), unless the MySQL PDO driver does not provide support for writing. So, at the moment, the only way to solve this issue is to use the CDefaultValueValidator for such fields.