CODEWITHSUNDEEP
phpmysql
Hasan Alp Zengin
Hasan Alp Zengin

Reputation: 99

(SQL) Update row which is included null foreign key error

I am working on a database project. When i try to update row which is included null foreign key it gives me Cannot add or update a child row: a foreign key constraint fails ("archaelogy"."artifact", CONSTRAINT "location" FOREIGN KEY ("location_id") REFERENCES "location" ("location_id") ON DELETE NO ACTION ON UPDATE SET NULL error. I can add a row which is included null foreign key but can't update it.

This is my php code :

function editArtifact($editData){
$query = "UPDATE artifact SET worker_id=?, image=?, period=?,location_id=?, coordinate_x=?, coordinate_y=?, type=?, comment=?) WHERE artifact_id=?";
$stmt = $this->con->prepare($query);
$stmt->bind_param("ibsiddsss", $editData['worker_id'],$editData['image'],$editData['period'],$editData['location_id'],$editData['coordinate_x'],$editData['coordinate_y'],$editData['type'],$editData['comment'],$editData['artifact_id']);

if ($stmt->execute()) {
  echo json_encode(array("editStatus"=>array('success'=>true)));
}else{
  echo json_encode(array("editStatus"=>array('success'=>false)));
}

$stmt->close();

}

and this is my post:

artifact_id=qw & worker_id=1 & image=null & period=null & location_id=null & coordinate_x=null & coordinate_y=null & type=null & comment=null

Database design:

database design

I don't understand that, I can insert a null column but can't update it.

Upvotes: 0

Views: 1014

Answers (2)

Hasan Alp Zengin
Hasan Alp Zengin

Reputation: 99

Ok guys, i fixed the problem. Problem was on my php code. I was trying to determine null values with isset construct. When i try to determine null with '===', problem has fixed.

Fixed php code:

<?php
require_once "DbOperations.php";
$response=array();
if($_SERVER['REQUEST_METHOD']=='POST'){
   if (isset($_POST['artifact_id'])) {

$editData;
if($_POST['artifact_id']!=='null'){
  $editData['artifact_id']=$_POST['artifact_id'];
  if($_POST['worker_id']!=='null'){
    $editData['worker_id']=$_POST['worker_id'];
  }else{
    $editData['worker_id']=null;
  }
  if($_POST['image']!=='null'){
    $editData['image']=$_POST['image'];
  }else{
    $editData['image']=null;
  }
  if($_POST['period']!=='null'){
    $editData['period']=$_POST['period'];
  }else{
    $editData['period']=null;
  }
  if($_POST['location_id']!=='null'){
    $editData['location_id']=$_POST['location_id'];
  }else{
    $editData['location_id']=null;
  }
  if($_POST['coordinate_x']!=='null'){
    $editData['coordinate_x']=$_POST['coordinate_x'];
  }else{
    $editData['coordinate_x']=null;
  }
  if($_POST['coordinate_y']!=='null'){
    $editData['coordinate_y']=$_POST['coordinate_y'];
  }else{
    $editData['coordinate_y']=null;
  }
  if($_POST['type']!=='null'){
    $editData['type']=$_POST['type'];
  }else{
    $editData['type']=null;
  }
  if($_POST['comment']!=='null'){
    $editData['comment']=$_POST['comment'];
  }else{
    $editData['comment']=null;
  }

    $db = new DbOperations();
    $db->editArtifact($editData);
  }
  }else{
       $response['error']=true;
       json_encode($response);
    }
  }
?>

thanks everybody for trying to fix problem. I am posting data from an android app. I don't know why it is posting null string.

Upvotes: 1

Elumalai Kaliyaperumal
Elumalai Kaliyaperumal

Reputation: 1520

I think you're trying to update artifact a location_id value that doesn't exist in location table. Also check your post parameter location_id=null. There may not be a location_id as null in your location table. Hope this helps!

Your location_id in artifact table should allow NULL values.

Note: To utilize the SET NULL rule for update/delete operations the foreign key column should allow NULL values otherwise the SET NULL specification would fail by generating an error message.

Upvotes: 2

Related Questions