dbDelta using Foreign key not working on update

1 Views

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:

Read More

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…

Post Views: 1

Related posts

1 comment

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

Comments are closed.