help with a foreign key constraint fails

Darkmatter5 · June 2, 2008

Here is the gist of the error

Cannot add or update a child row: a foreign key constraint fails (`byrnjobdb/clients`, CONSTRAINT `fk_state` FOREIGN KEY (`state_id`) REFERENCES `states` (`state_id`) ON DELETE CASCADE ON UPDATE CASCADE)

What does that mean?

Here are the two tables in question.

states - TABLE

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL auto_increment,
  `state` varchar(50) NOT NULL,
  `state_abbrev` char(2) NOT NULL,
  PRIMARY KEY  (`state_id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=latin1;

clients - TABLE

CREATE TABLE `clients` (
  `client_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `company_name` varchar(50) default NULL,
  `contact_title` varchar(50) default NULL,
  `address` varchar(30) NOT NULL,
  `city` varchar(15) NOT NULL,
  `state_id` int(11) NOT NULL,
  `zip_code` varchar(10) NOT NULL,
  `home_phone` varchar(12) default NULL,
  `work_phone` varchar(12) default NULL,
  `work_phone_extension` varchar(4) default NULL,
  `fax_phone` varchar(12) default NULL,
  `email` varchar(50) default NULL,
  PRIMARY KEY  (`client_id`),
  KEY `fk_state` (`state_id`),
  CONSTRAINT `fk_state` FOREIGN KEY (`state_id`) REFERENCES `states` (`state_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5380 DEFAULT CHARSET=latin1;

Any ideas what this means?

fenway · June 2, 2008

What did you do to trigger the error? Presumably to the parent row...

Darkmatter5 · June 3, 2008

I used the following form to test if to see if the code worked.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Byrn & Associates, Inc. online Job database</title>
<link rel="stylesheet" type="text/css" href="library/site_config.css" />
<script src="library/selectClient.js"></script>
</head>

<body>
<form method="post">
<table width="770" border="0" align="center" class="sample">
  <caption class="style20">ADD CLIENT ENTRY</caption>
  
  
  <tr>
    <td width="119" class="style22"> </td>
    <td width="149" class="style21"><div align="center">New data database</div></td>
    <td width="484" class="style21"><div align="center">Description</div></td>
    </tr>
  <tr>
    <td class="style22"><strong>First name:</strong></td>
    <td><input name="first_name" type="text" tabindex="1" /></td>
    <td rowspan="2"><span class="style23">Enter clients first and last name. If the primary identifier of the client is a company name, also include a person as a contact for the company.</span></td>
    </tr>
  <tr>
    <td class="style22"><strong>Last name:</strong></td>
    <td><input name="Last_name" type="text" tabindex="2" /></td>
  </tr>
  <tr>
    <td class="style22"><strong>Company name:</strong></td>
    <td><input name="company_name" type="text" tabindex="3" /></td>
    <td> </td>
  </tr
  ><tr>
    <td class="style22"><strong>Contact Title:</strong></td>
    <td><input name="contact_title" type="text" tabindex="4" /></td>
    <td><span class="style23">Persons job title in the company i.e. CEO, CIO, etc...</span></td>
  </tr>
  <tr>
    <td class="style22"><strong>Address:</strong></td>
    <td><input name="address" type="text" tabindex="5" /></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><strong>City:</strong></td>
    <td><input name="city" type="text" tabindex="6" /></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><strong>State:</strong></td>
    <td>
      <?php
        include 'library/dbconfig.php';
	include 'library/opendb.php';

	$query="SELECT state_id, state, state_abbrev
			FROM byrnjobdb.states
			ORDER BY state ASC";
	$result=mysql_query($query);
	echo "<select id='state' method='get' tabindex='7'>";
	echo "<option>---Select---</option>";
	while ($row=mysql_fetch_array($result)) {
		$r1=$row['state_id'];
		$r2=$row['state']. ", " .$row['state_abbrev'];
		echo "<option value='$r1'>$r2</option>";
	}
	echo "</select>";

	include 'library/closedb.php';
  ?></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><strong>Zip code:</strong></td>
    <td><input name="zip_code" type="text" tabindex="7" /></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><strong>Home phone:</strong></td>
    <td><input name="home_phone" type="text" tabindex="8" /></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><strong>Work phone:</strong></td>
    <td><input name="work_phone" type="text" tabindex="9" /></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><strong>Extension:</strong></td>
    <td><input name="work_phone_extension" type="text" tabindex="10" /></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><strong>Fax phone:</strong></td>
    <td><input name="fax_phone" type="text" tabindex="11" /></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><strong>Email:</strong></td>
    <td><input name="email" type="text" tabindex="12" /></td>
    <td> </td>
  </tr>
  <tr>
    <td class="style22"><div align="center">
      <input name="addclient" type="submit" id="addclient" value="Submit new data" />
    </div></td>
    <td><div align="right" class="style3">Function Status =></div></td>
    <td><?php
    	if(isset($_POST['addclient'])) {
		include 'library/dbconfig.php';
		include 'library/opendb.php';

		$first_name=$_POST['first_name'];
		$last_name=$_POST['last_name'];
		$company_name=$_POST['company_name'];
		$contact_title=$_POST['contact_title'];
		$address=$_POST['address'];
		$city=$_POST['city'];
		$state=$_POST['state'];
		$zip_code=$_POST['zip_code'];
		$home_phone=$_POST['home_phone'];
		$work_phone=$_POST['work_phone'];
		$work_phone_extension=$_POST['work_phone_extension'];
		$fax_phone=$_POST['fax_phone'];
		$email=$_POST['email'];

		//put new client data from form into the clients table
		foreach (array('first_name', 'last_name', 'company_name', 'contact_title',  'address', 'city', 'state', 'zip_code', 'home_phone', 'work_phone', 'work_phone_extension', 'fax_phone', 'email') as $field) {
			if ($_POST[$field]) {
				$field_name[]=$field;
				$field_value[]="'{$_POST[$field]}'";
			}
		}

		$fields=join(", ", $field_name);
		$values=join(", ", $field_value);
		$insertdata="INSERT INTO byrnjobdb.clients ($fields) VALUES ($values)";
		mysql_query($insertdata) or die(mysql_error() . ": $insertdata");

		echo "NEW CLIENT ADDED...<br>";
		include 'library/closedb.php';
	}
?></td>
    </tr>
</table>
</form>
</body>
</html>

Any thoughts?

fenway · June 3, 2008

So it was the insert statement? Which mysql statement was the trigger?

Darkmatter5 · June 3, 2008

Yes the insert. The error came from the line...

mysql_query($insertdata) or die(mysql_error() . ": $insertdata");

fenway · June 3, 2008

Are you sure the state_id exists is the states table?

Darkmatter5 · June 3, 2008

Here is the code for the entire two tables.

employees

CREATE TABLE `employees` (
  `employee_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `address` varchar(50) default NULL,
  `city` varchar(50) default NULL,
  `state_id` int(11) default NULL,
  `zip_code` varchar(10) default NULL,
  `home_phone` varchar(12) default NULL,
  `cell_phone` varchar(12) default NULL,
  `active_employee` enum('No','Yes') NOT NULL default 'Yes',
  `lic_rpls` enum('No','Yes') NOT NULL default 'Yes',
  `lic_pe` enum('No','Yes') NOT NULL default 'Yes',
  PRIMARY KEY  (`employee_id`),
  KEY `fk_state_id` (`state_id`),
  CONSTRAINT `fk_state_id` FOREIGN KEY (`state_id`) REFERENCES `states` (`state_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

states

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL auto_increment,
  `state` varchar(50) NOT NULL,
  `state_abbrev` char(2) NOT NULL,
  PRIMARY KEY  (`state_id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=latin1;

fenway · June 3, 2008

No, I meant that the actual value you're inserting has a corresponding record.

Darkmatter5 · June 3, 2008

I think I'm simply not understanding the usage of foreign keys. I just deleted and foreign keys and just used a JOIN LEFT option in my query select and I got exactly what I'm looking for. The query is rather long, but it works. Is there another way with shorter code to accomplish the same result? Also can someone explain to me when and when not to use foreign keys?

Here's my query:

SELECT employees.employee_id, employees.first_name, employees.last_name, employees.address, employees.city, states.state, employees.zip_code, employees.home_phone, employees.cell_phone, employees.active_employee, employees.lic_rpls, employees.lic_pe

FROM byrnjobdb.employees

LEFT JOIN byrnjobdb.states

ON byrnjobdb.employees.state_id=byrnjobdb.states.state_id

WHERE employee_id=$employee_id

$employee_id is a $_POST variable selected from a dropdown list.

Thanks in advance!

fenway · June 4, 2008

Where did that INSERT statement go? Where did this SELECT query come from?

Sign In

help with a foreign key constraint fails

Recommended Posts

Darkmatter5

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Darkmatter5

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Darkmatter5

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Darkmatter5

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Darkmatter5

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information