Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?
share|improve this question
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

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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