Wordpress table key relationships

Question

I am writing a Wordpress plugin extending Woocommerce that requires to store additional information about a user.

I understand that there is a separate wp_user_meta table specific to this, but I would rather use a custom table as I plan to use Wordpress/Woocommerce as an initial platform then hopefully expand to others. The data will also be queried often.

This custom table requires a single entry per user, defined by the ID in wp_user, therefore essentially being a one-to-one relationship. An example schema for the custom table is:

CREATE TABLE {$wpdb->prefix}custom_table (
  custom_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  custom_data varchar(200) NULL,
  FOREIGN KEY (user_id) REFERENCES wp_users(ID),
  PRIMARY KEY (custom_id)
)

My question is using Wordpress, how do you ensure that the one-to-one relationship is maintained?

I know I will have to run an initial setup to create the entries in the custom table for existing users.

Other than this, are users allowed to change their ID at all in the user lifecycle? Is it a case of using registration/deletion hooks to ensure that the data in the custom table is up to date? (Or would the deletion cascade through to other tables when it happens in the wp_user table)

Answer 1

For reference the code snippet - note that checks should be in place for checking the array size etc. but this gets me in the right direction. Thank you @cabrarahector

require_once ABSPATH . 'wp-admin/includes/upgrade.php';
global $wpdb;

$query = "SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='{$wpdb->dbname}' AND TABLE_NAME='{$wpdb->users}';";
$engine = $wpdb->get_results( $query )[0]->ENGINE;

$sql = "CREATE TABLE {$wpdb->prefix}custom_table (
    custom_id bigint unsigned NOT NULL AUTO_INCREMENT,
    user_id bigint(20) unsigned NOT NULL,
    custom_data varchar(200) NULL,
    FOREIGN KEY (user_id) REFERENCES wp_users(ID) ON DELETE CASCADE,
    PRIMARY KEY (custom_id),
    UNIQUE (user_id)
) ENGINE={$engine};";

dbDelta($sql);

this creates the custom table in the database