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

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?

Answer 2

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.

Answer 3

Try:

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

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