skip to Main Content

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


  1. Chosen as BEST ANSWER

    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.


  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search