Unable to create foreign key in database

Question

I am trying to create a table with a varchar column as foreign key but MySQL gives me an error while creating the table. My query is like this:

CREATE TABLE `survey_hesco_subdivision` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`circle_code` VARCHAR(100) DEFAULT NULL,
`name` VARCHAR(100) DEFAULT NULL,
`circle_name` VARCHAR(100) DEFAULT NULL,
`division_code` VARCHAR(100) DEFAULT NULL,
`sub_div_code` VARCHAR(100) NOT NULL,
`division_name` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`id`,`sub_div_code`),
KEY `id` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1;

The above table is already in used

Create table `accurate_mam`.`meter_ping`(  
`id` int(11) NOT NULL AUTO_INCREMENT,
`meter_id` int(11) NOT NULL,
`meter_msn` varchar(100),
`sub_div_code` varchar(100) NOT NULL,
`sub_div_name` varchar(100),
primary key (`id`),
constraint `FK_PING_METER_ID` foreign key (`meter_id`) references 
`accurate_mam`.`meters`(`id`) on delete Cascade,
constraint `FK_PIN_SUB_DIV` foreign key (`sub_div_code`) references 
`accurate_mam`.`survey_hesco_subdivision`(`sub_div_code`) on delete Cascade
) ENGINE=InnoDB charset=latin1 collate=latin1_swedish_ci 

The error I am getting is

Error Number : 1005 Error Message: Can't create table accurate_mam.meter_ping (errno: 150 "Foreign key constraint is incorrectly formed")

I have already looked into this question

Answer 1

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

So, just create a index like this, before creating child table :

CREATE INDEX `idx_survey_hesco_subdivision_sub_div_code` ON survey_hesco_subdivision(sub_div_code);

Although, It is not best practice to use non-unique column as reference columns in relationship. DELETE CASCADE will not behave properly in that case. I will suggest you create a unique key on sub_div_code of primary table as well.

For more details, refere to this

Source : Cannot add foreign key - StackOverflow

Answer 2

Have you already run CREATE TABLE meters? There error is caused by that table being missing. Let's see that CREATE.