PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Wednesday, February 23, 2022

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

 February 23, 2022     codeigniter, php, phpmyadmin     No comments   

Issue

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


Solution

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



Answered By - Francisco de Castro
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing