dbDelta support for FOREIGN KEY

On PHP 5.3.13/MySQL 5.5.21 the following code doesn’t work:

if($check_custom_fields_form!=1){
    $sql = "CREATE TABLE IF NOT EXISTS ". $table_custom_fields_form ." (
                `form_name` longtext NOT NULL,
                `field_id` bigint(20) NOT NULL,
                FOREIGN KEY (`field_id`) REFERENCES $table_custom_fields (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
    ) CHARACTER SET utf8 COLLATE utf8_general_ci";
    dbDelta($sql);
}
if($check_subscribe_cat!=1){
    $sql = "CREATE TABLE IF NOT EXISTS ". $table_subscribe_cat ." (
                `subscribe_id` bigint(20) NOT NULL,
                `cat_id` bigint(20) NOT NULL,
                FOREIGN KEY (`subscribe_id`) REFERENCES ".$wpdb->prefix."tgt_subscription (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
                FOREIGN KEY (`cat_id`) REFERENCES ".$wpdb->prefix."terms (`term_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) CHARACTER SET utf8 COLLATE utf8_general_ci";
    dbDelta($sql);
 }

The code provider suggested a downgrade to MySQL 5.1.37 (no, thanks) or the following update:

Read More
if($check_custom_fields_form!=1){
        $sql = "CREATE TABLE IF NOT EXISTS ". $table_custom_fields_form ." (
                    `form_name` longtext NOT NULL,
                    `field_id` bigint(20) NOT NULL,
                    KEY(field_id)
        ) CHARACTER SET utf8 COLLATE utf8_general_ci";
        dbDelta($sql);
    }
if($check_subscribe_cat!=1){
      $sql = "CREATE TABLE IF NOT EXISTS ". $table_subscribe_cat ." (
                  `subscribe_id` bigint(20) NOT NULL,
                  `cat_id` bigint(20) NOT NULL,
                  KEY(subscribe_id),
                  KEY(cat_id)
      ) CHARACTER SET utf8 COLLATE utf8_general_ci";
      dbDelta($sql);
   }

Which seems like a rather dirty way to work around the problem (no cascading deletes/updates). Therefore:

  1. Do I really have to live with that until dbDelta supports FOREIGN
    KEY
    ?
  2. Is it true that dbDelta only works with foreign key in a 3 year old MySQL version?

Related posts

Leave a Reply

1 comment

  1. Do I really have to live with that until dbDelta supports FOREIGN KEY?

    Quite frankly, yes. But that’s the beauty of open source – anyone is welcome to post a patch!

    However, expanding it to cover other aspects of schema design would almost certainly incur unwanted complexity & heighten the possibility of failure – something the core team will strongly consider beforehand.

    I would take @xav0989’s advice – use dbDelta for it’s intention (basic table implementation, column additions & adjustments), and handle the additional features with $wpdb.