Can't insert : A foreign key constraint fails

Question

I have the following tables:

CREATE TABLE IF NOT EXISTS `location`(
    `ID` int(11) NOT NULL,
    `name` varchar(25) NOT NULL,
    `water` varchar(25) NOT NULL,
    `fodder` varchar(25) NOT NULL,
    `access` varchar(25) NOT NULL,
    PRIMARY KEY  (`ID`)
    KEY `water` (`water`)
    KEY `fodder` (`fodder`)
    KEY `access` (`access`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `watercondition`(
    `ID` int(11) NOT NULL,
    `watervalue` varchar(25) NOT NULL,
    PRIMARY KEY  (`watervalue`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `foddercondition`(
    `ID` int(11) NOT NULL,
    `foddervalue` varchar(25) NOT NULL,
    PRIMARY KEY  (`foddervalue`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `accesscondition`(
    `ID` int(11) NOT NULL,
    `accessvalue` varchar(25) NOT NULL,
    PRIMARY KEY  (`accessvalue`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

And for constraints table :

ALTER TABLE `location`
    ADD CONSTRAINT `location_ibfk2` FOREIGN KEY (`water`) REFERENCES `watercondition` (`watervalue`),
    ADD CONSTRAINT `location_ibfk3` FOREIGN KEY (`fodder`) REFERENCES `foddercondition` (`foddervalue`),
    ADD CONSTRAINT `location_ibfk4` FOREIGN KEY (`access`) REFERENCES `accesscondition` (`accessvalue`);

In my php file, i want to insert a value to all of the table like this :

$sqlwater = "INSERT INTO  `watercondition` (`ID`, `watervalue`) VALUES ('".$_SESSION['loc_id']."', '$watervalue')";
$resultwater = mysqli_query($con, $sqlwater) or die (mysqli_error($con));

$sqlfodder = "INSERT INTO  `foddercondition` (`ID`, `foddervalue`) VALUES ('".$_SESSION['loc_id']."', '$foddervalue')";
$resultfodder = mysqli_query($con, $sqlfodder) or die (mysqli_error($con));

$sqlaccess = "INSERT INTO  `accesscondition` (`ID`, `accessvalue`) VALUES ('".$_SESSION['loc_id']."', '$accessvalue')";
$resultaccess = mysqli_query($con, $access) or die (mysqli_error($con));

$sqlloc = "INSERT INTO  `location` (`ID`, `name`) VALUES ('".$_SESSION['loc_id']."', '$name')";
$resultaccess = mysqli_query($con, $access) or die (mysqli_error($con));

But when I execute the php file, I get this error :

Cannot add or update a child row: a foreign key constraint fails (mydb.location, CONSTRAINT location_ibfk2 FOREIGN KEY (water) REFERENCES watercondition (watervalue))

When I check on my db, the value from water, fodder, and access have already been inserted db, but not in my location table.

Answer 1

The insert into the location table must also include values for the water, fodder and location columns. They are columns in the location table and cannot just be ignored.

Also you were using the wrong query variable in the final query.

I guess what is happening here is that the constraints are being validated by MYSQL before it checks that you have values for all the NOT NULL fields, so you get the constraint error before the more obvious one about missing column values.

$sqlloc = "INSERT INTO  `location` 
           (`ID`, `name`, `water`, `fodder`, `location`) 
      VALUES ('{$_SESSION['loc_id']}', '$name', 
              '$watervalue', '$foddervalue', '$accessvalue' )";

$resultaccess = mysqli_query($con, $sqlloc) or die (mysqli_error($con));