I’m creating a custom plugin that creates four custom tables when the plugin is activated. I have two tables that is using 2 foreign keys in one table and four foreign keys in the other. If I don’t include the FOREIGN KEY (user_id) REFERENCES wp_users(id) in the code then the tables are created perfectly, everything works as expected but I need the foreign key for the user ID for both $questions_table and $forms_table.

I’ve tried FOREIGN KEY (user_id) REFERENCES $users_table(id) and FOREIGN KEY (user_id) REFERENCES $wpdb->users(id). I also tried changing “id” to uppercase which didn’t work either.

    global $wpdb;
    global $foo_plugin;

    $form_status_table = $wpdb->prefix . 'foo_form_status';
    $form_type_table = $wpdb->prefix . 'foo_form_type';
    $questions_table = $wpdb->prefix . 'foo_questions';
    $forms_table = $wpdb->prefix . 'foo_forms';

    $users_table = $wpdb->users;
    
    $charset_collate = $wpdb->get_charset_collate();

    $form_status_sql = "CREATE TABLE $form_status_table (
        id mediumint(10) NOT NULL AUTO_INCREMENT,
        name tinytext NOT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    $form_type_sql = "CREATE TABLE $form_type_table (
        id mediumint(10) NOT NULL AUTO_INCREMENT,
        type tinytext NOT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    $questions_sql = "CREATE TABLE $questions_table (
        id mediumint(10) NOT NULL AUTO_INCREMENT,
        date_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        user_id bigint NOT NULL,
        questions mediumtext NOT NULL,
        draft tinyint NOT NULL,
        foo_form_type_id mediumint(10) NOT NULL,
        PRIMARY KEY  (id),
        FOREIGN KEY (user_id) REFERENCES wp_users(id),
        FOREIGN KEY (foo_form_type_id) REFERENCES $form_type_table(id)
    ) $charset_collate;";

    $forms_sql = "CREATE TABLE $forms_table (
        id mediumint(10) NOT NULL AUTO_INCREMENT,
        user_id bigint NOT NULL,
        draft tinyint NOT NULL,
        app_id mediumint(10) NOT NULL,
        foo_form_type_id mediumint(10) NOT NULL,
        foo_form_status_id mediumint(10) NOT NULL,
        foo_questions_id mediumint(10) NOT NULL,
        form_title tinyint NOT NULL,
        date_submitted datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        applicant_info mediumtext NOT NULL,
        appilication_info mediumtext NOT NULL,
        PRIMARY KEY  (id),
        FOREIGN KEY (user_id) REFERENCES wp_users(id),
        FOREIGN KEY (foo_form_type_id) REFERENCES $form_type_table(id),
        FOREIGN KEY (foo_form_status_id) REFERENCES $form_status_table(id),
        FOREIGN KEY (foo_questions_id) REFERENCES $questions_table(id)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $form_status_sql );
    dbDelta( $form_type_sql );
    dbDelta( $questions_sql );
    dbDelta( $forms_sql );

    add_option( 'foo_plugin', $foo_plugin );
}