2. PHPRunner General questions
  3. [SOLVED]  Foreign key constraint prevents NULL value
This topic is locked
[SOLVED]

 Foreign key constraint prevents NULL value

 		9/17/2009 7:27:04 AM
PHPRunner General questions
M
 mlcprs author

Hi,
I'm evaluating phprunner, and it's looking good. I'm generating pages with very little customisation and they do what I want. I haven't had to add any extra php code.

I've come across a problem I can't figure out.

My database has foreign key (FK) constraints (implemented by innodb). It still allows me to enter a NULL value for a FK that isn't specified as "not null" (using SQL directly).

However, when I try to add a NULL FK with my phpRunner-built pages, I get the message "Cannot add or update a child row: a foreign key constraint fails (jazzcreati2/Booking, CONSTRAINT fk_Booking_Company1 FOREIGN KEY (resellerName) REFERENCES Company (companyName) ON DELETE NO ACTION ON UPDATE NO ACTION)".

The FK field is controlled by a look up table, which I'm leaving on "please select" in the hope it will produce a NULL.
Can anyone please advise how I can do this?
Thanks,
Peter
M
 		mlcprs author 9/17/2009

I should have said that I was hoping it would insert the SQL default value in the FK field, which is NULL.
It just occurred to me that this problem might be occuring because the key is a VARCHAR. It seems to work OK where the key is an INT. Maybe the phpRunner code is trying to insert the empty string "".
Peter
Admin 9/17/2009

This behaviour is by design. You may want to override this field value with NULL using BeforeAdd/BeforeEdit events.
More info:

http://www.xlinesoft.com/phprunner/docs/before_record_updated.htm
A
 		Andrea Devecchi 10/1/2009

This problem occurred to me also and only if the key is a varchar.

I found a workaround for the add page:

insert in the BeforeAdd event:

if ($values['fieldname'] == "") unset($values['fieldname']);


This removes the key "fieldname" from the values array and this cause the field not to be included in the INSERT query.

So the field gets the default value in the db, which is NULL.

The only problem is in the edit page, once you set a value in that field, you won't be able anymore to set it to NULL.

Hope this helps somebody, sorry for my english.
Andrea



I should have said that I was hoping it would insert the SQL default value in the FK field, which is NULL.
It just occurred to me that this problem might be occuring because the key is a VARCHAR. It seems to work OK where the key is an INT. Maybe the phpRunner code is trying to insert the empty string "".
Peter