Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Apr 2010
    Posts
    27
    Thanks
    3
    Thanked 0 Times in 0 Posts

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

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,620
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,620
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    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???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    theophilusnwizu (05-08-2010)

  • #4
    New Coder
    Join Date
    Apr 2010
    Posts
    27
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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!

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,620
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    This is what I meant.

    You have this:
    Code:
    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
    Code:
    $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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,620
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    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:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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` ( 
    `
    accidint(4NOT NULL AUTO_INCREMENT
    `
    accnovarchar(10NOT NULL
    `
    balanceint(11NOT NULL
    `
    typevarchar(100NOT NULL
    `
    activevarchar(5NOT NULL
    `
    cusidINT 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')"
    Last edited by chkakashi24; 12-19-2012 at 09:52 PM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,620
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    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!

    Code:
    $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 );
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •