Codeigniter Activerecord update method refuses to insert NULL value

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 'O开发者_如何学GoPEN',
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?

Try:

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

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

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]);
        }
      }

    }

In CI the keyword NULL always needs to be uppercase. Try this simple change and see if that works. See the relevant bit of the userguide here.

Your reservations_id column references an id column in a reservations table, right? Have you checked that the column in the reservstions table is also allowed to have a null value? If its the primary key and auto-incremented then that could well be the problem. I dont think you can have a column as null in one table if its designated as a foreign key to a column that cant be null in the referenced table.  Have i explained that clearly for you?

From CI 3.0.0's documentation:

set() will also accept an optional third parameter ($escape), that will prevent data from being escaped if set to false.

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