Can you use data type: varchar() in MySQLl Databases?

I have tried adding a foreign key of type VARCHAR that references another table column with type VARCHAR as well. Both Columns are same type and length, but I keep getting an error: Failed to add the foreign key constraint. Missing index for constraint

The first table below has no problem when being created but the second table is the table to which throws the error: Failed to add the foreign key constraint. Missing index for constraint 'FK_Session_User' in the referenced table 'users'

The column I that I am trying to reference is the UserName Column.

CREATE TABLE Users (
    UserID INT NOT NULL AUTO_INCREMENT,
    Role_Code VARCHAR(15) NOT NULL,
    UserName VARCHAR(40) NOT NULL,
    Password TEXT NOT NULL,
    CONSTRAINT PK_User PRIMARY KEY (UserID, UserName),
    CONSTRAINT FK_User_Roles FOREIGN KEY (Role_Code)
    REFERENCES Roles(Role_Code));


CREATE TABLE Auth_Users (
    SessionID VARCHAR(96) NOT NULL,
    UserName VARCHAR(40) NOT NULL,
    Last_Visit TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT PK_AuthUser PRIMARY KEY (SessionID),
    CONSTRAINT FK_Session_User FOREIGN KEY (UserName)
    REFERENCES Users(UserName) 
    );

Solution

the problem is that UserName is not indexed indexed on Users. You should add an index to it. You can do it like this:

CREATE TABLE Users (
    UserID INT NOT NULL AUTO_INCREMENT,
    Role_Code VARCHAR(15) NOT NULL,
    UserName VARCHAR(40) NOT NULL,
    Password TEXT NOT NULL,
    INDEX(UserName),
    CONSTRAINT PK_User PRIMARY KEY (UserID, UserName),
    CONSTRAINT FK_User_Roles FOREIGN KEY (Role_Code)
    REFERENCES Roles(Role_Code)
);

UPDATE: With that said, please see this: https://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index#:~:text=From%20MySQL%20documentation%3A,unique%20columns%20of%20referenced%20table. On why you shouldn't do that.

I would recommend normalizing you data in a way that you have only unique not nulls as your references.

In your case the simplest way to solve it would be to reference UserID in your foreign key. Also, that is what you most probably really want to do.