Why am I not being able to insert values into my SQL database? [closed]-CodePudding

I'm working on a school project in which we create three tables and insert a few values onto them using MYSQL Workbench.

Pretty simple stuff right? Here are the tables that are being created :

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Regiao` (
  `codRegiao` BIGINT NOT NULL,
  `nomeRegiao` VARCHAR(100) NOT NULL,
  `descricaoRegiao` TEXT NULL,
  PRIMARY KEY (`codRegiao`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Vinicola` (
  `codVinicola` BIGINT NOT NULL,
  `nomeVinicola` VARCHAR(100) NOT NULL,
  `descricaoVinicola` TEXT NULL,
  `foneVinicola` VARCHAR(15) NULL,
  `emailVinicola` VARCHAR(15) NULL,
  `codRegiao` BIGINT NOT NULL,
  PRIMARY KEY (`codVinicola`),
  INDEX `fk_Vinicola_Regiao1_idx` (`codRegiao` ASC) VISIBLE,
  CONSTRAINT `fk_Vinicola_Regiao1`
    FOREIGN KEY (`codRegiao`)
    REFERENCES `mydb`.`Regiao` (`codRegiao`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Vinho` (
  `codVinho` BIGINT NOT NULL,
  `nomeVinho` VARCHAR(50) NOT NULL,
  `tipoVinho` VARCHAR(30) NOT NULL,
  `anoVinho` INT NOT NULL,
  `descricaoVinho` TEXT NULL,
  `codVinicola` BIGINT NOT NULL,
  PRIMARY KEY (`codVinho`),
  INDEX `fk_Vinho_Vinicola_idx` (`codVinicola` ASC) VISIBLE,
  CONSTRAINT `fk_Vinho_Vinicola`
    FOREIGN KEY (`codVinicola`)
    REFERENCES `mydb`.`Vinicola` (`codVinicola`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

And here is the script in which I'm trying to build to insert values onto the tables :

insert into `mydb`.`Regiao` values (1, "Campos Alegres", "Lindos campos verdes");
insert into `mydb`.`Regiao` values (2, "Campos Tristes", "Campos cinzas e planos");
insert into `mydb`.`Regiao` values (3, "Planalto Central", "Campos verdes e montanhosos");
insert into `mydb`.`Regiao` values (4, "Montanhas Roxas", "Terrenos montanhosos e rochosos");
insert into `mydb`.`Regiao` values (5, "Caverna do Dragão", "Temperatura amena e úmida");

insert into `mydb`.`Vinicola` values (1, "Durigan" , "Vinhos para te deixar alegre", "12345678901", "[email protected]" , 1);
insert into `mydb`.`Vinicola` values (2, "Legado" , "Vinhos para te deixar triste", "45623572901", "[email protected]" , 2);
insert into `mydb`.`Vinicola` values (3, "Franco" , "Vinhos para subir nas alturas", "918273531", "[email protected]" , 3);
insert into `mydb`.`Vinicola` values (4, "Bento" , "Vinhos afrodisíacos", "9102184629", "[email protected]" , 4);
insert into `mydb`.`Vinicola` values (5, "Busato" , "Vinhos do centro da terra", "9182736409", "[email protected]" , 5);

insert into `mydb`.`Vinho` values (1, "Manacá", "Merlot", 1998, "Sabor seco e doce", 1);
insert into `mydb`.`Vinho` values (2, "Tupinambá", "Cabernet Sauvignon", 2011, "Sabor suave e doce", 2);
insert into `mydb`.`Vinho` values (3, "Xavassa", "Pinot Noir", 1972, "Vinho tinto e seco", 3);
insert into `mydb`.`Vinho` values (4, "Tiluriloca", "Syrah", 2010, "Vinho rosé", 4);
insert into `mydb`.`Vinho` values (5, "Lapolipo", "Malbec", 2021, "Vinho branco", 5);

The issue is that it's not working, for some reason "Vinho" and "Vinicola" are getting none of the values, showing only NULL columns.

I'm thinking this could have something to do with the relationship keys but I haven't been able to figure it out.

Would be grateful if someone could help me with this.

CodePudding user response：

Your issue is in the definition of the Vinicola.emailVinicola column

 VARCHAR(15) NULL

Your email addresses are larger than 15 characters so the inserts fail, which then causes the Vinho table inserts to fail due to the constraint.

Increase the size of the column to something appropriate like VARCHAR(80)