Error when inserting into a table with a NULL value for a parent key

Yii 1.1 General Discussion
1

I have the following MySQL table:

CREATE TABLE IF NOT EXISTS financial_system.category (

category_key INT UNSIGNED NOT NULL AUTO_INCREMENT ,

category_name VARCHAR(100) NOT NULL ,

parent_category_key INT UNSIGNED NULL DEFAULT NULL ,

PRIMARY KEY (category_key) ,

INDEX fk_category_parent_idx (parent_category_key ASC) ,

CONSTRAINT fk_category_parent

FOREIGN KEY (`parent_category_key` )


REFERENCES `financial_system`.`category` (`category_key` )


ON DELETE CASCADE


ON UPDATE NO ACTION)

ENGINE = InnoDB;

When I insert into the table at MySQL command line with a NULL value for parent_category_key, the insertion is successful:

mysql> INSERT INTO category (category_name, parent_category_key) VALUES (‘Dessert’,NULL);

Query OK, 1 row affected (0.00 sec)

When I try to insert into the table with Yii by not selecting from the parent category select statement, I get the following error:

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (financial_system.category, CONSTRAINT fk_category_parent FOREIGN KEY (parent_category_key) REFERENCES category (category_key) ON DELETE NO ACTION ON UPDATE NO ACTION). The SQL statement executed was: INSERT INTO category (category_name, parent_category_key) VALUES (:yp0, :yp1). Bound with :yp0=‘Dessert’, :yp1=’’

I have a select statement for my parent category key so that any of the current categories may be chosen as a parent category, however I want an option for the category not to have a parent category, where the DEFAULT NULL would be applied. Here is the snippet for my select statement:

<div class="row">


	<?php echo $form->labelEx($model,'parent_category_key'); ?>


			<?php echo $form->dropDownList($model, 'parent_category_key',


          $model->getCategoryNames(),


          array('empty' => 'Select a parent category'));


 ?>


</div>

Any suggestions to fix this error will be appreaciated.

2

I guess you are trying to insert empty string not null. They are not same (dev.mysql.com/doc/refman/5.0/en/problems-with-null.html).

Default null will work if no parent_category_key mention at all.

3

Yes, the table is set to default to NULL. Yii tries to insert an empty string, but that is not what I want. When a parent category is not selected, I would like it to ignore it instead of trying to insert an empty value. I need this value to be NULL, which is the default in the database for that column. I would like the following statement to happen if a parent category is not selected:

INSERT INTO category (category_name) VALUES (‘Appetizer’);

How do I modify Yii so that it ignores that column when a value is not selected rather than it trying to insert it with an empty value?

4

I found a solution. I added the following to my rules:

array(‘parent_category_key’, ‘default’, ‘setOnEmpty’ => true, ‘value’ => null),