too long

I'm trying to add a new table which has a relation with {wp_posts} table, This code is working properly in localhost and it passes all tests but it failed on server database and generates [Cannot add foreign key constraint].

The [post_id] field is exactly like {wp_posts->ID} field .. I don't know what I missed.

global $wpdb;
$charset_collate = $wpdb->collate;

$tbl_my_users = $wpdb->prefix . '_TABLE_NAME';

dbDelta( "CREATE TABLE IF NOT EXISTS {$tbl_my_users} (
            user_id BIGINT(20) UNSIGNED NOT NULL,
            post_id BIGINT(20) UNSIGNED NOT NULL,
            name VARCHAR(150) NOT NULL,
            email TINYTEXT NULL,
            INDEX (name),
            PRIMARY KEY  (user_id),
            CONSTRAINT Constr_Unique_UserID UNIQUE( user_id ),
            CONSTRAINT Constr_Unique_PostID UNIQUE( post_id ),
            CONSTRAINT Constr_Unique_User UNIQUE( name ),
            CONSTRAINT Constr_My_Users 
                FOREIGN KEY FK_My_Users (post_id) REFERENCES {$wpdb->posts} (ID) ON DELETE CASCADE ON UPDATE CASCADE
        ) COLLATE {$charset_collate}" );

try this bewlo query

  global $wpdb;
$charset_collate = $wpdb->collate;

$tbl_my_users = $wpdb->prefix . '_TABLE_NAME';

dbDelta("CREATE TABLE IF NOT EXISTS  ".$tbl_my_users."  (
            user_id BIGINT(20) UNSIGNED NOT NULL,
            post_id BIGINT(20) UNSIGNED NOT NULL,
            name VARCHAR(150) NOT NULL,
            email TINYTEXT NULL,
            INDEX (name),
            PRIMARY KEY  (user_id),
            CONSTRAINT Constr_Unique_UserID UNIQUE( user_id ),
            CONSTRAINT Constr_Unique_PostID UNIQUE( post_id ),
            CONSTRAINT Constr_Unique_User UNIQUE( name ),
            CONSTRAINT Constr_My_Users 
                FOREIGN KEY (`post_id`) REFERENCES ".$wpdb->prefix."posts (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
        ) COLLATE SET utf8 COLLATE utf8_general_ci" );

old question but i face this problem now.

the issue is when we want to set relation we need to match the column type, collation and the engine.

wp_posts ID : bigint(20) UNSIGNED

your table post_id: bigint(20) UNSIGNED

this is match, but you have problem with ENGINE.

example : the old table is written as innodb but your MYSQL default engine is set as MYISAM. so you without define ENGINE type, you will create MYISAM table instead InnoDB which not support relationship/foreign key. Note: old mysql version using MYISAM as default engine, not InnoDB

In my problem:

wp_posts ID: bigint(20) UNSIGNED InnoDB

my table post_id: bigint(20) InnoDB

the engine and type is match, but i got the eror. Because i forgot to set attribute UNSIGNED. when i set the same attribute, all works perfectly.

Note: don't forget about collation when using string type like varchar, char, etc

i hope this will help others.