Database Table creation Issue on Wordpress Plugin Activation

Question

Good day, please I am trying to create table on plugin activation. Some tables are created successfully why some don't. The one with issues are payment service names and currency section. It created the table columns but no row with default value. I have tried all I could but the rows still didn't show up. I even deleted the tables and activate the plugins again to recreate but still no table rows inside the tables carrying the default values specified. Please what is the way out??

function hdm_plugin_activate() {
    global $wpdb;

    // Create a custom table to store keywords and their status
    $table_name_keywords = $wpdb->prefix . 'hdm_article_keywords';
    $charset_collate = $wpdb->get_charset_collate();
    $sql_keywords = "CREATE TABLE $table_name_keywords (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        keyword varchar(255) NOT NULL,
        status varchar(20) NOT NULL,
        selected_by varchar(255) DEFAULT NULL,
        assigned_by mediumint(9) DEFAULT NULL,
        assigned_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        hours_to_complete int(11) DEFAULT 0 NOT NULL,
        PRIMARY KEY (id)
    ) $charset_collate;";
    
    
        // Create the table for cost per word
    $table_name_cost_per_word = $wpdb->prefix . 'hdm_cost_per_word';
    $sql_cost_per_word = "CREATE TABLE $table_name_cost_per_word (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        cost_per_word decimal(10, 3) NOT NULL,
        PRIMARY KEY (id)
    ) $charset_collate;";
    

    // Create the table for currency
    $table_name_currency = $wpdb->prefix . 'hdm_article_keyword_currency';
    $sql_currency = "CREATE TABLE $table_name_currency (
        id mediumint(9) NOT NULL DEFAULT '1',
        currency_code varchar(3) NOT NULL DEFAULT 'USD',
        PRIMARY KEY (id)
    ) $charset_collate;";

    // Create the table for payment service names
    $table_name_payments = $wpdb->prefix . 'hdm_keyword_payments';
    $sql_payments = "CREATE TABLE $table_name_payments (
        id mediumint(9) NOT NULL DEFAULT '1',
        payment1 VARCHAR(255) NOT NULL DEFAULT 'Paypal ID',
        payment2 VARCHAR(255) NOT NULL DEFAULT 'Payoneer Email',
        PRIMARY KEY (id)
    ) $charset_collate;";

    // Add 'Content Writer' role
    add_role('hdm_content_writer_role', __('HDM Content Writer'), array(
        'read' => true,
        'edit_posts' => true,
        'delete_posts' => true,
        'upload_files' => true, // Allow adding images to posts
        // Add other capabilities as needed
    ));
    
    // Add 'HDM Editor' role
    add_role('hdm_proofreader', __('HDM Editor'), array(
        'read' => true,
        'edit_posts' => true,
        'delete_posts' => true,
        'upload_files' => true,
        'edit_published_posts' => true,
        'publish_posts' => true,
        'moderate_comments' => true,
        // Add other capabilities as needed
    ));

    // Use dbDelta to create/modify tables
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql_keywords);
    dbDelta($sql_cost_per_word);
    dbDelta($sql_currency);
    $wpdb->query($sql_payments);
    

}

I tried using the code below to force the creation. It works but it returns fatal error when activating again because of ID value 1. When I remove ID value it works but continue to create new row after each activate and deactivate.

// Insert default currency options for rows not created
$default_currency_code = 'USD';
$wpdb->insert($table_name_currency, array('currency_code' => $default_currency_code));
$wpdb->insert($table_name_payments, array('payment1' => 'Paypal ID', 'payment2' => 'Payoneer Email'));

Answer 1

Casting aside the fact that this is a Wordpress installation for a moment, what you're trying to do doesn't really make much sense. What you're trying to do at the core in both of the "problematic" tables you reference is create a field that's a PRIMARY KEY (that is, a field that is guaranteed unique when compared to all other rows in that table) that has a DEFAULT value.

How do you expect it to be logically possible to have a unique PRIMARY KEY that simply defaults to a static value, especially when INSERTing data you don't explicitly assign an id value? While hypothetically this could work (and the reason why this isn't an error up front) if you had some algorithm for generating valid id values and were explicitly providing them in your INSERT statements, it doesn't seem like this is what you're intending.

The solution is to remove the DEFAULT for the id columns, and instead allow MySQL's AUTO_INCREMENT feature handle assignments for you:

$sql_payments = "CREATE TABLE $table_name_payments (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    payment1 VARCHAR(255) NOT NULL DEFAULT 'Paypal ID',
    payment2 VARCHAR(255) NOT NULL DEFAULT 'Payoneer Email',
    PRIMARY KEY (id)
) $charset_collate;";