8

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?
  • 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 '12 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 '12 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 '13 at 22:04
3

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, privacy policy and cookie policy

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