为什么我可以用NULL值初始化此列，但我无法更新它？

Situation:

I have 2 tables, created with

$wpdb->query("
CREATE TABLE projs 
    (
    id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    compname VARCHAR (200), 
    projname VARCHAR (200), 
    imageurl VARCHAR(300), 
    sumsmall VARCHAR (250), 
    sumfull VARCHAR (5000), 
    results VARCHAR (5000), 
    postdate DATETIME DEFAULT CURRENT_TIMESTAMP, 
    caseid MEDIUM INT, 
    FOREIGN KEY (caseid) 
        REFERENCES cases(id) ON DELETE SET NULL
    ) $charset_collate
");

and

$wpdb->query("
CREATE TABLE cases 
    (
    id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    compname VARCHAR (200), 
    casename VARCHAR (200), 
    imageurl VARCHAR(300), 
    summary VARCHAR (5000), 
    results VARCHAR (5000), 
    postdate DATETIME DEFAULT CURRENT_TIMESTAMP
    ) $charset_collate
");

The relationship between them is that each project (a row in the projs table) can have an associated case case study (a row in the cases table); hence, the

caseid MEDIUM INT, FOREIGN KEY (caseid) REFERENCES cases(id) ON DELETE SET NULL

in the initialization of the cases table.

I have a PHP function to handle AJAX calls made from admin panel I created. It is for adding, updating or deleting a project.

add_action( 'wp_ajax_proj_update', 'proj_update' );

function proj_update ( )
{
    $id = $_POST['id'];
    $compname = $_POST['compname'];
    $projname = $_POST['projname'];
    $imageurl = $_POST['imageurl'];
    $sumsmall = $_POST['sumsmall'];
    $sumfull = $_POST['sumfull'];
    $results = $_POST['results'];
    $caseid = (!isset($_POST['caseid']) || strcmp($_POST['caseid'],'none')) ? $_POST['caseid'] : "null";

    $thisAction = $_POST['thisAction'];

    $message = "";

    switch ($thisAction)
    {
        case 'add':
        {
            global $wpdb;
            $message .= $wpdb->query("INSERT INTO projs (compname,projname,imageurl,sumsmall,sumfull,results,caseid) VALUES (" . implode(",", array("'$compname'", "'$projname'", "'$imageurl'", "'$sumsmall'", "'$sumfull'", "'$results'", "$caseid")) . ")") 
                        ? 'Successfully added project ' . $projname . ' to the database.'
                        : 'Error occurred when trying to add project to database: ' . $wpdb->last_error;
            break;
        }
        case 'delete':
        {
            global $wpdb; 
            $message .= $wpdb->query("DELETE FROM projs WHERE id=" . $id)
                      ? 'Successfully deleted project from the database.'
                      : 'Error occurred when trying to delete project from database: ' . $wpdb->last_error;
            break;
        }
        case 'update':
        {
            global $wpdb;
            $message .= $wpdb->query("UPDATE projs SET compname='$compname',projname='$projname',imageurl='$imageurl',sumsmall='$sumsmall',sumfull='$sumfull',results='$results',caseid='$caseid' WHERE id=$id")
                        ? 'Successfully updated project.'
                        : 'Error occurred when trying to update project in database: ' . $wpdb->last_error;
            break;
        }
        default:
        {
            $message .= 'Didn\'t recognize action.'; 
            break;
        }
    }
    die($message);    
}

I've tested the add part of the function and haven't experienced any failures, but I have been getting an error whenever I try to update a caseid to be null. The error statement generated by my script above when I try to do this is

Error occured when trying to trying to update project in database: Cannot add or update a child row: a foreign key constraint fails ('my_database_name'.'projs',CONSTRAINT 'projs_ibfk_1' FOREIGN KEY ('caseid') REFERENCES 'cases' ('id') ON DELETE SET NULL

Question:

Any idea what I'm doing wrong here?

You're doing:

INSERT ... VALUES (..., "$caseid")

which means your query is actually running

INSERT ... VALUES (..., "null")

That's not an SQL null. It's a STRING with the characters n, `u, etc...

If you want to insert an actual SQL null, you'll have to do the value quoting elsewhere:

$caseid = (isset stuff) ? "'" . $_POST['caseid'] . "'" : "null";
                          ^^^^^------------------^^^^^-- add quotes here

$message .= $wpdb->query([..snip..], $caseid)) . ")") 
                                    ^-------^---no quotes here

The problem in your query is that you are assigning the string "null" to your project's caseid.

You should do something like:

$caseid = (!isset($_POST['caseid']) || strcmp($_POST['caseid'],'none')) ? "'" . $_POST['caseid'] . "'" : "null";

And then, in your query:

case 'update':
        {
            global $wpdb;
            $message .= $wpdb->query("UPDATE projs SET compname='$compname',projname='$projname',imageurl='$imageurl',sumsmall='$sumsmall',sumfull='$sumfull',results='$results',caseid=$caseid WHERE id=$id")
                        ? 'Successfully updated project.'
                        : 'Error occurred when trying to update project in database: ' . $wpdb->last_error;
            break;
        }

Notice that caseid is not wrapped in single quotes any more because i am adding those single quotes when defining it.