Using CodeIgniter's delete() unintentionally deletes related records in other tables

Question

This is my code:

public function remove_employee( $emp_no, $now ) {
    $this->db->delete('employees', array('emp_no' => $emp_no));
    $this->db->flush_cache();

    $this->db->start_cache();
    $this->db->where('emp_no', $emp_no);
    $this->db->where('to_date', '9999-01-01');
    $this->db->update('dept_emp', array('to_date' => $now));
    $this->db->stop_cache();
    $this->db->flush_cache();   

    $this->db->start_cache();
    $this->db->where('emp_no', $emp_no);
    $this->db->where('to_date', '9999-01-01');
    $this->db->update('salaries', array('to_date' => $now));
    $this->db->stop_cache();
    $this->db->flush_cache();   

    $this->db->start_cache();
    $this->db->where('emp_no', $emp_no);
    $this->db->where('to_date', '9999-01-01');
    $this->db->update('titles', array('to_date' => $now));
    $this->db->stop_cache();
    $this->db->flush_cache();   

    return;
} //END REMOVE EMPLOYEE

When I run this code it deleted my records. I don't understand why.

I want it to: UPDATE TABLE WHERE CONDITION_1 AND CONDITION_2 = B

PS:

**$now** is todays date (i.e. 2012-12-25)
**$emp_no** is a unique employee number i.e. 500122

Answer 1

Possible way to debug this would be to comment out the $this->db->delete().

Try changing these lines:

$this->db->delete('employees', array('emp_no' => $emp_no));
$this->db->flush_cache();

To:

//$this->db->delete('employees', array('emp_no' => $emp_no));
//$this->db->flush_cache();

Then try it and see if your records are still there. If your update was successful and your records are still in the tables, you might have a Foreign Key using Delete Cascade. Which means if you delete the record from the employees table you will also be deleting the records from dept_emp, salaries and titles.

CodeIgniter Active Record Class: http://ellislab.com/codeigniter/user-guide/database/active_record.html

InnoDB Foreign Key: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Check Foreign Keys: How do I see all foreign keys to a table or column?