（SQL）包含null外键错误的更新行

Question

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:

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

Answer 1

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.