Codeigniter Activerecord update method refuses to insert NULL value

Question

I'm using Codeigniters Active record library to carry out an update on a column on my DB.

Here's the SQL for the table

CREATE TABLE `schedules` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`reservation_id` INT(11) NULL DEFAULT NULL,
`title` VARCHAR(255) NOT NULL,
`description` VARCHAR(512) NULL DEFAULT NULL,
`start_date` DATE NOT NULL,
`start_time` TIME NOT NULL,
`end_time` TIME NULL DEFAULT NULL,
`enabled` TINYINT(1) NULL DEFAULT '1',
`status` ENUM('OPEN','RESERVED') NULL DEFAULT 'OPEN',
PRIMARY KEY (`id`),
INDEX `fk_schedules_reservations` (`reservation_id`),
CONSTRAINT `fk_schedules_reservations` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION

)

I've declared reservation_id as nullable (reservation_id INT(11) NULL DEFAULT NULL)

The problem is that CI doesn't seem to want to send a NULL value when I create the statement.

$data['status'] = $this->Schedule->get_status_open();
$data['reservation_id'] = null; 
$this->Schedule->update($s_id, $data);

That bit of code just generates the following error message

Error Number: 1452

Cannot add or update a child row: a foreign key constraint fails (`ethyme/schedules`, CONSTRAINT `fk_schedules_reservations` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

UPDATE `schedules` SET `status` = 'OPEN', `reservation_id` = '' WHERE `id` = '4'

From what I know, all you have to do is set the value to NULL and AR should pick up that it's a NULL value, but that doesn't seem to be the case here. Just keeps sending empty values.

I'm new to CI, do I have to do anything else to make it work? any ideas?

Answer 1

Try:

$this->db->set('reservation_id', NULL);

Could well be the $data approach doesn't like NULLs.

Answer 2

I handled it this way: extend the CI_Model class with this method to handle nulls. You have to call it each time you expect a null though. I don't use nulls that often, so this method is fine for when I need to set null. Just pass a string 'NULL' from the data array. This unsets the array member and sets it into the active record cache.

     class MY_Model extends CI_Model {

      public function __construct() {
          parent::__construct();
      }

      /* Handles null values for active record data array.  If 
       * $dataarray[$data_key] is string 'NULL',
       * use active record set method to set null and unset the 
       * string 'NULL'.  $data_array is by reference.
       * @param - array   the data array
       * @param - data_key - string  the key to be evaluated */
      protected function handle_null_active_record(&$data_array, $data_key){
        if ('NULL'==$data_array[$data_key]){
          $this->db->set($data_key, null);
          unset($data_array[$data_key]);
        }
      }

    }