Mysql - Foreign key in ON DELETE NO ACTION but runs ON DELETE CASCADE

Asked

Viewed 367 times

2

I’m developing a system using Apache, Hibernate e MySQL. But I’m having a problem during the exclusion of parent entries from a foreign key. The database should prevent deletion, but delete the parent entry and all associated with it, through the foreign key (CASCADE). A SELECT @@FOREIGN_KEY_CHECKS returns 1 demonstrating that the check is active in MySQL. I suspect that the Apache or the Hibernate is disabling the check, but I don’t know how.

Here is part of my bank with the two tables in question:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema dispensario
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema dispensario
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `dispensario` DEFAULT CHARACTER SET utf8 ;
USE `dispensario` ;

-- -----------------------------------------------------
-- Table `dispensario`.`estado`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dispensario`.`estado` (
  `estado_id` INT NOT NULL AUTO_INCREMENT COMMENT 'Código identificador do estado.',
  `estado_nome` VARCHAR(75) NOT NULL COMMENT 'Nome do estado.',
  `estado_uf` VARCHAR(2) NOT NULL COMMENT 'Unidade da federação do estado.',
  `estado_situacao` TINYINT(1) NOT NULL COMMENT 'Situação do estado (ativo ou inativo)',
  PRIMARY KEY (`estado_id`),
  UNIQUE INDEX `estado_nome_UNIQUE` (`estado_nome` ASC),
  UNIQUE INDEX `estado_uf_UNIQUE` (`estado_uf` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dispensario`.`cidade`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dispensario`.`cidade` (
  `cidade_id` INT NOT NULL AUTO_INCREMENT COMMENT 'Código identificador da cidade.',
  `cidade_nome` VARCHAR(75) NOT NULL COMMENT 'Nome da cidade.',
  `cidade_estado` INT NOT NULL COMMENT 'Código do estado do qual a cidade pertence.',
  `cidade_situacao` TINYINT(1) NOT NULL COMMENT 'Situação da cidade (ativa ou inativa).',
  PRIMARY KEY (`cidade_id`),
  INDEX `cidade_estado_idx` (`cidade_estado` ASC),
  CONSTRAINT `fk_cidade_estado`
    FOREIGN KEY (`cidade_estado`)
    REFERENCES `dispensario`.`estado` (`estado_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Data for table `dispensario`.`estado`
-- -----------------------------------------------------
START TRANSACTION;
USE `dispensario`;
INSERT INTO `dispensario`.`estado` (`estado_id`, `estado_nome`, `estado_uf`, `estado_situacao`) VALUES (1, 'Minas Gerais', 'MG', 1);

COMMIT;


-- -----------------------------------------------------
-- Data for table `dispensario`.`cidade`
-- -----------------------------------------------------
START TRANSACTION;
USE `dispensario`;
INSERT INTO `dispensario`.`cidade` (`cidade_id`, `cidade_nome`, `cidade_estado`, `cidade_situacao`) VALUES (1, 'Belo Horizonte', 1, 1);

COMMIT;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Checking the model class. Status found a code snippet that can explain the problem:

 @OneToMany(cascade = CascadeType.ALL, mappedBy = "cidadeEstado")
 private Collection<Cidade> cidadeCollection;

After commenting on the excerpt cascade = CascadeType.ALL the exclusion worked as expected.

 @OneToMany(/*cascade = CascadeType.ALL,*/ mappedBy = "cidadeEstado")
 private Collection<Cidade> cidadeCollection;

However, this Database Entity Class is automatically generated by the Netbeans IDE according to database specifications. I am a beginner in Hibernate and I must be leaving some option enabled or disabled during the class generation process that adds this configuration. Except, if these settings have to be manually undone whenever classes are generated.

java mysql hibernate apache

  • And how’s the code that doesn’t do what you intend?

  • I added the code that makes insertion and removal in the database in the question.

  • Fabio, if the question has not yet had the attention you need try to modify it more deeply. Small edits just to call the homage seems not to have been worth it...

  • I appreciate the tip, but I believe I’ve come up with a solution to the problem. I will summarize the question add an answer to anyone who has similar doubt or wants to contribute another point of view.

1 answer

0

As far as I could check the Netbeans IDE generates the Database Entity Class automatically and sets the notation "Cascade = Cascadetype.ALL" for all foreign keys. In this way, Hibernate disregards the constraints of the database cascading changes. Changing this rating manually whenever the Entity Classes need to be redone would be impractical.

To solve the problem I adopted as good practice not depending on the bank’s restrictions to identify undue changes. Instead of trying to capture exceptions whenever an improper change was made, I checked each change before executing it.

Browser other questions tagged java mysql hibernate apache

You are not signed in. Login or sign up in order to post.