CODEWITHSUNDEEP
pythonmysql
Ligen12
Ligen12

Reputation: 83

Missing Index Constraint Adding Foreign Key

I am trying to define the foreign key in the leaderinfo table as UserID, which is the primary key in the usercredentials table.

USERtable="CREATE TABLE IF NOT EXISTS usercredentials (userID VARCHAR(255),username VARCHAR(255),password VARCHAR(255),stakeholder VARCHAR(255))"
mycursor.execute(USERtable)
LEADERtable="""CREATE TABLE IF NOT EXISTS leaderinfo (leaderID VARCHAR(255),firstname VARCHAR(255),secondname VARCHAR(255),age VARCHAR(255), \
        gender VARCHAR(255),ethnicity VARCHAR(255),address VARCHAR(255),postcode VARCHAR(255),telephone VARCHAR(255), \
        email VARCHAR(255),userID VARCHAR(255),PRIMARY KEY(leaderID),FOREIGN KEY(userID) REFERENCES usercredentials(userID) on update cascade on delete cascade)"""

Error Code:

mysql.connector.errors.DatabaseError: 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'leaderinfo_ibfk_1' in the referenced table 'usercredentials'

Upvotes: 0

Views: 1461

Answers (1)

Jonah Bishop
Jonah Bishop

Reputation: 12581

The column you are referencing in the leader table isn't indexed. You can fix this by making the userID column a primary key (I'm going to wrap the SQL here for readability):

USERtable="CREATE TABLE IF NOT EXISTS usercredentials (
           userID VARCHAR(255),
           username VARCHAR(255),
           password VARCHAR(255),
           stakeholder VARCHAR(255),
           PRIMARY KEY(userID))"

Upvotes: 1

Related Questions