Why can't I -- or how do I -- insert NULL values into my MySQL database using the WordPress API

Question

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.

score 0 · Answer 1 · edited Aug 26 '15 at 06:46

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
);

edited Aug 26 '15 at 06:46

Jazi

6,569
13
60
92

answered Aug 25 '15 at 21:31

Jon Lauters

56
7

So is your comment referring to what you believe is the source of my problem, or is it a separate problem? – Aug 25 '15 at 22:11

score 0 · Answer 2 · answered Aug 25 '15 at 22:16

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.)

answered Aug 25 '15 at 22:16

Don't Panic

41,125
10
61
80

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. – Aug 25 '15 at 22:27
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`) – Don't Panic Aug 25 '15 at 22:49
Here's a good Q&A about that: http://stackoverflow.com/questions/2366854/can-table-columns-with-a-foreign-key-be-null – Don't Panic Aug 25 '15 at 22:56

Why can't I -- or how do I -- insert NULL values into my MySQL database using the WordPress API

2 Answers2