Foreign Key Constraint Fails

[KanixD]

I've hit a bit of a roadblock with one of my projects and I cant seem to get past it. I am trying to add a new row to one of my tables, but the foreign key seems to be causing me problems. As you will see from my code below, I am trying to insert a row into my 'job' table.

 

Error:

Query failed: Cannot add or update a child row: a foreign key constraint fails (`kanix_support_desk`.`job`, CONSTRAINT `job_ibfk_1` FOREIGN KEY (`agentId`) REFERENCES `agents` (`agentId`))

 

Tables:

CREATE TABLE IF NOT EXISTS `agents` (
  `agentId` int(11) NOT NULL AUTO_INCREMENT,
  `AgentFirstname` varchar(50) DEFAULT NULL,
  `AgentSurname` varchar(50) DEFAULT NULL,
  `username` varchar(25) DEFAULT NULL,
  `password` varchar(25) DEFAULT NULL,
  `rank` varchar(12) NOT NULL,
  PRIMARY KEY (`agentId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


CREATE TABLE IF NOT EXISTS `job` (
  `jobId` int(11) NOT NULL AUTO_INCREMENT,
  `agentId` int(11) NOT NULL,
  `date` date NOT NULL,
  `time` time DEFAULT NULL,
  `jobName` varchar(100) NOT NULL,
  `jobDescription` varchar(9999) NOT NULL,
  `jobstatus` varchar(50) NOT NULL,
  `customerFirstname` varchar(50) DEFAULT NULL,
  `customerSurname` varchar(50) DEFAULT NULL,
  `CustomerTelephoneNo` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`jobId`),
  KEY `agentId` (`agentId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

 

Php:

session_start();

include("dbConnect.php");

$agent = $_SESSION['agent'];		//Set agent name and ID variables
$agentid = $_SESSION['identity'];

//Check that no section of the form was left blank
if($_POST['fname'] != null && $_POST['sname'] != null && $_POST['phone'] != null && $_POST['jtitle'] != null && $_POST['jdesc'] != null){

mysql_query("INSERT INTO job (jobId, agentId, date, time, jobName, jobDescription, jobstatus, customerFirstname, customerSurname, customerTelephoneNo) VALUES (null, '".$agentid."', CURDATE(), NOW(), '".$_POST['jtitle']."', '".$_POST['jdesc']."', 'Live', '".$_POST['fname']."', '".$_POST['sname']."', '".$_POST['phone']."')") or die ("Query failed: " . mysql_error());


header("location:reception.php?content=3");


} else{		//Error message
header("location:reception.php?content=4");
}

[AyKay47]

Then the `agentId` value that you are trying to insert into the `job` table is not found in the `agents` table.

This is the nature of foreign keys, they maintain the referential integrity of the database.

[KanixD]

I pulled the value from my agents table straight into $_SESSION['identity'] when the user logs in. Echoing this to the user shows a value of 2, and I know there is an 'agentId' with a value of 2 because I'm looking at it.

[ManiacDan]

You're not understanding what we're saying:

 

This is not a PHP problem.  It has nothing to do with PHP, the session, or anything else.  You are trying to insert a row into a MySQL table which has a foreign key constraint on it.  That constraint requires that the agentId you insert already be present in another table.  it is not, so the insert fails.  That is the purpose of foreign keys, and you would get this error if you tried to insert this row by hand directly onto the database server.