MySQL: Can not add foreign key into create table statement

Question

The queries below (assigned to php variables) seem to have an issue on the 2nd query, where I set the foreign key to the "couponId" column on the first table. My IDE lints "couponId" (inside the foreign key declaration) as well to show there's a issue.

Does anyone see what the issue is? Below is the error and the create function/query

// codeception is a test runner so I think it injects "temporary" into create statements

WordPress database error Cannot add foreign key constraint for query CREATE TEMPORARY TABLE IF NOT EXISTS wp_frequentVisitorCoupons_targets (
      targetId mediumint not null auto_increment unique,
      primary key (targetId),
      isSitewide tinyint(1) not null,
      targetUrl varchar(500),
      displayThreshold tinyint(5) not null default 20,
      offerCutoff tinyint(5),
      fk_coupons_targets mediumint not null unique,
      foreign key (fk_coupons_targets) references wp_frequentVisitorCoupons_coupons(couponId) on delete cascade
    ) made by Codeception\Application->run,  <continues on ...>



// the create table function
public static function createTablesIfNotExists() {
    global $wpdb;

  $createCouponTableQuery = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fvc_coupons (
  couponId mediumint not null auto_increment unique,
  primary key (couponId),
  totalHits mediumint not null,
  isText boolean not null,
  fileName varchar(200),
  folderDateString varchar(7)
  )";

  $createTargetTableQuery = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fvc_targets (
    targetId mediumint not null auto_increment unique,
    primary key (targetId),
    isSitewide tinyint(1) not null,
    targetUrl varchar(500),
    displayThreshold tinyint(5) not null default 20,
    offerCutoff tinyint(5),
    fk_coupons_targets mediumint not null unique,
    foreign key (fk_coupons_targets) references {$wpdb->prefix}fvc_coupons(couponId) on delete cascade
  )";

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

}

Answer 1

I am not sure about word press, but if you are using MySQL then you need to change your foreign key structure Original:

foreign key (fk_coupons_targets) references wp_frequentVisitorCoupons_coupons(couponId)

After adjust:

foreign key fk_coupons_targets (col_name) references wp_frequentVisitorCoupons_coupons(couponId)

example:

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)

Note: if you want to add them foreign key to create table then you need to add only the last two lines