I am having a bit of trouble with referential integrity errors on my database final exam project. I have tried to figure it out myself, but to no avail, and I am hoping someone here can point me in the right direction.
The problem is that I have three tables with the first two being parent tables to the third table. I am trying to construct a php script that will update the two parent tables and then insert the data into the child table. What I have thus far, I thought would work, but whenever I try to use it, I get the following error:
INSERT INTO Purchases (CustomerID, PurchaseOrderNo, PurchaseTotal, DateOfPurchase, SalesPersonID,SpecialOrder) VALUES ('10', '0000','100.00','0000-00-00','5555','N')
Cannot add or update a child row: a foreign key constraint fails ('xxxxxx'.'Purchases', CONSTRAINT 'Purchases_ibfk_1' FOREIGN KEY ('CustomerID') REFERENCES 'CustomerInfo'('CustomerID') ON DELETE CASCADE ON UPDATE CASCADE)'
Any thoughts where I have gone wrong?
########## FOREIGN KEY CHECK START ##########
$sql = "select count(*) as count from CustomerInfo where '$customerid' = CustomerID";
$result = mysqli_query($con,$sql)
or die('Error: ' . mysql_error());
$row = mysqli_fetch_assoc($result);
if ( $row['count']==0 ){
"INSERT INTO CustomerInfo ('CustomerID') VALUES ('$customerid');";
echo "<p>Customer ID Not Found. <br />New CustomerID Created.</p>";
}
$sql2 = "select count(*) as count from EmployeeInfo where '$salespersonid' = SalesPersonID;";
$result2 = mysqli_query($con,$sql)
or die('Error: ' . mysql_error());
$row2 = mysqli_fetch_assoc($result2);
if ( $row2['count']==0 ){
"INSERT INTO EmployeeInfo ('SalesPersonID')VALUES ('$salespersonid');";
echo "<p>Salesperson ID Not Found. <br />New Salesperson ID Created.</p>";
}
########## FOREIGN KEY CHECK END ##########
########## DATA ENTRY SQL STATEMENT START ##########
$sql3 = "INSERT INTO Purchases (CustomerID,
PurchaseOrderNo,
PurchaseTotal,
DateOfPurchase,
SalesPersonID,
SpecialOrder)
VALUES ('$customerid',
'$purchaseorderno',
'$purchasetotal',
'$dateofpurchase',
'$salespersonid',
'$specialorder')";
########### DATA ENTRY SQL STATEMENT END ##########
########## INPUT SUCCESS/FAILURE REPORTING#########
if (mysqli_query($con, $sql3)) {
echo "<P>Record Successfully Created</P><BR />";
} else {
echo "Error: " . $sql9. "<br>" . mysqli_error($con);
}
mysqli_close($con);
echo "<P>Connection Successfully Closed.</P>";