#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    3

    Foreign key fails to insert


    Hi,

    I am attempting to insert a foreign key into a table along with other information, but I am getting an error message:

    Code:
    Could not insert data into DataBase: Cannot add or update a child row: a foreign key constraint fails (`collectionsdb2`.`collections`, CONSTRAINT `collections_ibfk_1` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`))
    My insert statement is as follows:

    PHP Code:
    $sql "INSERT INTO collections (cat_id, ctitle, csubject, creference, cmaterial, ctechnic, cwidth, cheight, cperiod, cmarkings, cdescription, csource, cartist, cfilename) VALUES ($cat_id, '$ctitle', '$csubject', '$creference', '$cmaterial', '$ctechnic', '$cwidth', '$cheight', '$cperiod', '$cmarkings', '$cdescription', '$csource', '$cartist', '" $image['name'] . "')";
             
    $result mysql_query($sql) or die ("Could not insert data into DataBase: " mysql_error());

             exit; 
    And my two tables are:

    PHP Code:
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    DROP TABLE IF EXISTS collectionsdb2.collections;
    CREATE TABLE `collections` (
      `
    c_idint(11NOT NULL AUTO_INCREMENT,
      `
    cat_idint(4) DEFAULT NULL,
      `
    ctitlevarchar(65NOT NULL,
      `
    csubjectvarchar(65) DEFAULT NULL,
      `
    creferencevarchar(65) DEFAULT NULL,
      `
    cyeartimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `
    cmaterialvarchar(65) DEFAULT NULL,
      `
    ctechnicvarchar(255) DEFAULT NULL,
      `
    cwidthvarchar(65) DEFAULT NULL,
      `
    cheightvarchar(65) DEFAULT NULL,
      `
    clengthvarchar(65) DEFAULT NULL,
      `
    cdiametervarchar(65) DEFAULT NULL,
      `
    cperiodvarchar(65) DEFAULT NULL,
      `
    cmarkingsvarchar(255) DEFAULT NULL,
      `
    cdescriptiontext,
      `
    csourcevarchar(65) DEFAULT NULL,
      `
    cartistvarchar(65) DEFAULT NULL,
      `
    cfilenamevarchar(65) DEFAULT NULL,
      
    PRIMARY KEY (`c_id`),
      
    KEY `cat_id` (`cat_id`),
      
    CONSTRAINT `collections_ibfk_1FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`)
    ENGINE=InnoDB DEFAULT CHARSET=latin1;


    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
    PHP Code:
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    DROP TABLE IF EXISTS collectionsdb2.categories;
    CREATE TABLE `categories` (
      `
    cat_idint(4NOT NULL AUTO_INCREMENT,
      `
    categoryvarchar(25NOT NULL,
      
    PRIMARY KEY (`cat_id`)
    ENGINE=InnoDB AUTO_INCREMENT=DEFAULT CHARSET=latin1;


    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
    In the insert php I have a select option list box for selecting categories to insert into the datable.

    Please, how may I resolve this issue?

    joseph
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,620
    Rep Power
    595
    From a PHP perspective you should not be using the deprecated MySQL extensions. Switch to PDO. As to your question, this is really a MySQL issue so I requested it be moved to that forum for you.

    Comments on this post

    • Jacques1 disagrees : The only MySQL issue is that a foreign key constraint is violated. And we already know that.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    well, I'd say this is an PHP issue. The database people can only tell you that you're trying to insert an invalid cat_id. How that ID got into the query is a problem of your PHP code.

    So start with the standard debugging procedure:
    • Echo the query.
    • What's the value for cat_id in the query?
    • If the value is nonsense, how is $cat_id defined? If it's valid, why do you not have that category?
    • ...


    Apart from that, I hope you've escaped all your values?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    3
    Ok, I am going to do some debuging another machine.

    I have escaped all input variables except the foreign key, which is not a string but an int.

    Also, I already had some data in the parent table (categories table).
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,620
    Rep Power
    595
    My apologies. Since no PHP code was provided, other than the query itself, I made the assumption that the values being set were already checked and correct. I gave the OP the benefit of the doubt that basic debugging, even for a beginner, of the PHP side had already been done.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    Echo the query to make sure it's correct.

    If it is correct, then you're violating the foreign key constraint on the table (the key you're inserting must already exist on the other table).

    I'm leaving this in PHP even though I suspect it's simply a problem with the data in the table, which is really a problem neither with PHP nor MySQL.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    3
    Originally Posted by ManiacDan
    Echo the query to make sure it's correct.

    If it is correct, then you're violating the foreign key constraint on the table (the key you're inserting must already exist on the other table).

    I'm leaving this in PHP even though I suspect it's simply a problem with the data in the table, which is really a problem neither with PHP nor MySQL.
    I got it working !

    Thanx alot. I love you all.

    Stay well.
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    What was it?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    3
    Originally Posted by ManiacDan
    What was it?
    After going through the whole code I discovered it was nothing to do with any of the above issues, but the miss named select control (name=cat_id).

    Thanx oncemore.

IMN logo majestic logo threadwatch logo seochat tools logo