MySQL错误1005：无法创建operation表，外键约束格式错误求助

Hey there, let's break down why that foreign key constraint is failing and fix it up.

The error message tells us the constraint is incorrectly formed, and the root cause is straightforward:
When you create a foreign key linking one table to another, the column you're referencing in the parent table must be either a PRIMARY KEY or have a UNIQUE constraint. In your Logininfo table, the primary key is idLogin, but you're trying to tie Operation.userName to Logininfo.userName—right now, userName in Logininfo has no uniqueness guarantee. MySQL can't ensure there's only one matching row for the foreign key, so it rejects the table creation.

Two Fix Options: #

Option 1: Add a UNIQUE constraint to Logininfo.userName

Since usernames should logically be unique for a login system anyway, this is a natural fix:

DROP DATABASE IF EXISTS AgenceAssurance; 
CREATE DATABASE IF NOT EXISTS AgenceAssurance; 
USE AgenceAssurance; 

CREATE TABLE Logininfo ( 
    idLogin INT(3) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    userName VARCHAR(50) NOT NULL UNIQUE, -- Added UNIQUE constraint here
    pass VARCHAR(50), 
    usertype VARCHAR(50) DEFAULT NULL, 
    GrantedKey VARCHAR(8) DEFAULT NULL 
)ENGINE = InnoDB; 

CREATE TABLE Operation ( 
    idOpt INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    libelOpt TEXT, 
    dateOpt DATETIME DEFAULT now(), 
    userName VARCHAR(50), 
    CONSTRAINT fk_login FOREIGN KEY (userName) REFERENCES Logininfo (userName) 
)ENGINE = InnoDB;

Option 2: Link via the primary key idLogin (common best practice)

If you prefer to follow standard foreign key patterns (using primary keys for relationships), adjust the Operation table to reference idLogin instead:

DROP DATABASE IF EXISTS AgenceAssurance; 
CREATE DATABASE IF NOT EXISTS AgenceAssurance; 
USE AgenceAssurance; 

CREATE TABLE Logininfo ( 
    idLogin INT(3) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    userName VARCHAR(50) NOT NULL, 
    pass VARCHAR(50), 
    usertype VARCHAR(50) DEFAULT NULL, 
    GrantedKey VARCHAR(8) DEFAULT NULL 
)ENGINE = InnoDB; 

CREATE TABLE Operation ( 
    idOpt INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    libelOpt TEXT, 
    dateOpt DATETIME DEFAULT now(), 
    loginId INT(3), -- Column matches the type of Logininfo.idLogin
    CONSTRAINT fk_login FOREIGN KEY (loginId) REFERENCES Logininfo (idLogin) 
)ENGINE = InnoDB;

Either approach will resolve the 1005/150 error. Pick the one that aligns better with your application's data model.

内容的提问来源于stack exchange，提问作者AbdeAMNR