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

4 Views

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

Read More

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.

Post Views: 4

Related posts

2 comments

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

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

Comments are closed.