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

[theophilusnwizu]

Dear Good ever responding friends,

I have the error that held me down for sometime. I always recieve this mail "'Cannot add or update a child row: a foreign key constraint fails" whenever i try to submit data. Does it mean my foriegn key is not prperly set? I don't seem to find the problem. Below is the mysql code and php newcustomer code respectively:

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 07, 2010 at 08:40 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bank`
--

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

--
-- Table structure for table `accounts`
--

CREATE TABLE IF NOT EXISTS `accounts` (
`accid` int(4) NOT NULL AUTO_INCREMENT,
`accno` varchar(10) NOT NULL,
`balance` int(11) NOT NULL,
`type` varchar(100) NOT NULL,
`active` varchar(5) NOT NULL,
PRIMARY KEY (`accid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `accounts`
--


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

--
-- Table structure for table `customer`
--

CREATE TABLE IF NOT EXISTS `customer` (
`cusid` int(4) NOT NULL AUTO_INCREMENT,
`busname` varchar(50) NOT NULL,
`busnat` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
`address` text NOT NULL,
`email` varchar(50) NOT NULL,
`avermon` varchar(50) NOT NULL,
`nextkin` varchar(50) NOT NULL,
`pin` varchar(10) NOT NULL,
`picture` blob NOT NULL,
`sign` blob NOT NULL,
`date` date NOT NULL,
`acc_id` int(4) NOT NULL,
PRIMARY KEY (`cusid`),
KEY `INDEX` (`acc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `customer`
--


--
-- Constraints for dumped tables
--

--
-- Constraints for table `customer`
--
ALTER TABLE `customer`
ADD CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`acc_id`) REFERENCES `accounts` (`accid`) ON DELETE CASCADE ON UPDATE CASCADE;

and the newcustomer php code:

function newcustomer($accno,$type,$balance,$active,$name,$pin,$address,$email,$picture,$busname,$busnat,$sign ,$date,$avermon,$nextkin){

//first insert customer details, then use the newly generated id
$sql = "INSERT INTO customer (name,busname,busnat,address,email,date,avermon,nextkin,picture,sign,pin) values ('".$name."','".$busname."','".$busnat."','".$address."','".$email."','".$date."','".$avermon."','". $nextkin."','".$picture."','".$sign."','".$pin."')";
if(!mysql_query($sql)){
throw new Exception(mysql_error());
return FALSE;
}else{
$newid = mysql_insert_id();
return TRUE;
}

if($newid > 0){
$sql_acc = "INSERT INTO accounts (accno,type,balance,active,cusid) values ('".$accno."','".$type."','".$balance."','1','".$newid."')";

if(!mysql_query($sql_acc)){
throw new Exception(mysql_error());
}
return TRUE;
}
}

Thanks in advance.

[Old Pedant]

Your constraint is basically saying "the field accid in every record in the Customer table *must* match the field accid in *some* record in the Accounts table."

You can't add the Cutomer record, which needs an *EXITING* accid, until *AFTER* the Accounts record with that accid is added.

In other words, you have the order of your INSERTs backwards. Add to the Accounts table and *THEN* add to the Customer table.

[Old Pedant]

On top of that, you are trying to add data to a field named cusid in the accounts table but there is no such field as your table was created.

Nor should there be. Links between customer and accounts should go one way. Doesn't matter which way, but only one way.

What I'm more than a little confused by: Why would customer and accounts be separate tables, in the first place???? As your tables are currently designed, each account can have many customers associated with it. Is that your intention???

[theophilusnwizu]

Thank you very much for prompt response. I grateful for your observations.

No. One account which can be savings/current (accno) to one customer. I hope i understand what you mean't "to add data to a field named cusid in the accounts table but there is no such field as your table was created."? I created cusid field in the customer's table "`cusid` int(4) NOT NULL AUTO_INCREMENT, " Let me try as u observed.

Thanks for your wonderful time!

[Old Pedant]

This is what I meant.

You have this:

CREATE TABLE IF NOT EXISTS `accounts` ( 
`accid` int(4) NOT NULL AUTO_INCREMENT, 
`accno` varchar(10) NOT NULL, 
`balance` int(11) NOT NULL, 
`type` varchar(100) NOT NULL, 
`active` varchar(5) NOT NULL, 
PRIMARY KEY (`accid`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

So that accounts table has ONLY those fields.

But your PHP code is trying to do

$sql_acc = "INSERT INTO accounts (accno,type,balance,active,cusid) values ('".$accno."','".$type."','".$balance."','1','".$newid."')";

Your accounts table does not *HAVE* a cusid field.

[Old Pedant]

Now, if you WANT to create the customer record first, then yes, it would make more sense to turn things around.

Add the cusid field to the accounts table and then change your constraint to go the other way.

Like this, maybe:

DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` ( 
`accid` int(4) NOT NULL AUTO_INCREMENT, 
`accno` varchar(10) NOT NULL, 
`balance` int(11) NOT NULL, 
`type` varchar(100) NOT NULL, 
`active` varchar(5) NOT NULL, 
`cusid` INT REFERENCES customer(cusid),
PRIMARY KEY (`accid`) 
) ENGINE=InnoDB;

And then you could remove accid from the customer table.

As I said, either way works. Just don't try to go both ways.

[chkakashi24]

Now, if you WANT to create the customer record first, then yes, it would make more sense to turn things around.

Add the cusid field to the accounts table and then change your constraint to go the other way.

Like this, maybe:

PHP Code:

Code:
DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` ( 
`accid` int(4) NOT NULL AUTO_INCREMENT, 
`accno` varchar(10) NOT NULL, 
`balance` int(11) NOT NULL, 
`type` varchar(100) NOT NULL, 
`active` varchar(5) NOT NULL, 
`cusid` INT REFERENCES customer(cusid),
PRIMARY KEY (`accid`) 
) ENGINE=InnoDB; 


And then you could remove accid from the customer table.

As I said, either way works. Just don't try to go both ways.

i have this same problem when inserting >_<
I already checked my foreign keys and there seems to be nothing wrong with them @_@ PLEASE HELP?



And This is how my insert goes...

PHP Code:

$pkey = mysql_insert_id(); 
                
                $sql_query = "insert into med_rec(med_name, med_type) values ('$name', '$type')";
                $sql_query2 = "insert into medicine(med_rec_ID, expiry) values (". $pkey .",'$exp')";
                $sql_query3 = "insert into inventory(staff_ID, med_ID, qty) values (".$_SESSION['id'].",". $pkey .", '$qty')"; 


[Old Pedant]

Ummm...you can *NOT* get the value of the newly created AUTO_INCREMENT value (the primary key) until *AFTER* the INSERT takes place!!!!

In other words *AFTER* you actually *EXECUTE* the insert query!

$sql_query = "insert into med_rec(med_name, med_type) values ('$name', '$type')";
mysql_query( $sql_query ) or die( mysql_error );

$pkey = mysql_insert_id(); 
$sql_query2 = "insert into medicine(med_rec_ID, expiry) values (". $pkey .",'$exp')";
mysql_query( $sql_query2 ) or die( mysql_error );

$pkey = mysql_insert_id(); 
$sql_query3 = "insert into inventory(staff_ID, med_ID, qty) values (".$_SESSION['id'].",". $pkey .", '$qty')";  
mysql_query( $sql_query3 ) or die( mysql_error );