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

Navigation
1

I have a table called usuario . The code you use to create it is the following: 

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 correctly and everything is fine. The situation is the following. I need to make UPDATE to idusuario and that change be reflected in the tables where this field is a foreign key.

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

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 REFERENCES is my field idusuario . I also understand that it is in this table that I have to change the ON UPDATE NO ACTION to ON UPDATE CASCADE , and so on in other tables where I have this field as a foreign key.

Now, basically I have not done it because I have a little fear that something could happen to my tables and I intuit that there is an easy way to do it. How do I change that? Do I have to use the same script and only change that part of each table? or maybe, can it be done in some way in Workbench?

    
asked by Kenny Barrera 15.08.2017 в 17:34
source

2 answers

1

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

  • Delete the current constraint
  • Add a new constraint

    • Therefore, you should 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 .

        
    answered by 15.08.2017 / 17:47
    source
    1

    Indeed, Caesar's answer is correct. Thanks to César's response, I was able to find a way to do it in Workbench, in a graphic way.

  • Right click on the table, select Alter Table and on the bottom part select Foreign Keys .

  • The foreign key to which the action has to be changed is selected.
  • In the part on the right, in the part that says Foreign Key Options, you can change the option.
  • And at the end, Workbench will be responsible for generating the same script that Cesar has suggested to obtain the desired changes.
    • answered by 15.08.2017 в 18:07
    How to change the input method of an Edit text in java? Retry loading image in Glide with 2 URL's