How to solve the problem with DBDelta and FOREIGN KEY – BrowserStudios

If you are not familiar with FOREIGN KEY:

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

I went through a lot of threads, codex page and tried messing with a lot of things in order to generate an one by many relation with DBDelta and FOREIGN KEY in my database. As of today, dbDelta does not yet support FOREIGN KEY in fact it doesn’t work for MySQL 5.5 either.

From WordPress-codex about dbDelta:

The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates (see wp-admin/upgrade-schema.php for more examples of how to use dbDelta). Note that the dbDelta function is rather picky, however.

For instance:

• You must put each field on its own line in your SQL statement.
• You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
• You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
• You must not use any apostrophes or back ticks around field names.

But even when you follow those instructions, DBDelta and FOREIGN KEY doesn’t seem to go hand in hand with each other.

My goal is a one by many relation with DBDelta and FOREIGN KEY in order to show that one user can obtain many links. Or in other words many links belong to one user.

Here is what I was trying to achieve:

One by many relation with DBDelta and FOREIGN KEY

My proceeding was rather brute force than reading. I thought if DBDelta and FOREIGN KEY don’t get a long why don’t just call FOREIGN KEY through the back door. So I created the tables first and used the following queries:

global $wpdb;

// Is InnoDB available?
$have_innodb = $wpdb->get_results("SHOW VARIABLES LIKE 'have_innodb'", ARRAY_A);
$use_innodb = ($have_innodb[0]['Value'] == 'YES') ? 'ENGINE=InnoDB' : '';

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

// this if statement makes sure that the table doe not exist already if ($wpdb->get_var("SHOW TABLES LIKE " . self::$database['user']) != self::$database['user']) {
   $sql_user = "CREATE TABLE " . self::$database['user'] . " (
   id BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
   fp BIGINT UNSIGNED NOT NULL DEFAULT 1,
   PRIMARY KEY (id)
  ) COLLATE utf8_general_ci $use_innodb";
 dbDelta($sql_user);
}
if ($wpdb->get_var("SHOW TABLES LIKE " . self::$database['link']) != self::$database['link']) {
  $sql_link = "CREATE TABLE " . self::$database['link'] . " (
  id BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
  parent_id BIGINT(20) unsigned NOT NULL,
  PRIMARY KEY (id),
  KEY par_ind (parent_id)
) COLLATE utf8_general_ci $use_innodb";
 dbDelta($sql_link);
}

This seemed to pave the path and open the door when we use $wpdb->query(). So this is what I did. I only added this pice of code to it and voila it did the trick. I know it’s rather quick and dirty, but it’s a solution to a problem some developers face.

$sql = "ALTER TABLE " . self::$database['link'] . " ADD FOREIGN KEY (parent_id) REFERENCES " . self::$database['user'] . "(id)
    ON DELETE CASCADE;";
 $wpdb->query($sql);

Here is the full code:

global $wpdb;

// Is InnoDB available?
$have_innodb = $wpdb->get_results("SHOW VARIABLES LIKE 'have_innodb'", ARRAY_A);
$use_innodb = ($have_innodb[0]['Value'] == 'YES') ? 'ENGINE=InnoDB' : '';

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

// this if statement makes sure that the table doe not exist already if ($wpdb->get_var("SHOW TABLES LIKE " . self::$database['user']) != self::$database['user']) {
   $sql_user = "CREATE TABLE " . self::$database['user'] . " (
   id BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
   fp BIGINT UNSIGNED NOT NULL DEFAULT 1,
   PRIMARY KEY (id)
 ) COLLATE utf8_general_ci $use_innodb";
 dbDelta($sql_user);
}
 if ($wpdb->get_var("SHOW TABLES LIKE " . self::$database['link']) != self::$database['link']) {
  $sql_link = "CREATE TABLE " . self::$database['link'] . " (
  id BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
  parent_id BIGINT(20) unsigned NOT NULL,
  PRIMARY KEY (id),
  KEY par_ind (parent_id)
 ) COLLATE utf8_general_ci $use_innodb";

 dbDelta($sql_link);

// Alter table link
 $sql = "ALTER TABLE " . self::$database['link'] . " ADD FOREIGN KEY (parent_id) REFERENCES " . self::$database['user'] . "(id)
    ON DELETE CASCADE;";
 $wpdb->query($sql);
}

I would appreciate if someone would post a comment if what I did doesn’t make sense, plus add the reason. Or even if someone leaves a comment with an updated version of the DBDelta and FOREIGN KEY.