CODEWITHSUNDEEP
mysql
Nick Bailey
Nick Bailey

Reputation: 3162

Confusing cannot add foreign key constraint error

Ok, maybe it's late and I'm being stupid, but I can't seem to figure out why I'm getting a Cannot add Foreign Key Constraint error for the following query

DROP TABLE IF EXISTS People_Lordships;
CREATE TABLE People_Lordships
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    PersonId INT NOT NULL,
    LordshipId INT NOT NULL,
    AssumedDate Date,
    AbdicatedDate Date
);

DROP TABLE IF EXISTS People_Lordships_Current;
CREATE TABLE People_Lordships_Current
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    People_LordshipsId INT NOT NULL,
    LordShipId INT NOT NULL,
    CONSTRAINT Fk_People_Lordships_Current_People_LordshipsId_LordshipId
    FOREIGN KEY (`LordshipId`,`People_LordshipsId`)
    REFERENCES People_Lordships (`LordshipId`,`Id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT UQ_People_Lordships_Current_LordshipId
    UNIQUE KEY (`LordshipId`)
 );

And yes, it is a database about noble titles... it's a long story

Upvotes: 0

Views: 29

Answers (2)

Nick Bailey
Nick Bailey

Reputation: 3162

Figured this one out.

It turns out MySQL cannot add a foreign key constraint against a column that is not the first column in an index.

The following will work

DROP TABLE IF EXISTS People_Lordships;
CREATE TABLE People_Lordships
(
Id INT PRIMARY KEY AUTO_INCREMENT,
PersonId INT NOT NULL,
LordshipId INT NOT NULL,
AssumedDate Date,
AbdicatedDate Date,
INDEX Idx_LordshipId (LordshipId)
);

DROP TABLE IF EXISTS People_Lordships_Current;
CREATE TABLE People_Lordships_Current
(
Id INT PRIMARY KEY AUTO_INCREMENT,
People_LordshipsId INT NOT NULL,
LordShipId INT NOT NULL,
CONSTRAINT Fk_People_Lordships_Current_People_LordshipsId_LordshipId
FOREIGN KEY (`LordshipId`,`People_LordshipsId`)
REFERENCES People_Lordships (`LordshipId`,`Id`)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT UQ_People_Lordships_Current_LordshipId
UNIQUE KEY (`LordshipId`)
);

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

There is no column LordshipId in table People_Lordships.

Your foreign key definition attempts to reference a column that doesn't exist.

 REFERENCES People_Lordships (`LordshipId`,`Id`)
                              ^^^^^^^^^^^^

Upvotes: 2

Related Questions