0

I would like to import SQL dump file, some how it's not working. I got this error when I try to import data.

Referencing column 'originator_id' and referenced column 'name' in foreign key constraint 'fk_rails_79d8c4ccda' are incompatible.

DROP TABLE IF EXISTS `loan_fee_shares`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `loan_fee_shares` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `originator_id` varchar(255) DEFAULT NULL,
  `percent` decimal(6,2) DEFAULT NULL COMMENT 'belongs to [0,1] set',
  `active` tinyint(1) DEFAULT '0',
  `deactivated_at` timestamp NULL DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_rails_79d8c4ccda` (`originator_id`),
  CONSTRAINT `fk_rails_79d8c4ccda` FOREIGN KEY (`originator_id`) REFERENCES `originators` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


DROP TABLE IF EXISTS `originators`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `originators` (
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `index_originators_on_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
Jin Lim
  • 1,759
  • 20
  • 24
  • 2
    You need to move the `originators` table before `loan_fee_shares`. – Barmar Feb 09 '22 at 19:02
  • 1
    You probably have an existing `originators` table with different definition of the `name` column, so you need to recreate the table first to make them compatible. – Barmar Feb 09 '22 at 19:03

1 Answers1

0

I am posting this for someone who might have the same issue in the future.

Barmar has a good point. and I moved originators table to before loan_fee_shares. But I still had an issue.

and It had an issue with DEFAULT CHARSET=latin1, I had to change it to DEFAULT CHARSET=utf8. and It works.

I suggest that change all your tables to the same character set.

How to change all the tables in my database to UTF8 character set?

Jin Lim
  • 1,759
  • 20
  • 24