How to solve error "Foreign key constraint is incorrectly formed"

Question

I want to create a customers table with foreign key relations but when I execute the query I'm getting an error like this:

(errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE `customers` (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) DEFAULT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) DEFAULT NULL,
  `postalCode` varchar(15) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` int(11),
  `creditLimit` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`customerNumber`),
  KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

'How to solve' - work through the conditions required for your version of mysql https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html — P.Salmon, Nov 20 '18 at 07:26

Gaurav Neema · Answer 1 · 2018-11-20T07:38:53.070

0

Check if:

'employeeNumber' column should have an index.
'salesRepEmployeeNumber' and 'employeeNumber' have same type and
length.

edited Nov 20 '18 at 07:38

answered Nov 20 '18 at 06:04

Gaurav Neema

146
1
1
12

Your first point is incorrect. It is only necessary that the `employeeNumber` column has an index on it, not that it is the primary key. See the [manual](https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html) – Nick Nov 20 '18 at 06:24
Ohh yeah!! I missed it. Thanks :) – Gaurav Neema Nov 20 '18 at 06:41
So you should correct your answer... used the `edit` link... – Nick Nov 20 '18 at 07:36
Edited the answer – Gaurav Neema Nov 20 '18 at 07:39

score 0 · Answer 2 · answered Nov 20 '18 at 06:08

0

Check if value exist with "salesRepEmployeeNumber" which is not available in salesRepEmployeeNumber table. Try to remove all "salesRepEmployeeNumber". Check all reference ids before making Foreign Key relation.

answered Nov 20 '18 at 06:08

Arslan Ali

25
8

i tried this also but didn't worked – Anonymous Nov 20 '18 at 06:43

How to solve error "Foreign key constraint is incorrectly formed"

2 Answers2