0

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) 
    );
toshioue
  • 3
  • 2
  • Doesn't the error message tell you exactly what the problem is? It doesn't say anything about the datatype, it says the problem is no index. – Barmar Sep 30 '20 at 19:10
  • 2
    But why are you using the username as the foreign key? It's better to use the primary key of the other table as the FK. What if you decide to allow users to change their username? – Barmar Sep 30 '20 at 19:11
  • Look here example tables with foreign keys references to primary keys: https://sqlize.online/?phpses=null&sqlses=d21c8e9e0eb691191570ce2c3dcda9e1&php_version=null&sql_version=mysql80 – Slava Rozhnev Sep 30 '20 at 20:06

1 Answers1

0

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.

g_bor
  • 1,077
  • 6
  • 14