Home / Questions / 95134
Accepted
Kenny Barrera
Asked: 2020-08-16 07:34:50 +0800 CST 2020-08-16 07:34:50 +0800 CST

How do I change the referential integrity constraints once the tables are created?

  • 772

I have a table called usuario. The code I used to create it is as follows:

CREATE TABLE IF NOT EXISTS `cumplimiento`.`usuario` (
  `idusuario` VARCHAR(10) NOT NULL,
  `pass` VARCHAR(100) NOT NULL,
  `rol` INT NOT NULL,
  `activo` TINYINT(1) NOT NULL,
  `pregunta` VARCHAR(55) NULL,
  `respuesta` VARCHAR(200) NULL,
  PRIMARY KEY (`idusuario`),
  INDEX `fk_usuario_rol1_idx` (`rol` ASC),
  CONSTRAINT `fk_usuario_rol1`
    FOREIGN KEY (`rol`)
    REFERENCES `cumplimiento`.`rol` (`rol`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The table is created successfully and everything is fine. The situation is the following. I need to make an UPDATEa idusuarioand that change is reflected in the tables where this field is a foreign key.

I found that the way to do that is to use the statement:

ON UPDATE CASCADE

For example, I have this other person table, whose code is as follows:

CREATE TABLE IF NOT EXISTS `cumplimiento`.`persona` (
  `cif` INT NOT NULL,
  `idusuario` VARCHAR(10) NOT NULL,
  `numero_impresiones` INT NULL,
  INDEX `fk_persona_usuario1_idx` (`idusuario` ASC),
  PRIMARY KEY (`cif`),
  CONSTRAINT `fk_persona_usuario1`
    FOREIGN KEY (`idusuario`)
    REFERENCES `cumplimiento`.`usuario` (`idusuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

And I understand that under the REFERENCESis my field idusuario. I also understand that it is in this table that I have to change the ON UPDATE NO ACTIONto ON UPDATE CASCADE, and so on in other tables where I have this field as a foreign key.

Now, basically I haven't done it because I'm a little scared that something might happen to my tables and I guess there's an easy way to do it. How do I go about changing that? Do I have to use the same script and only change that part of each table? or maybe, can it be done somehow in Workbench?

mysql

2 Answers

  1. Best Answer

    It seems to me that there is no way to update a constraint, what you have to do are two things:

    1. Remove the current constraint
    2. Add a new constraint

    Therefore, you would have to do:

    -- Eliminar el constraint
ALTER TABLE `cumplimiento`.`usuario`
DROP FOREIGN KEY `fk_usuario_rol1`;

-- Crearlo nuevamente
ALTER TABLE `cumplimiento`.`usuario`
ADD CONSTRAINT `fk_usuario_rol1` 
    FOREIGN KEY (`rol`)
    REFERENCES `cumplimiento`.`rol` (`rol`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE;

    You can consult the documentation regarding the use of FOREIGN KEY Constraints .

    • 1

  2. Indeed, Cesar's answer is the correct one. Thanks to César's answer, I was able to find a way to do it in Workbench, in a graphical way.

    1. Right click on the table, select it Alter Tableand at the bottom select Foreign Keys.

    enter image description here

    1. The foreign key to which the action has to be changed is selected. enter image description here
    1. On the right side, in the part that says Foreign Key Options, you can change the option. enter image description here
    1. And in the end, Workbench will take care of generating the same script that Cesar has suggested to obtain the desired changes. enter image description here
    • 1