0

This question already has an answer here:

I am using FOREIGN KEY with dbDelta when activating the plugin (register_activation_hook()). It works great when creating the table for the first time, but outputs an error when updating the table (the table is already present but an alteration has to be done on the table). Any suggestions?

    if(GLOBAL_PLUGIN_VERION != get_option("GLOBAL_PLUGIN_VERSION")){
      $sql = "CREATE TABLE `".TEST_TABLE."` (
     `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
     `title` VARCHAR( 100 ) NOT NULL,
     `description` TEXT DEFAULT NULL,
     `location_id` INT( 11 ) NOT NULL,
     PRIMARY KEY  (`id`),
     FOREIGN KEY  (`location_id`) REFERENCES `".TEST2_TABLE."` (`id`)
    );";
   }

Error:

PHP Notice: Undefined offset: 1 in wp-admin/upgrade.php on line 1568

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY (location_id) REFERENCES test2 (id)' at line 1 for query ALTER TABLE test ADD COLUMN FOREIGN KEY (location_id) REFERENCES test2 (id) made by ...

WordPress database error Multiple primary key defined for query ALTER TABLE TEST ADD PRIMARY KEY (id) made by...

marked as duplicate by s_ha_dum, fuxia Jul 15 '13 at 16:49

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

From the Codex:

Note that the dbDelta function is rather picky, however. For instance: [...]

  • You must not use any apostrophes or backticks around field names.
$sql = "CREATE TABLE ".TEST_TABLE." (
    id INT( 11 ) NOT NULL AUTO_INCREMENT,
    title VARCHAR( 100 ) NOT NULL,
    description TEXT DEFAULT NULL,
    location_id INT( 11 ) NOT NULL,
    PRIMARY KEY  (id),
    FOREIGN KEY  (location_id) REFERENCES ".TEST2_TABLE." (id)
);";
  • Thank you for pointing out the apostrophes but it did not solve the issue. – CodePorter Jul 15 '13 at 15:02
  • Can you update your question with more context? When you call dbDelta, what are the conditions (if( xyz ) { dbDelta( $sql ); }) ? – Pat J Jul 15 '13 at 15:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.