Cannot add or update a child row: a foreign key constraint fails -> WHY??

[hass87]

im doing a simple insert statement, taking data from the forms. This will be insert into the clientbiodata and stakeholder tables.

the child table:

CREATE TABLE `clientbiodata` (

`ClientID` int(10) NOT NULL,

`Name` varchar(255) DEFAULT NULL,

`HealthcareRole` varchar(100) DEFAULT NULL,

`Handphone` int(10) DEFAULT NULL,

`OfficeNo` int(10) DEFAULT NULL,

`FaxNo` int(10) DEFAULT NULL,

`Email` varchar(100) DEFAULT NULL,

`Address` varchar(255) DEFAULT NULL,

`KeyInfo` text,

`CTSoic` varchar(50) DEFAULT NULL,

`CTSsic` varchar(50) DEFAULT NULL,

PRIMARY KEY (`ClientID`),

CONSTRAINT `clientbiodata_ibfk_1` FOREIGN KEY (`ClientID`) REFERENCES `stakeholder` (`StakeholderID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

 

the parent table:

 

CREATE TABLE `stakeholder` (

`StakeholderID` int(10) NOT NULL,

`StakeholderName` varchar(50) DEFAULT NULL,

`StakeholderType` varchar(50) DEFAULT NULL,

`Location` varchar(255) DEFAULT NULL,

PRIMARY KEY (`StakeholderID`),

CONSTRAINT `stakeholder_ibfk_1` FOREIGN KEY (`StakeholderID`) REFERENCES `engagement` (`EngagementID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

as you can notice, the id for both tables are the primary keys and they are not auto-increment.

i coded this to generate the id and insert into the table.

clientbiodata.php:

<?php

require ("mysqlConnect.php");

$result = mysql_query("Select * From Stakeholder");

$numRowsSH = mysql_num_rows($result);

echo $numRowsSH;

$result = mysql_query("Select * From ClientBiodata");

$numRowsCB = mysql_num_rows($result);

echo $numRowsCB;

 

$orgType = $_POST['organizationType']; //stakeholder

$orgName = $_POST['organizationName'];

 

$name = $_POST['name_client']; //clientbiodata

$email = $_POST['email_client'];

$hp = $_POST['mobile_client'];

$officeTel = $_POST['officetel_client'];

$fax = $_POST['fax_client'];

$address = $_POST['address_client'];

$healthcareRole = $_POST['HealthcareRole'];

$ctsOic = $_POST['ctsofficer'];

$ctsSic = $_POST['ctsofficer_second'];

$keyinfo = $_POST['keyinfo'];

 

if (!isset($_POST['submit_button'])) { // if page is not submitted to itself echo the form

 

echo "ERROR!!";

}

$newNumSH = $numRowsSH+1;

$newNumCB = $numRowsCB+1;

$query = "INSERT INTO Stakeholder (StakeholderID,StakeholderName,StakeholderType) VALUES ('".($newNumSH)."','".$orgName."','".$orgType."')";

mysql_query($query) or die(mysql_error());

echo "Data inserted! <br/>";

 

$query1 = "INSERT INTO ClientBiodata (ClientID, Name, HealthcareRole, Handphone, OfficeNo, FaxNo, Email, Address, CTSoic, CTSsic, KeyInfo) VALUES ('".($newNumCB)."','".$name."', '".$email."', '".$hp."',  '".$officeTel."',  '".$fax."',  '".$address."',  '".$healthcareRole."',  '".$ctsOic."',  '".$ctsSic."',  '".$keyinfo."')";

mysql_query($query1) or die(mysql_error());

echo "Data inserted!";

 

?>

so why is the insert still fail?

do i need to change the ON DELETE to CASCADE?

do i need to change ON UPDATE to CASCADE too?

please explain to me as im such a noob..

Thanks =)

[hass87]

im sorry but the version of mysql im using is ver 5.1.36

thanks again

[fenway]

Can't possibly debug that until I see actual mysql queries.  And why aren't the PKs auto-increment?

[hass87]

The query is right above as i have displayed: Its the $query

However, thanks for your help.

I solved it by inserting data in the parent table first, and then to the child table.

Therefore, my form flow in a way that the parent table will be fill first.

Thanks again 

[hass87]

But now the same error is here again!!! :'(

I don't really understand as i can do an insert successfully before.

the error is this again: Cannot add or update a child row: a foreign key constraint fails (`ctsdb`.`assignments`, CONSTRAINT `assignments_ibfk_1` FOREIGN KEY (`AssignmentID`) REFERENCES `ctsstaff` (`StaffID`) ON DELETE CASCADE ON UPDATE CASCADE)

 

anybody has any experience in this?

I need help!!

[fenway]

Has nothing to do with ON DELETE / ON UPDATE, especially for an INSERT.

 

It just means that you don't have a matching id in the other references table.

 

And when I ask for a query, I don't mean php code.