How can I prevent the deletion of relational data through referential integrity in MYSQL?

Question

How can I prevent the deletion of relational data through referential integrity in MYSQL?

Navigation

#1 by (1 votes)

5

I have the SIMULATE and QUESTION tables, where a simulation can have several questions, but one question can only be for a simulated (1-n relationship).

SIMULATED TABLE:

simuladoId
simuladoNome

QUESTION TABLE

questaoId
questaoPergunta
questaoIdSimulado

I'd like to know how to prevent these relational data from being deleted. That is, when I delete a dummy that has its foreign key propagated to one or more question, an error is issued, preventing.

For the little bit that I researched I understood that, at least in MYSQL, it is possible through Triggers .. But I do not understand much of the subject, so I would like some other option, or even some trigger examples.

I've tried the following:

alter table ang_questao add constraint FK_ang_questao foreign key (questao_id_simulado) references ang_simulado (simulado_id) ON DELETE RESTRICT

I ran and there was no error in SQL.

When I tried to delete it, I was shown:

Deletion Error: SQLSTATE [23000]: Integrity constraint violation: 1451   Can not delete or update parent row: a foreign key constraint fails   (% with%.% of%, CONSTRAINT% with% FOREIGN   KEY ( simulado_anglo ) REFERENCES ang_questao ( FK_ang_questao ))

Line Error: # 10 :: Can not modify header information - headers   already sent by (output started at   C: \ xampp \ htdocs \ simulate-general \ config.php: 33)   C: \ xampp \ htdocs \ dummy \ admin \ php \ deletar.php

mysql sql trigger

asked by anonymous 14.09.2016 / 17:37

1 answer

Group items with a date difference of 10 minutes between each other How do I update my application through APK without losing my SQLite database?

score 1 · Answer 1

Good afternoon, Thiago,

I want to know what it would look like if I wanted the two options: When deleting a dummy, remove cascading issues, or not allowing the dummy to be erased.

1) For when to exclude a Simulate Cascade delete the Issues, you must use "on delete cascade" in the Foreign Key constraint.

2) In order to not allow the Simulated to be deleted with at least one Issue, you have already done so by creating the Foreign Key constraint normally.

My Example:

1) Create the tables:

mysql> create table simulado
-> (simuladoId int not null auto_increment,
->  simuladoNome varchar(40) not null,
->  primary key (simuladoId)
-> ) engine=innodb;
Query OK, 0 rows affected (1,25 sec)

mysql> create table questao
    -> (questaoId int not null auto_increment,
    ->  questaoPergunta varchar(40) not null,
    ->  questaoIdSimulado int not null,
    ->  primary key (questaoId),
    ->  constraint fk_questao_questaoIdSimulado foreign key (questaoIdSimulado) references simulado (simuladoId)
    -> ) engine=innodb;
Query OK, 0 rows affected (0,57 sec)

2) Insert the records:

mysql> insert into simulado values (1, 'Simulado do Fernando');
Query OK, 1 row affected (0,06 sec)

mysql> insert into questao values (1, 'Pergunta 1', 1);
Query OK, 1 row affected (0,02 sec)

mysql> insert into questao values (2, 'Pergunta 2', 1);
Query OK, 1 row affected (0,07 sec)

3) Now try to delete the Simulate, it will not let:

mysql> delete from simulado where simuladoId=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ('teste'.'questao', CONSTRAINT 'fk_questao_questaoIdSimulado' FOREIGN KEY ('questaoIdSimulado') REFERENCES 'simulado' ('simuladoId'))

4) If I want to delete child records from the Simulate, I have to change the constraint on the child table (Questao) for cascading deletion, for example:

mysql> alter table questao drop foreign key fk_questao_questaoIdSimulado;
Query OK, 0 rows affected (0,52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table questao add constraint fk_questao_questaoIdSimulado 
    -> foreign key (questaoIdSimulado) references 
    -> simulado (simuladoId) on delete cascade;
Query OK, 2 rows affected (1,08 sec)
Records: 2  Duplicates: 0  Warnings: 0

5) Now deleting the parent record of the Simulate automatically deletes the child records of Issues:

mysql> delete from simulado where simuladoId=1;
Query OK, 1 row affected (0,02 sec)

mysql> select * from simulado;
Empty set (0,00 sec)

mysql> select * from questao;
Empty set (0,01 sec)