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

Question

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:

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

Following are my tables.

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;

customerDetails(CustomerName) must either be a pk or unique. — jarlh, Aug 28 '17 at 09:04
Keeping customer name in transactions looks like a bad idea (and redundant) - it's unlikely that name is unigue and what happens if there is a name change. — P.Salmon, Aug 28 '17 at 09:08
@P.Salmon in many cases it is a legal requirement to retain customer details as they were at the time of a transaction. One way of achieving this is to save them into the transaction table as well. It's not nice, but often useful. — Shadow, Aug 28 '17 at 09:17
@shadow Not nice , Appalling more like but true enough there are horrible designs out there. — P.Salmon, Aug 28 '17 at 09:19
@jarlh Thanks.It solved the issue.Added CustomerName as a unique field. @ P.Salmon and @ Shadow Yes I understand it is a bad idea to keep customer name in transactions,but that is how the requirements specified. — Supun Amarasinghe, Aug 28 '17 at 09:34

score 4 · Accepted Answer · answered Aug 28 '17 at 09:07

4

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.

answered Aug 28 '17 at 09:07

Mayank Pandeyz

25,704
4
40
59

Thanks Mayank. Your solution solved the issue. – Supun Amarasinghe Aug 28 '17 at 09:30

score 3 · Answer 2 · answered Aug 28 '17 at 09:08

You should use CustomerID since you have created customerDetails table with primary column of CustomerID columns. CustomerID should be referred as foreign key not customerName.

Use below structure to create transactions table.

Also you dont need to map AccountNumber here since you are going to map with CustomerID.

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

Thanks Vignesh for your answer.But this is the way I should be mapping according to the requirements of the project.Anyway its a helpful answer. — Supun Amarasinghe, Aug 28 '17 at 09:32

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

2 Answers2