Cannot add or update a child row: a foreign key constraint fails

Databases
1

Hi

I am trying a simple InnoDB table with foreign key constraints, but i can’t seem to pass the constrained value from HTML/PHP form … I have been on this for days and trawled forums and books, but no luck … any hints will be greatly appreaciated.

Here are the table structures for both tables

–
– Table structure for table parent



CREATE TABLE IF NOT EXISTS `parent` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

–
– Table structure for table child



CREATE TABLE IF NOT EXISTS `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `child_name` varchar(50) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `par_ind` (`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=61 ;

And this is the form for inserting a child, it retrieves the foreing parent_id constraint on child insert, and loads it into the form:

<?php include("database.php");

$id = $_GET['id'];

$query = "SELECT * FROM parent WHERE id=$id"; 	

$result = mysql_query($query) or die(mysql_error());	
	
$row = mysql_fetch_array( $result ); // Get selected entry  ?>	

<form name="test_fk" action="test_exec.php" method="POST">		

<table >
<tr>	
  <td valign="top"><label for="parent_id"> Parent id </label></td>	
  <td><input type="text" disabled="disabled" name="parent_id" id="parent_id" value="<?php echo $row['id']; ?>" /> </td>	
</tr>
<tr>	
  <td valign="top"><label for="child_name"> Child name </label></td>	
  <td><input type="text" name="child_name" id="child_name" /> </td>	
</tr>
<tr>
  <td> </td>
  <td> <input name="Submit" type="submit" value="Save "/></td>
</tr>
</table>
</form>

and here is the PHP ‘insert’ script

<?php

//	Error reporting
error_reporting (E_ALL ^ E_NOTICE);

include("database.php");


if(isset($_POST[Submit])){

  $id = $_GET['id'];

  $query = "INSERT INTO child (id, parent_id, child_name) VALUES ('', '$_POST[parent_id]','$_POST[child_name]')";	
	
  echo $query;

  $result = mysql_query($query) or die(mysql_error());
  //$result = mysql_query($query) or die('<br /><br /> MySQL query failed');

  } // End else
	
	echo $child_name;
 ?>

And this is the MySQL error i get:

INSERT INTO child (id, parent_id, child_name) VALUES (‘’, ‘’,‘Insert’)Cannot add or update a child row: a foreign key constraint fails (widss.child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE)

What i cant figure out is why the parent_id value is not inserting … it retrieves fine in the form …

Any help would be greatly appreaciated. Thanks in advance.

2

I don’t see why your query itself fails, since you haven’t posted the constraint you set.
However, your code that you posted has some terrible practices - the thing, if you type something like:

$_POST[parent_id];

The index parent_id will be considered as a definition in terms of PHP since you haven’t quoted it such as:

$_POST['parent_id'];

I’m guessing this is a test code or project running only on your localhost, but still - it would be good if you cleaned the user input sent trough the form since your code is susceptible to SQL injection attack.

Another thing that I can see is in your INSERT query.
You’re specifying an empty value for “id” column, which is auto_increment by default.
You don’t need to do that.

$parent_id = intval($_POST['parent_id']);
$child_name = mysql_real_escape_string($_POST['child_name']);

$query = "INSERT INTO child (parent_id, child_name) VALUES ($parent_id','$child_name')";
3

Thank you Blue

I have made the changes … i have progress, but not quiet. I am able to save now, with a parent_id of ‘0’ - regardless of what parent is selected :S

This is the query print:

INSERT INTO child (parent_id, child_name) VALUES (‘0’,‘Test name’)

This is the script

if(isset($_POST[Submit])){

  $id = $_GET['id'];
  $parent_id = intval($_POST['parent_id']);
  $child_name = mysql_real_escape_string($_POST['child_name']);

  $query = "INSERT INTO child (parent_id, child_name) VALUES ('$parent_id','$child_name')";

//$query = "INSERT INTO child (id, parent_id, child_name) VALUES ('', '$_POST[parent_id]','$_POST[child_name]')";	
	
  echo $query;

  $result = mysql_query($query) or die(mysql_error());
  //$result = mysql_query($query) or die('<br /><br /> MySQL query failed');

  } // End else
	
  echo $child_name;

I thought $id = $_GET[‘id’]; retrieves the parent ID from the submitted from and passes to the insert query?

4

Your $_POST[‘parent_id’] is empty.
The reason is that you have the following line:

<input type="text" disabled="disabled" name="parent_id" id="parent_id" value="<?php echo $row['id']; ?>" />

If an HTML field has disabled attribute set to true, it won’t be passed when the form is submitted.
If you want to make that field such that you can view the value and not change it, use the following:

<input type="text" readonly="readonly" name="parent_id" id="parent_id" value="<?php echo $row['id']; ?>" />

Form should be working now.
You won’t be getting zeroes anymore, hence the constraint shouldn’t fail.

5

BLUE, i love you!

Thank you so much. Would never have figured that out.