Mysql - Cannot add or update a Child Row: a Foreign key Constraint fails (`areas_knowledge`. `sub_areas`,

Question

Hello, I am doing a job that consists in developing a system to manage projects and etc. In this system I need to have a table with the knowledge areas of cnpq. I’m taking the Grande Areas, Areas, Sub-Areas and Specialties from a JSON file - with all the data I talked about - that I found on Github. I managed to enter the large areas and areas quietly but when it arrives in the sub-areas I have as return an error:

'Cannot add or update a child row: a foreign key constraint fails (`areas_conhecimento`.`sub_areas`, CONSTRAINT `area` FOREIGN KEY (`area`) REFERENCES `areas` (`codigo`))'

I am using Mysql with Nodejs, the Insert I used was:

INSERT INTO areas_conhecimento.sub_areas VALUES ('Álgebra', '10100008', '10101004');

Where 10100008 is the code of a record of the area table. Follow the bank code:

create database areas_conhecimento;
use areas_conhecimento;

CREATE TABLE categorias (
    nome VARCHAR(30) NOT NULL,
    codigo INT NOT NULL PRIMARY KEY
);

CREATE TABLE areas (
    nome VARCHAR(30) NOT NULL,
    categoria INT NOT NULL,
    codigo INT NOT NULL PRIMARY KEY,
    CONSTRAINT categoria FOREIGN KEY (categoria)
        REFERENCES categorias (codigo)
);

CREATE TABLE sub_areas (
    nome VARCHAR(30) NOT NULL,
    codigo INT NOT NULL PRIMARY KEY,
    area INT NOT NULL,
    CONSTRAINT area FOREIGN KEY (area)
        REFERENCES areas (codigo)
);

CREATE TABLE especialidades (
    nome VARCHAR(30) NOT NULL,
    codigo INT NOT NULL PRIMARY KEY,
    sub_area INT NOT NULL,
    CONSTRAINT sub_area FOREIGN KEY (sub_area)
        REFERENCES sub_areas (codigo)
);

Where category = Large areas and the rest is the same thing.

the JSON file is available here

I wonder how I can resolve this error and not lose data from my table.

Answer 1

According to your file:

"areas": [
    {
      "codigo": "10100008",
      "nome": "Matemática",
      "sub-areas": [
        {
          "codigo": "10101004",
          "nome": "Álgebra",

but in your INSERT you specified:

INSERT INTO areas_conhecimento.sub_areas VALUES ('Álgebra', '10100008', '10101004');

This is for sub_areas:

nome: 'Álgebra'
codigo: 10100008
area: 10101004

that doesn’t seem right, maybe it should be:

INSERT INTO areas_conhecimento.sub_areas VALUES ('Álgebra', '10101004', '10100008');