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

[baldiajaib]

I'm currently working on an online project evaluation system which contain 2 tables which are company and project

 

the company table stores the company's information while the project table contains lists of project's score @ grades labelled x1, x2, x3, x4, x5, x6, x7, x8, x9.

 

the aim is to calculate x1, x2, x3 and store it into project table. after processing all the calculation, i then post it into the database. the problem is this error:

 

    "Cannot add or update a child row: a foreign key constraint fails (`movedb`.`project`, CONSTRAINT `project_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `company` (`companyId`) ON DELETE CASCADE ON UPDATE CASCADE)"

 

 

I've tried various solution that i googled but it seems that al of them are not working...

Anyway, thank u for taking the time to read and understand my problem

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Server version: 5.1.37

 

CREATE TABLE company
(
companyId INT NOT NULL,
username VARCHAR(30),
password VARCHAR(30),
company_name VARCHAR (255),
PRIMARY KEY (companyId )
) TYPE = INNODB;

CREATE TABLE project
(
projectId INT NOT NULL,
x1 DOUBLE,
x2 DOUBLE,
x3 DOUBLE,
x4 DOUBLE,
x5 DOUBLE,
x6 DOUBLE,
x7 DOUBLE,
x8 DOUBLE,
x9 DOUBLE,
companyId INT NOT NULL,
PRIMARY KEY(projectId),
INDEX (companyId),
FOREIGN KEY (companyId) REFERENCES company (companyId) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = INNODB;

 

 

company table

 

project table

 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

<?php
ob_start();
//start session
session_start();

if (!isset($_SESSION["manager"])) {
    header("location: Login.php"); 
    exit();
}
//Be sure to check that this manager SESSION value is in fact in the database
$managerID = preg_replace('#[^0-9]#i', '', $_SESSION["id"]); // filter everything but numbers and letters
$manager = preg_replace('#[^A-Za-z0-9]#i', '', $_SESSION["manager"]); // filter everything but numbers and letters
$password = preg_replace('#[^A-Za-z0-9]#i', '', $_SESSION["password"]); // filter everything but numbers and letters
//Run mySQL query to be sure that this person is an admin and that their password session var equals the database information
//Connect to the MySQL database  
include "storescripts/connect_to_mysql.php"; 
$sql = mysql_query("SELECT * FROM company WHERE companyId='$managerID' AND username='$manager' AND password='$password' LIMIT 1"); // query the person
// ------- MAKE SURE PERSON EXISTS IN DATABASE ---------
$existCount = mysql_num_rows($sql); // count the row nums
if ($existCount == 0) { // evaluate the count
 echo "Your login session data is not on record in the database. Go to <a href='Login.php'>Login Page</a>";
     exit();
}

//IF POST SUBMIT FROM PROJEK 1
//CALCULATE X1
if ($_POST['submit']) {

$_1 = $_POST['q1']; 
$_2 = $_POST['q2'];
$_3 = $_POST['q3'];
$_4 = $_POST['q4']; 
$_5 = $_POST['q5']; 
$_6 = $_POST['q6']; 
$_7 = $_POST['q7']; 
$_8 = $_POST['q8']; 
$_9 = $_POST['q9'];

$k = $_1 + $_2 + $_3 + $_4 + $_5 + $_6 + $_7 + $_8 + $_9;
$n = 9*4;
$resultX1 = $k/$n;

//echo "X1:  " . $resultX1 . "</br>";

$_SESSION['resultX1'] = $resultX1;
}

//IF POST SUBMIT ON THIS PAGE, PROJEK 2
//Calculate X2 & x3
if ($_POST['hantar']) {

$_10 = $_POST['q10'];
$_11 = $_POST['q11'];
$_12 =  $_POST['q12'];

$j = $_10 + $_11 + $_12;
$m = 3*4;
$resultX2 = $j/$m;

//Calculate X3
$_13 = $_POST['q13'];
$_14 = $_POST['q14'];
$_15 = $_POST['q15'];
$_16 = $_POST['q16'];

$p = $_13 + $_14 + $_15 + $_16;
$q = 4*4;
$resultX3 = $p/$q;

//echo "X3:  " . $resultX3 . "</br>";
//echo "X2:  " . $resultX2 . "</br>";

$_SESSION['resultX2'] = $resultX2;
$_SESSION['resultX3'] = $resultX3;

$exone = $_SESSION['resultX1'];
$extwo = $_SESSION['resultX2'];
$exthree = $_SESSION['resultX3'];

// INSERTING INFO INTO DATABASE CUSTOMERINFO

$query = "INSERT INTO project(x1,x2,x3) VALUES ('$exone','$extwo','$exthree')";
mysql_query($query) or die(mysql_error());
mysql_insert_id();
mysql_close($connection);

echo '<span class="style3">Anda telah berjaya menilai Kategori Projek. Tahap Pengurusan Skor Projek akan dipaparkan sebentar lagi..</span>';
header("Refresh: 3; url=\"skopprojek.php\"");
//exit();
}
?>

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[ignace]

INSERT INTO project(x1,x2,x3, companyId) VALUES ('$exone','$extwo','$exthree', ??)

 

You need to pass the companyId as your foreign key enforces that the inserted value exists in the company table, and NULL does not.

[baldiajaib]

INSERT INTO project(x1,x2,x3, companyId) VALUES ('$exone','$extwo','$exthree', ??)

 

You need to pass the companyId as your foreign key enforces that the inserted value exists in the company table, and NULL does not.

 

so does it mean that the null shouldn't be there at the first place?does the foreign key will be auto generated?

[kartul]

Try emptying the tables and then add foreign keys.

[baldiajaib]

Try emptying the tables and then add foreign keys.

 

ok so i've deleted all data and tried reentering is, but still the same error came out. do u mean that i should add foreign keys in the company_id first before prompting the data from php to mysql?

[kartul]

Try emptying the tables and then add foreign keys.

 

ok so i've deleted all data and tried reentering is, but still the same error came out. do u mean that i should add foreign keys in the company_id first before prompting the data from php to mysql?

Nope. You did right. I don't know then, it worked for me tho ...

[ignace]

@kartul It worked for you because you are using MyISAM

 

@baldiajaib companyId will only be auto-generated when you insert something in company table not in project table. In your project table you MUST give a companyId as it's a foreign key and you specified he column as NOT NULL. Inserting a record into project table without a companyId specified will lead to give that column a value of NULL, which by NOT NULL means 0 and 0 is not a PK in your company table.

 

So either specify the column as nullable (not recommended) OR make sure you always specify a value for companyId in your projects table (recommended)

[baldiajaib]

@kartul It worked for you because you are using MyISAM

 

@baldiajaib companyId will only be auto-generated when you insert something in company table not in project table. In your project table you MUST give a companyId as it's a foreign key and you specified he column as NOT NULL. Inserting a record into project table without a companyId specified will lead to give that column a value of NULL, which by NOT NULL means 0 and 0 is not a PK in your company table.

 

So either specify the column as nullable (not recommended) OR make sure you always specify a value for companyId in your projects table (recommended)

 

good idea friend, i did like what u said. where i took the companyID from the company table --> put it into a $_SESSION['id'] --> when i want to input data into the projectID, i placed the session ID as well, where $id = $_SESSION['id'] and input the companyID that was logging in into the project table..it really works.

 

before that, here's the error and the correction:

 

error

$query = "INSERT INTO project(x1,x2,x3) VALUES ('$exone','$extwo','$exthree')";

 

correction

$query = " INSERT INTO project (projectId, x1, x2, x3, companyId) VALUES (2, '$exone','$extwo','$exthree','$id')";

 

Problem solved in a different method, thanks to u guys. really appreciate it.

 

 

by the way, is it possible for me to auto_increment the projectID without putting the value of 2 in my correction $query? how does the aut_increment work? do i just leave the space for projectID empty?

[ignace]

If projectId is an AUTO_INCREMENT NOT NULL PRIMARY KEY value then you can leave set it to NULL or DEFAULT

 

$query = " INSERT INTO project (projectId, x1, x2, x3, companyId) VALUES (NULL, '$exone','$extwo','$exthree','$id')";

 

or

 

$query = " INSERT INTO project (projectId, x1, x2, x3, companyId) VALUES (DEFAULT, '$exone','$extwo','$exthree','$id')";