Getting cannot find an index in the referenced table error

178 views Asked by At

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.

Above is the full description of error im getting. i checked the indexes of both table and should be fine but it still gives me this error.

Below is the tables structure.

CREATE TABLE `contact_address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address_type` int(5) DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postcode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `area` int(5) DEFAULT NULL,
  `state` int(5) DEFAULT NULL,
  `country` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fax` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_branch_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int(4) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_c_a_area` (`area`),
  KEY `fk_c_a_state` (`state`),
  KEY `fk_c_a_country` (`country`),
  KEY `fk_c_a_contact_branch_id` (`contact_branch_id`),
  KEY `fk_c_a_created_by` (`created_by`),
  CONSTRAINT `fk_c_a_area` FOREIGN KEY (`area`) REFERENCES `ref_area` (`area_id`),
  CONSTRAINT `fk_c_a_contact_branch_id` FOREIGN KEY (`contact_branch_id`) REFERENCES `contact_branch` (`id`),
  CONSTRAINT `fk_c_a_created_by` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`),
  CONSTRAINT `fk_c_a_state` FOREIGN KEY (`state`) REFERENCES `ref_state` (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ref_countries` (
  `country_code` char(2) NOT NULL,
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(80) NOT NULL,
  `alpha_3` char(3) DEFAULT NULL,
  `calling_code` int(5) NOT NULL,
  `continent_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`country_code`),
  KEY `country_id` (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=253 DEFAULT CHARSET=utf8mb4;

Im trying to run this and giving me the error.

ALTER TABLE `contact_address`
  ADD CONSTRAINT `fk_c_a_countries` FOREIGN KEY (`country`) 
  REFERENCES `ref_countries` (`country_code`);
0

There are 0 answers