2

I've been working on this for hours and can't figure out how to insert/update NULL values for columns. For example, I have a block of code

    case 'add':
    {
        $message = $wpdb->insert('projs', array('compname' => $compname, 'projname' => $projname, 'imageurl' => $imageurl, 'sumsmall' => $sumsmall, 'sumfull' => $sumfull, 'results' => $results, 'caseid' => $caseid, 'hide' => $hide)) 
                    ? 'Successfully added project to the database.'
                    : 'Error occurred when trying to add project to database: ' . $wpdb->last_error;
        break;
    }

and whenever it is executed with $caseid being equal to null (in PHP land) the query fails with wpdb->last_error being

Cannot add or update a child row: a foreign key constraint fails ('my_table_name', 'projs', CONSTRAINT 'projs_ibfk_1' FOREIGN KEY ('caseid') REFERENCES 'cases' ('id') ON DELETE SET NULL

For reference, the tables projs and cases are created during installation with

// Add table to hold the project portfolio
if ($wpdb->get_var("SHOW TABLES LIKE 'projs'") != 'projs')
{
    $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");
}
// Add table to hold the case studies
if ($wpdb->get_var("SHOW TABLES LIKE 'cases'") != 'cases')
{
    $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");
}

and I don't see how inserting a NULL value for caseid in projs violates anything.

Improve this question

2 Answers 2

Reset to default
0

It's not that it isn't letting you insert a NULL value in general. It's that it is specifically not letting you insert a NULL value into caseid.

Because of the foreign key constraint linking caseid to the id column in cases, it is expecting a value equal to one of those ids.

You should have no trouble inserting NULL values into any of the other columns (except id, of course.)

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

So foreign keys can't be nullable? What if I want a column in a table to be possibly, but not necessarily, associated with a column in another table? For my application, some rows of projs are associated with rows of cases, but don't have to necessarily be.
I believe that you could allow nulls in your foreign key by explicitly defining that column as nullable in your create. (i.e. caseid MEDIUM INT NULL)
Here's a good Q&A about that: stackoverflow.com/questions/2366854/…
0

MySQL's CURRENT_TIMESTAMP function expects to be called on a TIMESTAMP datatype, not DATETIME.

Note the ON DELETE SET NULL will cause issues if the table is InnoDB

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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
caseid MEDIUMINT, 
FOREIGN KEY (caseid) REFERENCES cases(id) ON DELETE SET NULL
);
Improve this answer

1 Comment

So is your comment referring to what you believe is the source of my problem, or is it a separate problem?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.