I am getting Error 3780 when attempting to create the following table Tool in MySQL Workbench:
CREATE TABLE IF NOT EXISTS Recipe (
Recipe_ID int primary key,
Author varchar(50),
Recipe_name varchar(50),
Recipe_time int, # in minutes
Prep_time int,
Cook_time int,
Recipe_cal int,
Recipe_fat int,
Recipe_carbs int,
Recipe_protein int
);
CREATE TABLE IF NOT EXISTS Tool (
Tool_ID int primary key,
Recipe_ID int,
foreign key (Recipe_ID) references Recipe(Recipe_ID)
);
Error Code: 3780. Referencing column ‘Recipe_ID’ and referenced column ‘Recipe_ID’ in foreign key constraint ‘tool_ibfk_1’ are incompatible. I am using MySQL version 8.0.33 as shown by SELECT version();
I ran DESCRIBE on Recipe, and here was the result:
Recipe_ID varchar(4) NO PRI
Author varchar(50) YES
Recipe_name varchar(50) YES
Recipe_time int YES
Prep_time int YES
Cook_time int YES
Recipe_cal int YES
Recipe_fat int YES
Recipe_carbs int YES
Recipe_protein int YES
I also ran SHOW CREATE TABLE for Recipe.
'Recipe', 'CREATE TABLE `recipe` (n `Recipe_ID` varchar(4) NOT NULL,n `Author` varchar(50) DEFAULT NULL,n `Recipe_name` varchar(50) DEFAULT NULL,n `Recipe_time` int DEFAULT NULL,n `Prep_time` int DEFAULT NULL,n `Cook_time` int DEFAULT NULL,n `Recipe_cal` int DEFAULT NULL,n `Recipe_fat` int DEFAULT NULL,n `Recipe_carbs` int DEFAULT NULL,n `Recipe_protein` int DEFAULT NULL,n PRIMARY KEY (`Recipe_ID`)n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
I also ran DESCRIBE Tool, but Tool doesn’t exist yet due to the error.
I have studied a few of the Overflow threads regarding this issue, and attempted to apply this solution regarding collation and character sets. This didn’t resolve the issue at hand:
CREATE SCHEMA IF NOT EXISTS KITCHEN;
USE KITCHEN;
ALTER DATABASE Kitchen
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_520_ci;
To my knowledge, both Recipe_IDs are both ints, as many have had issues when the types didn’t match. I would appreciate any suggestions to fix this problem.
2
Answers
I previously had the Recipe ID in Recipe set to be a varchar(4). I attempted to correct the type mismatch between the foreign key Recipe ID in Tool by adding int, but because the table already existed, no updates were made. I solved the issue by dropping Recipe with the varchar ID and recreating the newer one.
In the "Recipe" table, the data type of the "Recipe_ID" column is specified as varchar(4), but in the "Tool" table, the data type of the "Recipe_ID" column is specified as int. This inconsistency could be causing the error you’re seeing.