11

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:

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?
3
  • 1
    While cascading deletes/updates are nice, are they totally necessary? Can you include that part in your code, or refactor your tables?
    – T0xicCode
    Jul 27, 2012 at 20:44
  • I am not sure whether they are necessary, I haven't studied this 3rd party code enough to know.
    – Gaia
    Jul 29, 2012 at 20:25
  • 1
    TheDeadMedic is correct. However, I just found this Link dbDelta and FOREIGN KEY. It explains a way around the problem. It worked for me. Good luck!
    – user37058
    Aug 23, 2013 at 22:04

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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