MySQL KeyColumn doesn't exist on Foreign Key

December 1, 2021

I currently have the following MySQL database structure:

WebsiteUsers:

WS_UsersID (INT(10) UNSIGNED PRIMARY KEY)
WS_Username (VARCHAR(255) NOT NULL)
WS_Password (VARCHAR(255) NOT NULL)
WS_Email (VARCHAR(255) NOT NULL)
WS_UserActive (TINYINT(1) UNSIGNED DEFAULT '1')
WS_UserCreated (DATETIME)
WS_UserModified (DATETIME)

WebsiteTaskTypes:

WS_TaskTypeID (INT(10) UNSIGNED PRIMARY KEY)
WS_TaskTypeName (VARCHAR(255)) NOT NULL

Only indexes set up in the InnoDB tables are the 2 primary keys respectively, both being named of course "PRIMARY", pointing to the columns listed above.

In PHPMyAdmin I would like to run the following MySQL command:

CREATE TABLE WebsiteTasks (
    WS_TaskID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    FOREIGN KEY WS_UserID (WS_UserID) REFERENCES WebsiteUsers (WS_UsersID),
    FOREIGN KEY WS_TaskTypeID (WS_TaskTypeID) REFERENCES WebsiteTaskTypes(WS_TaskTypeID),
    WS_TaskActive BOOLEAN NOT NULL DEFAULT '1',
    WS_TaskCompletedBy INT(10) UNSIGNED COMMENT 'User who completed task',
    WS_TaskCreated DATETIME,
    WS_TaskModified DATETIME
) CHARSET=utf8mb4;

However, this gives the following error message: #1072 - 'WS_UserID' Key Column doesn't exist in table.

What am I doing wrong here? Could someone please explain it to me, and give me a fix? 😀

>Solution :

I think your issue was you had not actually created the column in this (theWebsiteTasks table) when you were trying to use it in a foreign key reference. So create the columns and then add the constraints like this.

CREATE TABLE WebsiteTasks (
    WS_TaskID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    WS_UserID INT(10) 
    WS_TaskTypeID INT(10)
    WS_TaskActive BOOLEAN NOT NULL DEFAULT '1',
    WS_TaskCompletedBy INT(10) UNSIGNED COMMENT 'User who completed task',
    WS_TaskCreated DATETIME,
    WS_TaskModified DATETIME,
    CONSTRAINT fk_userid FOREIGN KEY (WS_UserID) REFERENCES WebsiteUsers (WS_UsersID),
    CONSTRAINT fk_ttid FOREIGN KEY (WS_UserID) REFERENCES WebsiteTaskTypes(WS_TaskTypeID)

) CHARSET=utf8mb4;