phpmyadmin Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    If p_id is your primary key you are not supposed to pass it while insert. Remove this line 'p_id' => '', from $parent_data

    If you are unable to delete or update data. Please check foreign_key restriction on your database table, you may have ristricted it for so.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search