MySQL: ERROR 3780 (HY000) at line 1406622: Referencing column 'os_branch_id' and referenced column 'id' in foreign key constraint '***' are incompatible.

ERROR 3780 (HY000) at line 1406622: Referencing column 'os_branch_id' and referenced column 'id' in foreign key constraint '***' are incompatible.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE `gas_bombehouse` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
   ....
  `os_branch_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`), 
  UNIQUE KEY `code` (`code`),
  KEY `gas_bombehouse_os_branch_id_d35d17d3_fk_outsource` (`os_branch_id`),
  CONSTRAINT `gas_bombehouse_os_branch_id_d35d17d3_fk` FOREIGN KEY (`os_branch_id`) REFERENCES `outsources_outsourcebranch` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

1
2
3
4
5
6
7

CREATE TABLE `outsources_outsourcebranch` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=9751 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

原因

• リモートの mysqldump を ローカルにそのまま取り込もうとしていた
• 古いスキーマが残っていて、そのidがint であったため。
• DROP TABLE IF EXISTS table CREATE TABLE table が実行されるまで、古いスキーマが残っている、ということ。

1
2
3
4
5

DROP TABLE IF EXISTS `outsources_outsourcebranch`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `outsources_outsourcebranch`
....

解決

• DROP DATABASE & CREATE DATABASE して、更地にmysqldumpをロードする

資料

• How to fix MySQL error 1215 Cannot add foreign key constraint

  1. Make sure that you are using the correct syntax
  2. Make sure your tables are using InnoDB engine
  3. Make sure the two columns are using the same data type
  4. Adding ON DELETE / UPDATE SET NULL clause on a NOT NULL column
  5. For VARCHAR columns, make sure you have the same collation for both tables

• 【MySQL】外部キー制約がつけられなくてハマった話

Edge

The error message “ERROR 3780 (HY000)” indicates that there is an issue with a foreign key constraint¹. A foreign key constraint is a rule that ensures the referential integrity of data in a database¹. It means that the data in one table must match the data in another table¹.

The error message “Referencing column ’emp_no’ and referenced column ’emp_no’ in foreign key constraint ‘dept_emp_ibfk_1’ are incompatible” indicates that there is an issue with the data type of the columns¹. The two columns must have the same data type and length¹.

You mentioned that both of the referring table column and referred table column are same. In this case, there might be other issues than column types such as collation². You can check if both tables have the same collation².

Can you provide more context about where you encountered this error message? It would help me provide a more accurate response.

ソース: Bing との会話 2023/4/6(1) MySQLのデータ型確認はshow columnsで。データ型変更時に …. https://style.potepan.com/articles/19094.html アクセス日時 2023/4/6. (2) Error when foreign referencing in mySQL (Error 3780). https://stackoverflow.com/questions/58550408/error-when-foreign-referencing-in-mysql-error-3780 アクセス日時 2023/4/6. (3) laravel - Laravel7 マイグレーションのエラー General error: 3780 …. https://ja.stackoverflow.com/questions/67594/laravel7-%E3%83%9E%E3%82%A4%E3%82%B0%E3%83%AC%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B3%E3%81%AE%E3%82%A8%E3%83%A9%E3%83%BC-general-error-3780 アクセス日時 2023/4/6.