Modify user

Question

Modify user

Navigation

#1 by (7 votes)

4

I have a database with 2 tables. A call sec_users with the following fields:

pk_user login pswd

Another table called solicitud with the following fields:

pk_solicitud fk_usuario fecha

Each user can create a request. The field fk_usuario of table solicitud is foreign key of field login of table sec_users Well I created a request for example with user1:

    pk_solicitud          fk_usuario          fecha
===================     =============     ==============
       1                   usuario1          2018/04/02

So far so good, my problem comes when I want to modify the user login that instead of user1, I want to change it to user3 and I get this error:

Cannot delete or update a parent row: a foreign key constraint fails ('bd_vacaciones'.'solicitud', CONSTRAINT 'Usuario' FOREIGN KEY ('fk_usuario') REFERENCES 'sec_users' ('login') ON DELETE NO ACTION ON UPDATE NO ACTION)

My sentence is this:

update sec_users set login='usuario3' where login = 'usuario1'

Any solution?

mysql sql

asked by Xerox 02.04.2018 в 11:10

source

1 answer

Remove scroll bars when using overflow: scroll Create border of different palette or rainbow colors

score 7 · Accepted Answer

The problem

For the error message: ON DELETE NO ACTION ON UPDATE NO ACTION , you can understand that when you update the field in the sec_users table, it will not be updated in the solicitud table .

Then, the update fails because if it is done, there would be data inconsistency. That is, since the column will not be updated to usuario3 in table solicitud , this column would remain usuario1 and would be orphan , since its reference changed to usuario3 ... .

The solution

In order for changes in related columns to take effect in their reference columns, the restriction for UPDATE should be set to ON UPDATE CASCADE instead of NO ACTION . So, when you update in one table, it will be updated in the other (in cascade).

To make this change in MySQL you can proceed as follows:

you delete the current restriction (this deletes the restriction itself, not the columns or the data in them)
```
ALTER TABLE 'solicitud' 
DROP FOREIGN KEY 'fk_usuario'; 
```

you create it again, indicating that you want updates in cascade

ALTER TABLE 'solicitud'  
ADD CONSTRAINT 'fk_usuario' 
FOREIGN KEY ('fk_usuario') REFERENCES 'sec_users' ('login') 
ON UPDATE CASCADE;

Here you can see how the restrictions work , it's explained for DELETE , but for UPDATE it's the same.