Error Referencing wp_users as Foreign Key I'm fairly new to SQL. I'm trying to create a FK for one of my tables (time_sheet) to one of the Wordpress tables (wp_users). But I'm hitting an error and I'm not entirely sure why that is.

This is my SQL statement to create the table time_sheet:

Create table Time_Sheets
(
 id bigint AUTO_INCREMENT NOT NULL,
    project_id varchar(255) not null,
    username varchar(250) not null,
    task varchar(255) not null,
    description varchar(255) not null,
    hours int(10) not null,
    time_date varchar(255) not null,
    PRIMARY KEY (id),
    FOREIGN KEY (project_id) REFERENCES Time_Sheet_Projects(project_id),
    FOREIGN KEY (task) REFERENCES Task_List(name),
    FOREIGN KEY (username) REFERENCES wp_users(display_name),
    CHECK (hours &gt; 0)
)


Here's an image of the Wordpress database schema:



When I take out this line FOREIGN KEY (username) REFERENCES wp_users(display_name), the query run perfectly fine.

I've check the name/column name and they are correct. I'm not sure why this isn't working.

show create table wp_users

CREATE TABLE `wp_users` (
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `user_status` int(11) NOT NULL DEFAULT '0',
 `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 PRIMARY KEY (`ID`),
 KEY `user_login_key` (`user_login`),
 KEY `user_nicename` (`user_nicename`),
 KEY `user_email` (`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


Edit: So it turned out the database engine being different was the problem. After switching wp_users to InnoDB, everything is working nicely.
The MyISAM engine doesn't support foreign keys. Use the InnoDB engine instead.

Other observations:

The referenced table has no index on display_name. As per documentation, it is required to have an index on the referenced field.

It is better however to use the ID field from wp_users table to be referenced, and use this ID to lookup display_name, like:

FOREIGN KEY (user_id) REFERENCES wp_users(id)


In addition, change the field in the time_sheet table from username varchar(250) not null, to user_id BIGINT not null...