Resources

https://deliciousbrains.com/creating-custom-table-php-wordpress/

Create / Update A Table

While not a requirement to use the wordpress dbDelta function, it is recommended when making changes to the database as it examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary.

  //ENSURE USER IS ADMIN
  if (!current_user_can('administrator'))
    die;
  
  $output = "";
  
  global $wpdb;
  $charset_collate = $wpdb->get_charset_collate();
  require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

  //----- CREATE / UPDATE my_tablename -----
  $sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
    user_id bigint(20) UNSIGNED NOT NULL,
    my_field1  varchar(255),
    PRIMARY KEY  (user_id)
  ) $charset_collate;";
  //IMPORTANT!!!!
  //  You must have two spaces between the words PRIMARY KEY and the definition of your primary key.

  
  dbDelta($sql);
  $output = "Updating my_tablename: " . print_r($wpdb->last_result) . ", " . $wpdb->last_error;

dbDelta IS VERY PICKY!!!! Important rules for dbDelta()

  //dbDelta() FUNCTION HAS SOME IMPORTANT RULES:
  //- You must put each field on its own line in your SQL statement.
  //- You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
  //- You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
  //- KEY (not "PRIMARY KEY") must be followed by a SINGLE SPACE then the key name then a space then open parenthesis with the field name then a closed parenthesis.
  //- You must not use any apostrophes or backticks around field names.
  //- Field types must be all lowercase.
  //- SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
  //- You must specify the length of all fields that accept a length parameter. int(11), for example.

//Defaults:
// tinyint(4)
// smallint(6)
// mediumint(8)
// int(11)
// bigint(20)

dbDelta useful resources

https://www.hungred.com/how-to/wordpress-dbdelta-function/

Table Definitions

Various different column types and default values

  $sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
    user_id bigint(20) UNSIGNED NOT NULL,
    my_tinyint  tinyint(4) UNSIGNED DEFAULT 0,
    my_smalling  smallint(6) UNSIGNED DEFAULT 0,
    my_int  int(11) NOT NULL DEFAULT 10000,
    CreatedDateTime  datetime DEFAULT NULL,
    my_string  varchar(255) DEFAULT '',
    my_text  text DEFAULT '',
    PRIMARY KEY  (user_id)
  ) $charset_collate;";
  //IMPORTANT!!!!
  //  You must have two spaces between the words PRIMARY KEY and the definition of your primary key.

Defining character set

LiveSearchMeta  varchar(5000) CHARACTER SET utf8 DEFAULT '',

Defining an index

  //Index 1 column
  $sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
    user_id bigint(20) UNSIGNED NOT NULL,
    my_field1  tinyint(4),
    my_field2  tinyint(4),
    my_field3  tinyint(4),
    PRIMARY KEY  (user_id),
    INDEX MyIndexName (my_field1)
  ) $charset_collate;";       //MyIndexName can be the same as the field name if desired
  //IMPORTANT!!!!
  //  You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
  
  
  //Index 2 columns separatly, 1 of them ensure uniqueness
  $sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
    user_id bigint(20) UNSIGNED NOT NULL,
    my_field1  tinyint(4),
    my_field2  tinyint(4),
    my_field3  tinyint(4),
    PRIMARY KEY  (user_id),
    INDEX MyIndexName (my_field1),
    UNIQUE INDEX MyIndexName (my_field2)
  ) $charset_collate;";       //MyIndexName can be the same as the field name if desired
  //IMPORTANT!!!!
  //  You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
  
  //Index 3 columns as 1 index and ensure uniqueness
  $sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
    user_id bigint(20) UNSIGNED NOT NULL,
    my_field1  tinyint(4),
    my_field2  tinyint(4),
    my_field3  tinyint(4),
    PRIMARY KEY  (user_id),
    UNIQUE INDEX MyIndexName (my_field1, my_field2, my_field3)      
  ) $charset_collate;";
  //IMPORTANT!!!!
  //  You must have two spaces between the words PRIMARY KEY and the definition of your primary key.