0

I am trying to create a table on the Wordpress database as the plugin gets activated. My code is the following:

in the main plugin file:

register_activation_hook( __FILE__, 'sillyplugin_install');

in the activation.php file:

function sillyplugin_install()
{
    global $wpdb;

    $table_name = $wpdb->prefix . 'sillyplugindata';
    $charset_collate = $wpdb->get_charset_collate();

    if ( $wpdb->get_var( "show tables like '$table_name'" ) != $table_name )
    {
            $sql = "create table $table_name (" . 
            "post_id bigint(20) unsigned not null," .
            "comment_id bigint(20) unsigned not null," .
            "silly_number int unsigned not null," .
            "primary key(post_id, silly_number)," .
            "foreign key(post_id) references wp_posts(ID) on delete cascade," .
            "foreign key(comment_id) references wp_comments(comment_ID) on delete cascade" . 
        ") $charset_collate;";

        add_option( 'sillyplugindata_version', '1.0');

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql );
    }
}

I can't figure out what goes wrong because everything seems to go fine except the table doesn't get created. All I managed to know is:

  • the register_activation_hook() works properly and the callback function gets called;
  • if the table does not exist the function executes what's in the if statement as it should;
  • if I print the content of the $sql variable and try to execute the query manually on the MySQL client it works fine and it actually creates the table;
  • if I try to print the return value of dbDelta() I get returned an empty array
  • no error messages in the admin panel or debug/error log.

I am at loss here, why doesn't the table gets created?

  • dbDelta is extremely particular, see this answer for a few hints. One issue that I see immediately is capitalization. – Milo Dec 18 '17 at 15:55
  • @Milo thank you for your suggestion, capitalizing the SQL code does not solve the issue unfortunately. – haunted85 Dec 18 '17 at 16:05
  • 1
    oh, I don't think it supports foreign key, you'll have to alter the table after creation. – Milo Dec 18 '17 at 16:14
  • @Milo that was it!! But if foreign keys are not supported how come the sql works perfectly fine on the database when executed on the mysql client and dbDelta is so fussy? – haunted85 Dec 18 '17 at 16:20
  • Create your foreign key after using dbDelta with $wpdb->query This article can help you, I think browserstudios.com/2013/08/23/… – Anton Lukin Dec 18 '17 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.