Delete record in relational tables

1

I need to delete a record from a relational table, the data is in the main table, as I show in the image.

for this I have the following query that runs without error but does not affect the records:

DELETE FROM usuario
WHERE EXISTS (DELETE usuario, usuario_permiso 
FROM usuario, usuario_permiso 
WHERE usuario.idusuario = '25'
AND usuario_permiso.idusuario = '25')
    
asked by Anderviver 17.05.2018 в 15:37
source

1 answer

3

You must set your foreign key as "ON DELETE CASCADE" . It is probably by default in "MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION"

For this you must go to the specific table ( user_permission in your case) and check in the key statement the foreign key idusuario . There you will observe what I mentioned in the previous paragraph. You must update your foreign key to "ON DELETE CASCADE" which means if you delete a record in the "parent" table also delete it in the associated child table, that is, if you delete a record in the "user" table delete also in "user_permission" everything associated with idusuario selected.

An example in PostgreSQL would be:

When creating the foreign key, place in your configuration "On Delete" the "CASCADE" option, as the previous image shows in this way, guarantee that your problem is solved

Observation: As a rule, this type of "configuration" or "restriction" is done when designing the database schema to avoid complications once the production environment is in the base of data

    
answered by 17.05.2018 / 15:48
source