Kalhara Amarasinghe Kalhara Amarasinghe - 1 month ago 12
MySQL Question

MySQL Error[1215][HY000] - Cannot add foreign key

I have two tables which are customer and transaction tables. Transaction table have two foreign keys which refers two fields in the customer table.

I added the customer table but when I try to add the transaction table, it gives me a

SQL Error[1215][HY000] Cannot add foreign key constraints
.

Note: I searched for a solution in many sources and tried them but nothing seems to fix this error.

Following are my tables. Any help regarding this much appreciated.

CREATE TABLE customerDetails(
CustomerID varchar(10)NOT NULL,
AccountNumber varchar(15) NOT NULL,
CustomerName varchar(60)NOT NULL,
Address varchar(60),
phone varchar(15),
email varchar(50),
joinedDate date,
primary key(CustomerID)
)

CREATE TABLE transactions(
TraceNumber varchar(30) NOT NULL,
AccountNumber varchar(15)NOT NULL,
CustomerName varchar(60)NOT NULL,
TransactionType varchar(15) NOT NULL,
TransactionDateTime datetime NOT NULL,
TransactionAmount double DEFAULT NULL,
PRIMARY KEY (TraceNumber),
FOREIGN KEY(CustomerName) REFERENCES customerDetails(CustomerName),
FOREIGN KEY(AccountNumber) REFERENCES customerDetails(AccountNumber)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


Thanks :)

Answer Source

The issue is here:

FOREIGN KEY(AccountNumber) REFERENCES customerDetails(AccountNumber)

for making foreign key relationship, parent table column must be a primary key or unique, but in your case, AccountNumber is neither primary nor unique. So change it accordingly.