unable to update a child table when a foreign key constraint is added: error :Cannot add or update a child row: a foreign key constraint fails

Please, I have been trying to fix this error, have tried all searches and all solutions given but none is working for me, here are my tables.

   CREATE TABLE `member` (
 `member_id` int(11) NOT NULL AUTO_INCREMENT,
 `last_name` varchar(20) NOT NULL,
 `first_name` varchar(20) NOT NULL,
 `affiliation` varchar(20) NOT NULL,
 `street_address` varchar(50) NOT NULL,
 `city` varchar(20) NOT NULL,
 `state` varchar(20) NOT NULL,
 `zipcode` int(11) NOT NULL,
 `phone_number` varchar(15) NOT NULL,
 `cell_number` varchar(15) NOT NULL,
 `text` text NOT NULL,
 `email` varchar(20) NOT NULL,
 `permission_level` int(11) NOT NULL,
 `role` varchar(12) NOT NULL,
 PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


CREATE TABLE `structure` (
 `structure_id` int(11) NOT NULL AUTO_INCREMENT,
 `structure_name` varchar(70) NOT NULL,
 `year_built_from` date NOT NULL,
 `year_built_to` date NOT NULL,
 `builder` varchar(50) NOT NULL,
 `original_use` varchar(70) NOT NULL,
 `present_use` varchar(70) NOT NULL,
 `floor_area` int(50) NOT NULL,
 `levels` int(50) NOT NULL,
 `doors` int(50) NOT NULL,
 `windows` int(50) NOT NULL,
 `rooms` int(50) NOT NULL,
 `foundation_type` varchar(50) NOT NULL,
 `construction_style` varchar(50) NOT NULL,
 `joinery_style` varchar(50) NOT NULL,
 `primary_wood_species` varchar(50) NOT NULL,
 `condition` varchar(50) NOT NULL,
 `coordinate_latitude` int(11) NOT NULL,
 `coordinate_longitude` int(11) NOT NULL,
 PRIMARY KEY (`structure_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


CREATE TABLE `location` (
 `location_id` int(11) NOT NULL AUTO_INCREMENT,
 `structure_id` int(11) NOT NULL,
 `location_name` varchar(100) NOT NULL,
 `legal_description` varchar(100) NOT NULL,
 `township` varchar(100) NOT NULL,
 `county` varchar(50) NOT NULL,
 `state` varchar(50) NOT NULL,
 `private` char(1) NOT NULL,
 PRIMARY KEY (`location_id`),
 KEY `structure_id` (`structure_id`),
 CONSTRAINT `location_ibfk_1` FOREIGN KEY (`structure_id`) REFERENCES `structure` (`structure_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

CREATE TABLE `owner` (
 `owner_id` int(11) NOT NULL AUTO_INCREMENT,
 `structure_id` int(11) NOT NULL,
 `member_id` int(11) NOT NULL,
 PRIMARY KEY (`owner_id`),
 KEY `structure_id` (`structure_id`),
 KEY `member_id` (`member_id`),
 CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `owner_ibfk_2` FOREIGN KEY (`structure_id`) REFERENCES `structure` (`structure_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

what I want to achieve is to have the owner table show the id of the member and structure table, so that if you click on an id of a member it will take you to the member, same goes to location table. but once I enter my data in the form and submit it, only member table and structure table will get their contents, nothing will be on location and owner table, but when I remove the foreign key on location, and resubmit the same form, the location will get it own content now leaving only owner which does not have a field with direct contact with the form.

here is the php code

<?php

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// prepare and bind

$stmt_member = $conn->prepare("INSERT INTO `member`(` last_name`, `first_name`, `affiliation`, `street_address`, `city`, `state`, `zipcode`, `phone_number`, `cell_number`, `text`, `email`, `permission_level`, `role`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt_member->bind_param("ssssssissssis",$member_id, $last_name, $first_name, $affilition, $street_address, $city, $state,$zipcode, $phone_number, $cell_number, $text, $email, $permission_level, $role);

// set parameters and execute
    $last_name = $_POST['lname'];
    $first_name = $_POST['fname'];
    $affilition = $_POST['affiliation'];
    $street_address = $_POST['street_address'];
    $city = $_POST['city'];
    $state = $_POST['state'];
    $zipcode = $_POST['zipcode'];
    $phone_number = $_POST['phone_number'];
    $cell_number = $_POST['cell_number'];
    $text = $_POST['text'];
    $email = $_POST['email'];
    $permission_level = $_POST['permission_level'];
    $role = $_POST['role'];
    $stmt_member->execute();

    $stmt_member->close();
// prepare and bind

$stmt_structure = $conn->prepare("INSERT INTO `structure`( `structure_name`, `year_built_from`, `year_built_to`, `builder`, `original_use`, `present_use`, `floor_area`, `levels`, `doors`, `windows`, `rooms`, `foundation_type`, `construction_style`, `joinery_style`, `primary_wood_species`, `condition`, `coordinate_latitude`, `coordinate_longitude`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt_structure->bind_param("ssssssiiiiisssssii",$structure_id, $structure_name, $year_built_from, $year_built_to, $builder, $original_use, $present_use, $floor_area, $levels, $doors, $windows, $rooms, $foundation_type, $construction_style, $joinery_style, $primary_wood_species, $condition, $coordinate_latitude, $coordinate_longitude);

// set parameters and execute
    $structure_name = $_POST['structure_name'];
    $year_built_from = $_POST['year_built_from'];
    $year_built_to = $_POST['year_built_to'];
    $builder = $_POST['builder'];
    $original_use = $_POST['original_use'];
    $present_use = $_POST['present_use'];
    $floor_area = $_POST['floor_area'];
    $levels = $_POST['levels'];
    $doors = $_POST['doors'];
    $windows = $_POST['windows'];
    $rooms = $_POST['rooms'];
    $foundation_type = $_POST['foundation_type'];
    $construction_style = $_POST['construction_style']; 
    $joinery_style = $_POST['joinery_style'];
    $primary_wood_species = $_POST['primary_wood_species'];
    $condition = $_POST['condition'];
    $coordinate_latitude = $_POST['coordinate_latitude'];
    $coordinate_longitude = $_POST['coordinate_longitude'];

    $stmt_structure->execute();

    $stmt_structure->close();
// prepare and bind

$stmt_location = $conn->prepare("INSERT INTO `location`( `location_name`, `legal_description`, `township`, `county`, `state`, `private`) VALUES (?, ?, ?, ?, ?, ?)");
$stmt_location->bind_param("ssssss", $location_name, $legal_description, $township, $county, $state, $private);

    // set parameters and execute
    $location_name = $_POST['location_name'];
    $legal_description = $_POST['legal_description'];
    $township = $_POST['township'];
    $county = $_POST['county'];
    $state = $_POST['state'];
    $private = $_POST['private'];
    $stmt_location->execute();

    $stmt_location->close();


echo "New records created successfully";
$conn->close();
?>

Apologies for the long post. Thanks guys in advance

Answer

It happens because you cannot insert a location without a structure.

To insert a new location you must make sure that you passed the value of structure_id in the insert statement, in your case that doesn’t happen.

Let’s see your insert statement:

INSERT INTO `location`( `location_name`, `legal_description`, `township`, `county`, `state`, `private`)  Values(?,?,?,?,?,?);

If you notice, structure_id is missing in your query so it is always fails.

To fix it just add it.

INSERT INTO `location`( `structure_id`,`location_name`, `legal_description`, `township`, `county`, `state`, `private`)  Values(?,?,?,?,?,?,?);

We still have to get the last inserted structure_id, because it is not available in our code, so after this line in your php code $stmt_structure->execute(); type the following:

$structure_id =$conn->insert_id;

Advertisement

Answer