How to solve Error Number: 1452 - Cannot add or update a child row: a foreign key constraint fails

Question

Error Number: 1452

Cannot add or update a child row: a foreign key constraint fails (smp.student, CONSTRAINT FK_ParentStudent FOREIGN KEY (p_id) REFERENCES parent (p_id) ON DELETE CASCADE ON UPDATE CASCADE)

INSERT INTO student (s_id, student_code, f_name, l_name, dob, gender, address, tel, username, password) VALUES ('', 'cbg', 'sdfsdf', 'sdfsd', '02/13/2019', 'male', 'fgfhnf', '', 'admin', 'pAQ7oL+r8QJNgy2siN5moyJUhrgSj5tq3Ai5U2ngMyvaQZDsS9ooPbfcCm8qKEWi1C6nbYdMCyscz6ngU+1Tiw==')

Filename: C:/xampp/htdocs/SMP/system/database/DB_driver.php

Line Number: 691

I get this error, and i think its something to do with my function in the controller

public function register_students()     {

    // $this->load->model('Register_model','multi_model',TRUE);
    $encrypted_password1 = $this->encrypt->encode($this->input->post('p_pwd'));
    $parent_data = array(
        'p_id' => '',
        'parent_code' => $this->input->post('parent_code'),
        'f_name' => $this->input->post('p_first_name'),
        'l_name' => $this->input->post('p_last_name'),
        'dob' => $this->input->post('p_dob'),
        'address' => $this->input->post('p_address'),
        'tel' => $this->input->post('p_tel_no'),
        'email' => $this->input->post('email'),
        'username' => $this->input->post('p_username'),
        'password' => $encrypted_password1,
    );
        // var_dump($student_data);
        // var_dump($parent_data);

    $result = $this->Model_Action->insertTable('parent', $parent_data);
    $encrypted_password = $this->encrypt->encode($this->input->post('pwd'));
    $student_data = array(
        's_id' => '',
        'student_code' => $this->input->post('student_code'),
        'f_name' => $this->input->post('first_name'),
        'l_name' => $this->input->post('last_name'),
        'dob' => $this->input->post('dob'),
        'gender' => $this->input->post('gender'),
        'address' => $this->input->post('address'),
        'tel' => $this->input->post('tel_no'),
        'username' => $this->input->post('username'),
        'password' => $encrypted_password,
    );
    $result = $this->Model_Action->insertTable('student', $student_data);
    // $result = $this->multi_model->student_register($student_data, $parent_data);
    if($result)         {
        redirect('student');
    }
    else
    {
        redirect('student');
    }
}

model

 function insertTable($table, $data) {
     $this->db->insert($table, $data);
     return $this->db->insert_id();
 }

CREATE TABLE student ( s_id int(11) NOT NULL AUTO_INCREMENT, student_code varchar(50) NOT NULL, f_name varchar(150) NOT NULL, l_name varchar(250) NOT NULL, DOB text NOT NULL, gender enum('male','female') NOT NULL, address varchar(450) NOT NULL, tel int(50) NOT NULL, username varchar(100) NOT NULL, password varchar(150) NOT NULL, p_id int(11) NOT NULL, PRIMARY KEY (s_id), KEY s_id (s_id), KEY p_id (p_id), CONSTRAINT FK_ParentStudent FOREIGN KEY (p_id) REFERENCES parent (p_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

CREATE TABLE parent ( p_id int(11) NOT NULL AUTO_INCREMENT, parent_code varchar(100) NOT NULL, f_name varchar(150) NOT NULL, l_name varchar(250) NOT NULL, DOB text NOT NULL, address varchar(250) NOT NULL, tel varchar(50) NOT NULL, email varchar(250) NOT NULL, username varchar(250) NOT NULL, password varchar(250) NOT NULL, PRIMARY KEY (p_id), KEY p_id (p_id) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

Francisco de Castro Francisco de Castro · Accepted Answer · 2019-02-10T15:26:40

Analyzing the PHP code I figured that you insert the parent first and the function you use to insert returns a parent id but you are not using it on the student insert. If you add it to the student array it will solve you problem:

public function register_students()     {
   {...}
   $p_id = $this->Model_Action->insertTable('parent', $parent_data); // Parent it returned

   $encrypted_password = $this->encrypt->encode($this->input->post('pwd'));
   $student_data = array(
       's_id' => '',
       'p_id' => $p_id, // Adding parent id to student_data
       {...}
   );

   $result = $this->Model_Action->insertTable('student', $student_data);
   {...}

}

Explanation about the SQL error

You are getting this SQL exception error because on the student table you defined a not null foreign key p_id referencing p_id on the parent table:

CREATE TABLE student (  
  s_id int(11) NOT NULL AUTO_INCREMENT,  
  student_code varchar(50) NOT NULL,  
  f_name varchar(150) NOT NULL,  
  l_name varchar(250) NOT NULL,  
  DOB text NOT NULL,  gender enum('male','female') NOT NULL,  
  address varchar(450) NOT NULL,  
  tel int(50) NOT NULL,  
  username varchar(100) NOT NULL,  
  password varchar(150) NOT NULL,  
  p_id int(11) NOT NULL, 
  PRIMARY KEY (s_id), 
  KEY s_id (s_id), 
  KEY p_id (p_id), 
  CONSTRAINT FK_ParentStudent FOREIGN KEY (p_id) REFERENCES parent (p_id) ON DELETE CASCADE ON UPDATE CASCADE 
)ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

Note that p_id int(11) NOT NULL makes it impossible for you to pass anything rather than a valid id reference to the parent table. That said, you cannot pass ''or null or any other value than a valid id from the parent table. If you want this foreign key to have a default value edit your SQL to include a default value to p_id on the student table:

p_id int(11) DEFAULT NULL

How to solve Error Number: 1452 - Cannot add or update a child row: a foreign key constraint fails

2 Answers